Hi,
I am getting the runtime error 3125 - 'OutstandingAddendum$A!:Z100' is not a valid name. I have checked that my sheet names are correct as well as the spelling. What is odd, this same, exactly the same line of code for a different command click works and it doesn't on 3 others. I am adding an import on the other three button clicks that I don't have on the code that works.
Here is the code that is broken:
Private Sub Command2_Click()
Dim SourceFile, DestinationFile
Dim StrFile As String
Dim strDate As String
Dim strMsgBox As Integer
Dim mydb As Database
Set mydb = CurrentDb
'Hides warning Messages
DoCmd.SetWarnings False
'Define Source File name
SourceFile = "S:\CPR\Conversion_Priority_List\Conversion_Priority_Misc_Requests.xlsx"
'Defines Date
strDate = Format(Date, "yyyymmdd")
'Define Destination File Name
DestinationFile = "G:\SHARED\OPS\Task Database\Outstanding Addendum\Outstanding Addendum " & strDate & ".xlsx"
'Deletes Data from Temp Table
DoCmd.OpenQuery "Delete Outstanding Temp Table", acViewNormal, acEdit
'Deletes Data from Outstanding_Addendum Table
DoCmd.OpenQuery "Delete Outstanding Addendum", acViewNormal, acEdit
'Imports File
DoCmd.TransferSpreadsheet acImport, 9, "TblOustandingTemp", SourceFile, True, "Outstanding_Addendum!A1:Z100"
'Appends Data to Table
DoCmd.OpenQuery "Append Outstanding Addendum", acViewNormal, acEdit
'File Copy
FileCopy "G:\SHARED\OPS\Task Database\Outstanding Addendum\Outstanding_Addendum.xlsx", "G:\SHARED\OPS\Task Database\Outstanding Addendum\Outstanding_Addendum " & strDate & ".xlsx"
'Data Export
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Outstanding_Addendum", DestinationFile, True, "OutstandingAddendum!A1:Z100"
'Msg Box
strMsgBox = MsgBox("Export Complete!", vbOKOnly, "Outstanding Addendum")
End Sub
The code in Red is where I Am getting the error.
Any help is appreciated
Thanks,
Msublett
I am getting the runtime error 3125 - 'OutstandingAddendum$A!:Z100' is not a valid name. I have checked that my sheet names are correct as well as the spelling. What is odd, this same, exactly the same line of code for a different command click works and it doesn't on 3 others. I am adding an import on the other three button clicks that I don't have on the code that works.
Here is the code that is broken:
Private Sub Command2_Click()
Dim SourceFile, DestinationFile
Dim StrFile As String
Dim strDate As String
Dim strMsgBox As Integer
Dim mydb As Database
Set mydb = CurrentDb
'Hides warning Messages
DoCmd.SetWarnings False
'Define Source File name
SourceFile = "S:\CPR\Conversion_Priority_List\Conversion_Priority_Misc_Requests.xlsx"
'Defines Date
strDate = Format(Date, "yyyymmdd")
'Define Destination File Name
DestinationFile = "G:\SHARED\OPS\Task Database\Outstanding Addendum\Outstanding Addendum " & strDate & ".xlsx"
'Deletes Data from Temp Table
DoCmd.OpenQuery "Delete Outstanding Temp Table", acViewNormal, acEdit
'Deletes Data from Outstanding_Addendum Table
DoCmd.OpenQuery "Delete Outstanding Addendum", acViewNormal, acEdit
'Imports File
DoCmd.TransferSpreadsheet acImport, 9, "TblOustandingTemp", SourceFile, True, "Outstanding_Addendum!A1:Z100"
'Appends Data to Table
DoCmd.OpenQuery "Append Outstanding Addendum", acViewNormal, acEdit
'File Copy
FileCopy "G:\SHARED\OPS\Task Database\Outstanding Addendum\Outstanding_Addendum.xlsx", "G:\SHARED\OPS\Task Database\Outstanding Addendum\Outstanding_Addendum " & strDate & ".xlsx"
'Data Export
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Outstanding_Addendum", DestinationFile, True, "OutstandingAddendum!A1:Z100"
'Msg Box
strMsgBox = MsgBox("Export Complete!", vbOKOnly, "Outstanding Addendum")
End Sub
The code in Red is where I Am getting the error.
Any help is appreciated
Thanks,
Msublett