Hyphen in worksheet name when

[gdesreu]

I run a series of queries in access and then export them to an excel file and name the worksheet. I have one worksheet that must contain a hyphen but when it exports it to excel the worksheet name has an underscore inserted instead of the name I entered. Does anyone know how to keep the hyphen from being converted to an underscore.

Offending code:

If DCount("sampname", "qry_test") Then
    cnt = cnt + 1
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_test", "C:\Projects\Error Report\Report_working.xls", True, "NJ-AAA_Flag_Check"
End If

"NJ-AAA_Flag_Check" is converted to "NJ_AAA_Flag_Check"

 

[Micron]

Not sure, because running into this over the years I would never use special characters or spaces in the name of anything. Not drive folders, not file names, not sheets - not anything that could potentially be involved in vba or sql. You could try escaping it (--) or see if there is an escape character in vba. Or you could try the ASCII value for - (decimal 44). See

ASCII Table - ASCII Character Codes, HTML, Octal, Hex, Decimal

Ascii character table - What is ascii - Complete tables including hex, octal, html, decimal conversions

www.asciitable.com

 

[gdesreu]

Thanks, unfortunately the more I tell people not to use special characters in things like filepaths and sheetnames, the more they do it. In this particular case its not an option, the sheet must be named that way per the client.

 

[Micron]

Aside from that already suggested, here's an afterthought: try "NJ" & "-" & "AAA_Flag_Check"
Using suggested ASCII might look like "NJ" & Chr(45) & "AAA_Flag_Check"

 

[jackd]

Can you tell us more about "one worksheet that must contain a hyphen." Perhaps change/redesign the logic that demands this constraint.

Brute force maybe replace the - with say "WZW", perform your logic, export then Replace WZW with "-".

 

[james_lankford]

try [NJ-AAA_Flag_Check]