Help with some code Please

[Msublett]

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

 

[Sifou]

Hi Msublett,

Ideally it would be nice and quick if you can share a version of your workbook, and send it to me at: (e-mail address removed).

otherwise, have you tried debugging the code in VBA editor? if not, this may be the way you want to troubleshoot the issue you described in your post. Thanks

Regards,
Sifou