Solved

DoCmd.TransferSpreadsheet ac Export not working

Posted on 2014-01-25
5
633 Views
Last Modified: 2014-01-26
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
Comment
Question by:rrudolph
  • 2
  • 2
5 Comments
 
LVL 40
ID: 39809574
Why the SQLQuote at the beginning and the end of STRFILENAME? You should not need them.
0
 

Author Comment

by:rrudolph
ID: 39809586
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 250 total points
ID: 39809597
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
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 250 total points
ID: 39809732
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
 

Author Comment

by:rrudolph
ID: 39809915
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question