DoCmd.TransferSpreadsheet ac Export not working

I am running Office 2010, and am having problems transferring a table to a spreadsheet.

The last bit of code looks like this

Dim SQLQuote As String
SQLQuote = Chr(34)

STRFILENAME = SQLQuote & STRFILENAME & SQLQuote

Debug.Print STRFILENAME

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "FOODBUY_XFER_FILE", STRFILENAME, True

The immediate window for the Debug.Print STRFILENAME looks like this:

"D:\TIM_ACCESS_EXPORTS\FOODBUYACTIVITY\PROFISH01012014_01152014.XLS"

The spreadsheet is never created.

However, if I cut and paste the debug results in place of the variable STRFILENAME, it works fine.
Rick RudolphAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jacques Bourgeois (James Burger)Connect With a Mentor PresidentCommented:
It's not because you see an example in a book on on the Internet that it is good. Far from that.

Have you tried removing the qutoes as indicated.

Try this:

Dim x As String
x = "hello"
Debug.Print (x)

Open in new window

As you can see, the result does not show the quotes. The quotes are used in the code only, they are not in the variable. What is in the variable is not really "hello", it's hello, without the quotes. What the computer works with is hello.

Now, if you were to do:

x = SQLQuote & x & SQLQuote

Open in new window

What you would see in the debugger is "hello", not the hello usually given off by a string. That is because you have included the quotes as being part of the string.

What you are pasting do when you paste your debugger value in your line of code is the syntax that the interpreter expects, so it works:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "FOODBUY_XFER_FILE", "D:\TIM_ACCESS_EXPORTS\FOODBUYACTIVITY\PROFISH01012014_01152014.XLS", True

Open in new window

But when you run the code with your variable, in which you have inserted superfluous quotes, what the interpreter sees is the following:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "FOODBUY_XFER_FILE", ""D:\TIM_ACCESS_EXPORTS\FOODBUYACTIVITY\PROFISH01012014_01152014.XLS"", True

Open in new window

It sees 2 double quotes at the beginning, and 2 at the end. This is not the proper syntax, so it does not work.
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
Why the SQLQuote at the beginning and the end of STRFILENAME? You should not need them.
0
 
Rick RudolphAuthor Commented:
I used the SQLQuote because I saw an example with quotes typed out.

As I said in my post however, if I cut and paste with the quotes, it works, I did not test cutting & pasting without the quotes.

I should also say that this exact code works in a different database. I am assuming it has something to do with the library references, but am not sure.
0
 
mbizupConnect With a Mentor Commented:
Not only is the first comment correct about you not needing the SQLQuote, it may be causing this problem, so try omitting them altogether.

Literal text needs to be embedded in quotes, as in your immediate window test.

However, string variables (STRFILENAME) should not be surrounded by quotes in the TransferSpreadsheet command.

Those SQLQuotes DO have their place... for example to delimit text data when building query strings... but they are not used in this context (filenames in the transferspreadsheet command)


So just try the following:

Debug.Print STRFILENAME

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "FOODBUY_XFER_FILE", STRFILENAME, True

Open in new window

0
 
Rick RudolphAuthor Commented:
Everyone,

Thank you for your input, I think the SQLQuote issue threw everyone off of the issue, but the comments were on point with respect to that. I noted in my replies that I had tried this code without the quotes to begin with and had the same problem.

The problem turned out to be that the table being exported is a linked table that just prior to the code I shared was being updated with some SQL code. I suspect that the table was not in a state to be exported when the export code was encountered.

I inserted a Pause of 2 seconds in the code and that remedied all issues.

Thanks again, and I am simply splitting the points amongst all whom tried to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.