Access VBA to name excel sheet


Please see below code, which transfers MS Access query into MS Excel.

DoCmd.TransferSpreadsheet acExport, 10, "QryInt", "C:\abcd\abcd.xlsx", True, "2017abcd"

Everyting is fine but sheet name starts with an underscore. ie. "_2017abcd". How can I name the sheet name just "abcd"?

Thank you
Asatoma SadgamayaAnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sam JacobsDirector of Technology Development, IPMCommented:
You need to add a reference to the Microsoft Excel Object Library (under Tools):
Dim objExcel As Excel.Application
Dim objWB As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim rng As Excel.Range

Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open("C:\abcd\abcd.xlsx")
objExcel.Visible = True
objWB.Worksheets("_2017abcd").Name = "2017abcd"

objWB.Close True
Set objWS = Nothing
Set objWB = Nothing
Set objExcel = Nothing

Open in new window

Asatoma SadgamayaAnalystAuthor Commented:
Thanks for your reply Sam.

I have already done that!!. I am looking for an answer which work with my DoCmd code.

Thank you
Sam JacobsDirector of Technology Development, IPMCommented:
Does it help if you put single quotes within the double quotes?
DoCmd.TransferSpreadsheet acExport, 10, "QryInt", "C:\abcd\abcd.xlsx", True, "'2017abcd'"

Open in new window

Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Asatoma SadgamayaAnalystAuthor Commented:
No Sam that is not working..
Gustav BrockCIOCommented:
I don't think it is possible this way to use a leading digit in the worksheet name.
1.  Rather than using "10", use the name of the spreadsheet type so we don't have to open Access and look up the value to see if it is correct.  There is nothing cool or efficient about using numeric values for parameters rather than their assigned names.  It just makes your code harder to read.
2.  The sheet is named with the name of the query/table it is exported from but RangeName is used if it is supplied.  There is an underscore being added as the prefix because Excel does not allow sheet names to start with numbers.
3. Hardcoding values like year introduces unnecessary constraints.  What are you going to do next year when you want the year to be 2018?
Gustav BrockCIOCommented:
Excel does not allow sheet names to start with numbers.

It does, and you can easily rename worksheets to have a Name property of 1, 2, 3, etc.
Excel123.PNGHowever, a worksheet's CodeName property cannot start with a digit, but it is not this property that is being addressed with the parameter value of Range with DoCmd.TransferSpreadsheet acExport, ...  - that is Name; the CodeName property of the created worksheet remains untouched - the default "Sheet1" (localised).

So, the issue may be a left-over from ancient versions of Excel where - perhaps, I'm only guessing - Name and CodeName were the same.
I'm using O365 and when I export using TransferSpreadsheet, I get an underscore if the name starts with a number.  I didn't try opening Excel and making a new sheet since that wasn't where the problem happened.  So it looks like the issue is Access having some old memory of Excel's rules.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.