Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 710
  • Last Modified:

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.
0
rrudolph
Asked:
rrudolph
  • 2
  • 2
2 Solutions
 
Jacques Bourgeois (James Burger)Commented:
Why the SQLQuote at the beginning and the end of STRFILENAME? You should not need them.
0
 
rrudolphAuthor 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
 
mbizupCommented:
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
 
Jacques Bourgeois (James Burger)Commented:
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
 
rrudolphAuthor 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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now