Renaming & Exporting query to Excel

[Guest]

I'm trying to rename a query by adding the name and date and then exporting that query to Excel.

However, after the file has been exported, I get an error message in Excel that says repairs were made to the sheet. It says "Renamed invalid sheet name."

Any ideas of what is wrong?

Here is my code:
Option Compare Database

'------------------------------------------------------------
' mcrUpdate
'
'------------------------------------------------------------
Function mcrUpdate()
On Error GoTo mcrUpdate_Err
Dim Name As String
'Dim Min_SPL As Variant
'Dim Max_SPL As Variant
'Dim i As Variant
Name = [Forms]![frmMain]![cmbSPL]
'Dim StrDate As String
'StrDate = Mid(Date, 6, 2) & "0" & Mid(Date, 1, 1) & Mid(Date, 3, 2)
Dim MyDate As String
MyDate = Date ' MyDate contains the current system date.

'Min_SPL = DMin("SPL_NUMBER", "SPL_HEADER_LOCAL")
'Max_SPL = DMax("SPL_NUMBER", "SPL_HEADER_LOCAL")

'For i = Min_SPL To Max_SPL

'Next

DoCmd.SetWarnings False
' Deletes spl selected material
DoCmd.Hourglass hourglasson
DoCmd.OpenQuery "dqry_Selected_SPL_Material", acNormal, acEdit
' repopulates the select spl set of material for status
DoCmd.OpenQuery "aqry_Selected_SPL_Material", acNormal, acEdit
' Deletes SPL with location contents
DoCmd.OpenQuery "dqrySelected_SPL_Location", acNormal, acEdit
' Appends SPL with location contents
DoCmd.OpenQuery "aqrySelected_SPL_Location", acNormal, acEdit
' Populates CDC Values for selected SPL in the event they don't exist
DoCmd.OpenQuery "aqrySelected_SPL_Location_CDC", acNormal, acEdit
' Deletes the status table
DoCmd.OpenQuery "dqrySPL_Status", acNormal, acEdit
' re populaltes te status table with values that correspond to the spl selected material
DoCmd.OpenQuery "aqrySPL_Status_Fixed_Attribute", acNormal, acEdit
' Updates SPL qty for selected SPL
DoCmd.OpenQuery "uqry_SPL_Status_SPL_Qty", acNormal, acEdit
' Update on hand plus po qty
DoCmd.OpenQuery "uqrySPL_Status_OH_PO_Qty", acNormal, acEdit
DoCmd.CopyObject "", "SPL " & Name & " " & MyDate, acQuery, "V_SPL_Status"
DoCmd.OutputTo acQuery, "SPL " & Name & " " & MyDate, "MicrosoftExcel(*.xls)", "", True, ""
DoCmd.DeleteObject acQuery, "SPL " & Name & " " & MyDate

mcrUpdate_Exit:
Exit Function

mcrUpdate_Err:
MsgBox Error$
Resume mcrUpdate_Exit

End Function

 

[Ken Snell]

Because you're concatenating MyDate into the filename, the sheet name also
will be named by that same name....and, the way you're doing this, ACCESS is
using a format of the date that includes / characters. Those, of course, are
not valid characters.

Replace this line
MyDate = Date

with this line
MyDate = Format(Date, "mm_dd_yyyy")


--
Ken Snell
<MS ACCESS MVP>

I'm trying to rename a query by adding the name and date and then exporting that query to Excel.

However, after the file has been exported, I get an error message in Excel

that says repairs were made to the sheet. It says "Renamed invalid sheet
name."

Any ideas of what is wrong?

Here is my code:
Option Compare Database

'------------------------------------------------------------
' mcrUpdate
'
'------------------------------------------------------------
Function mcrUpdate()
On Error GoTo mcrUpdate_Err
Dim Name As String
'Dim Min_SPL As Variant
'Dim Max_SPL As Variant
'Dim i As Variant
Name = [Forms]![frmMain]![cmbSPL]
'Dim StrDate As String
'StrDate = Mid(Date, 6, 2) & "0" & Mid(Date, 1, 1) & Mid(Date, 3, 2)
Dim MyDate As String
MyDate = Date ' MyDate contains the current system date.

'Min_SPL = DMin("SPL_NUMBER", "SPL_HEADER_LOCAL")
'Max_SPL = DMax("SPL_NUMBER", "SPL_HEADER_LOCAL")

'For i = Min_SPL To Max_SPL

'Next

DoCmd.SetWarnings False
' Deletes spl selected material
DoCmd.Hourglass hourglasson
DoCmd.OpenQuery "dqry_Selected_SPL_Material", acNormal, acEdit
' repopulates the select spl set of material for status
DoCmd.OpenQuery "aqry_Selected_SPL_Material", acNormal, acEdit
' Deletes SPL with location contents
DoCmd.OpenQuery "dqrySelected_SPL_Location", acNormal, acEdit
' Appends SPL with location contents
DoCmd.OpenQuery "aqrySelected_SPL_Location", acNormal, acEdit
' Populates CDC Values for selected SPL in the event they don't exist
DoCmd.OpenQuery "aqrySelected_SPL_Location_CDC", acNormal, acEdit
' Deletes the status table
DoCmd.OpenQuery "dqrySPL_Status", acNormal, acEdit
' re populaltes te status table with values that correspond to the spl selected material
DoCmd.OpenQuery "aqrySPL_Status_Fixed_Attribute", acNormal, acEdit
' Updates SPL qty for selected SPL
DoCmd.OpenQuery "uqry_SPL_Status_SPL_Qty", acNormal, acEdit
' Update on hand plus po qty
DoCmd.OpenQuery "uqrySPL_Status_OH_PO_Qty", acNormal, acEdit
DoCmd.CopyObject "", "SPL " & Name & " " & MyDate, acQuery, "V_SPL_Status"
DoCmd.OutputTo acQuery, "SPL " & Name & " " & MyDate,

"MicrosoftExcel(*.xls)", "", True, ""

 

[Guest]

The code you gave me worked.

Thanks Ken.

Regards,
Matt

 

[Ken Snell]

You're welcome.