MS Access Query Export to Excel RRS feed

  • Question

  • Hi,

    When i am exporting the query to excel

    The name of the sheet is not getting correctly

    for eg:

    The query name is 123 abc yyy

    but when the query(123 abc yyy) is exported to excel the sheet name is taken as _123_abc_yyy

    I want the sheet name exactly as query name 123 abc yyy

    How to eliminate _ while exporting.

    advance thanks


    Monday, August 28, 2017 11:40 AM

All replies

  • Weird!  What is causing that '_' character to appear in sheet names?  Is it because databases prefer non-spaces in field names.  Well, anyway, here is one way to loop through all sheets in an Excel workbook and replace an unwanted character.

    Public Sub Direct2ToNet()
    Const sRepl As String = "_"
    Dim ws As Worksheet
    Dim nPos
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
    With ws
    If .Name Like "*" & sRepl & "*" Then _
    .Name = Replace(.Name, sRepl, " ")
    End With
    Next ws
    On Error GoTo 0
    End Sub


    • Edited by ryguy72 Wednesday, August 30, 2017 3:22 AM
    Wednesday, August 30, 2017 3:21 AM