Solved

DoCmd.TransferSpreadsheet ac Export not working

Posted on 2014-01-25
5
602 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now