Solved Export to Spreadsheet and Name Tab

[MattBaldry]

Hi all,

I am exporting 3 queries to a spreadsheet for import into another program. Export works fine, but 1 tab is giving me issues.

The tab needs to be named Bill of Material Records. But when exported it replaces the space with an underscore, Bill_of_Material_Records.

Does anyone know how I could export it with the spaces? I tried & space(1) & between each word, but this still had the _ on the tab name.

I also tried naming the query to the exact tab name I wanted, still had the _

    Dim strFilePath As String
        strFilePath = "C:\Users\" & Environ$("Username") & "\Desktop\"
    Dim strFileName As String
        strFileName = "Scott Cables Quote BOM Import " & [Forms]![frmQuote]![txtQuoteID] & ").xlsx"

        DoCmd.SetWarnings False
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMOperationImport", strFilePath + strFileName, True, "Operations"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMComponentImport", strFilePath + strFileName, True, "Components"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMRecordImport", strFilePath + strFileName, True, "Bill of Material Records"
        DoCmd.SetWarnings True

~Matt

 

[Ranman256]

tabs cant have spaces in names.

 

[MattBaldry]

tabs cant have spaces in names.

Why not? They can if I edit the name manually. Why can I not export to there with spaces?

~Matt

 

[arnelgp]

tabs cant have spaces in names.

it can have space.

what you can do is add code to open the Workbook and rename the Worksheet name:

Dim strFilePath As String
        strFilePath = "C:\Users\" & Environ$("Username") & "\Desktop\"
    Dim strFileName As String
        strFileName = "Scott Cables Quote BOM Import " & [Forms]![frmQuote]![txtQuoteID] & ").xlsx"

        DoCmd.SetWarnings False
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMOperationImport", strFilePath + strFileName, True, "Operations"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMComponentImport", strFilePath + strFileName, True, "Components"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMRecordImport", strFilePath + strFileName, True, "Bill of Material Records"
        DoCmd.SetWarnings True

' arnelgp
' rename the sheet name replacing "_" with space
'
Dim oSheet As Object
With CreateObject("Excel.Application")
    With .Workbooks.Open(strFilePath + strFileName)
        For Each oSheet In .Worksheets
            With oSheet
                .Name = Replace$(.Name, "_", " ")
            End With
        Next
        .Close True
    End With
    .Quit
End With

 

[MattBaldry]

it can have space.

what you can do is add code to open the Workbook and rename the Worksheet name:

Dim strFilePath As String
        strFilePath = "C:\Users\" & Environ$("Username") & "\Desktop\"
    Dim strFileName As String
        strFileName = "Scott Cables Quote BOM Import " & [Forms]![frmQuote]![txtQuoteID] & ").xlsx"

        DoCmd.SetWarnings False
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMOperationImport", strFilePath + strFileName, True, "Operations"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMComponentImport", strFilePath + strFileName, True, "Components"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMRecordImport", strFilePath + strFileName, True, "Bill of Material Records"
        DoCmd.SetWarnings True

' arnelgp
' rename the sheet name replacing "_" with space
'
Dim oSheet As Object
With CreateObject("Excel.Application")
    With .Workbooks.Open(strFilePath + strFileName)
        For Each oSheet In .Worksheets
            With oSheet
                .Name = Replace$(.Name, "_", " ")
            End With
        Next
        .Close True
    End With
    .Quit
End With

I love you. Marry me.

~Matt

 

[arnelgp]

i forgot, if you are "exporting" to same workbook (thus using the above code), you need to revert first the process (replace again the space with "_"), so the code will not create "another" worksheet with "_" on it's name:

Dim oSheet As Object
Dim oExcel As Object
Dim strFilePath As String
        strFilePath = "C:\Users\" & Environ$("Username") & "\Desktop\"
    Dim strFileName As String
        strFileName = "Scott Cables Quote BOM Import " & [Forms]![frmQuote]![txtQuoteID] & ").xlsx"

' arnelgp
' revert if workbook exists,
' if we did not, then it would create "new" sheet with
' "_" on it's name
Set oExcel = CreateObject("Excel.Application")
If Len(Dir$(strFilePath + strFileName)) <> 0 Then
    With oExcel
        With .Workbooks.Open(strFilePath + strFileName)
            For Each oSheet In .Worksheets
                With oSheet
                    .Name = Replace$(.Name, " ", "_")
                End With
            Next
            .Close True
        End With
    End With
End If
        DoCmd.SetWarnings False
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMOperationImport", strFilePath + strFileName, True, "Operations"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMComponentImport", strFilePath + strFileName, True, "Components"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMRecordImport", strFilePath + strFileName, True, "Bill of Material Records"
        DoCmd.SetWarnings True

' arnelgp
' rename the sheet name replacing "_" with space
'
With oExcel
    With .Workbooks.Open(strFilePath + strFileName)
        For Each oSheet In .Worksheets
            With oSheet
                .Name = Replace$(.Name, "_", " ")
            End With
        Next
        .Close True
    End With
    .Quit
End With
Set oExcel = Nothing

 

[MattBaldry]

i forgot, if you are "exporting" to same workbook (thus using the above code), you need to revert first the process (replace again the space with "_"), so the code will not create "another" worksheet with "_" on it's name:

Dim oSheet As Object
Dim oExcel As Object
Dim strFilePath As String
        strFilePath = "C:\Users\" & Environ$("Username") & "\Desktop\"
    Dim strFileName As String
        strFileName = "Scott Cables Quote BOM Import " & [Forms]![frmQuote]![txtQuoteID] & ").xlsx"

' arnelgp
' revert if workbook exists,
' if we did not, then it would create "new" sheet with
' "_" on it's name
Set oExcel = CreateObject("Excel.Application")
If Len(Dir$(strFilePath + strFileName)) <> 0 Then
    With oExcel
        With .Workbooks.Open(strFilePath + strFileName)
            For Each oSheet In .Worksheets
                With oSheet
                    .Name = Replace$(.Name, " ", "_")
                End With
            Next
            .Close True
        End With
    End With
End If
        DoCmd.SetWarnings False
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMOperationImport", strFilePath + strFileName, True, "Operations"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMComponentImport", strFilePath + strFileName, True, "Components"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryCIM50BOMRecordImport", strFilePath + strFileName, True, "Bill of Material Records"
        DoCmd.SetWarnings True

' arnelgp
' rename the sheet name replacing "_" with space
'
With oExcel
    With .Workbooks.Open(strFilePath + strFileName)
        For Each oSheet In .Worksheets
            With oSheet
                .Name = Replace$(.Name, "_", " ")
            End With
        Next
        .Close True
    End With
    .Quit
End With
Set oExcel = Nothing

Hi Arne,

The code is all good. Every time it will be used will be a unique file name, so it wouldn't have an issue. Once the import is done into the 3rd party software, the file is deleted.

~Matt