TransferSpreadsheet / Excel Export Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all, thanks in advance for any help you can provide.

I want export the results of a query to a specific location in a spreadsheet
by using a range name in the spreadsheet.

For example I want to copy the results of query "Financial Categories" to
the file "C:\test.xls" at the pre-defined range "FinancialCategoriesStart" in
the spreadsheet.

I do not want to link to the Excel file to the Access DB, I need this to be
pushed out from Access.

Apparently TransferSpreadsheet cannot do this?

I do know how to open the file as an Excel Object in Access VBA code, but
unsure of what to do from there. Is there anyway I can replicate something
as simple as copying the results of the query and pasting to a certain range
name.

Here's the psuedocode of what I want accomplish, any ideas how to make
something like this work?

Sub testtransfer()
Dim Survey As Object
Dim MyFilename As String

Set Survey = CreateObject("Excel.Application")

Survey.Workbooks.Open Filename:="C:\test.xls"

CurrentDb.Recordsets("Financial Categories").Copy
Survey.ActiveWorkbook.Sheets("Feeder").Activate

Survey.ActiveWorkbook.Sheets("Feeder").Range("FinancialCategoriesStart").Activate
Survey.ActiveCell.Paste

MyFilename = Survey.ActiveWorkbook.FullName

Survey.ActiveWorkbook.Save
Survey.Quit
End Sub
 
Although Help says using the Range argument of the TransferSpreadsheet method
does not work, it really does. Just put the range name there.
 
Well I have seen some crazy things in Excel before but nothing like this. I
tried this line of code.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"TestQuery", MyFilename, True, "TestUploadRange"

where "Test Query" is a simple 1 field query, MyFilename is the export file
and "TestUploadRange" is the name of the range I want to export to.

Here's what happened
1. File size doubled
2. When I open the spreadsheet I get the message ("File Error - Some data
may be lost")
3. On EVERY sheet and many cells in the workbook, random cells and random
formulas have been overwritten with values from other other cells (not
anything from the query)
4. My Range "TestUploadRange" was actually redefined to a random location
(Cell L10 on the same sheet).
5. Various columns were hidden

Crazy I know.... Any thoughts about what I might be doing wrong, or if not,
any though about my original idea of using the Excel object to accomplish the
same thing?
 
Your computer is on drugs.
I have no idea what is going on there. I would try creating a new
spreadsheet, creating the name range, and retry the transfer to see what it
does. There may be something wrong with that spreadsheet.
 
Well I'm screwed lol. It worked on a simple spreadsheet so I did confirm it
can work on my computer. But I'm up a creek because I need it to work on a
complex spreadsheet (that also has embedded code) and it fritzes that
particular spreadsheet out (although all the other code interaction I do with
that file works great). Either my spreadsheet is screwed up (I sure hope
not) or there is something wierd about the TransferSpreadsheet when you use a
range name (which may be why Microsoft says it doesn't work).

Anyway, here's a messy workaround I was just able to come up with in case
anyone ever needs something similar.

Sub TestTransferB()
Dim Survey As Object
Dim myfilename As String
Dim MyFiles As FileDialog 'the standard OpenFile Dialog box

'Show the open file dialog box
Set MyFiles = Application.FileDialog(msoFileDialogFilePicker)
MyFiles.AllowMultiSelect = False
MyFiles.Show

Set Survey = CreateObject("Excel.Application")

Survey.Workbooks.Open Filename:=MyFiles.SelectedItems(1)

QueryToClipboard "Export Financial Impact Categories - Revenue"

Survey.ActiveWorkbook.Sheets("Feeder").Activate
Survey.ActiveWorkbook.Sheets("Feeder").Range("TestUpload").Activate
Survey.ActiveSheet.Paste
Survey.ActiveWorkbook.Save
Survey.Quit
End Sub


Sub QueryToClipboard(QueryName As String)
DoCmd.OpenQuery QueryName
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.Close
End Sub
 
Your solution is good, but that clipboard part is ugly! There is a better
way. Look in the VB Editor Object Browser, select Excel as your library and
search for CopyFromRecordset. Your query can be your recordset. It is a much
prettier way to do it. Aslo, after your Survey.Quit statment, put in Set
Survey = Nothing
 
By the way, I am not exactly sure what is going on, but I have had some
frustrating experiences with the TransferSpreadsheet results in File
Error + gobbldygook issue. Here is the solution I have come up with -
obviously this isn't the cause of all TransferSpreadsheet errors, but
it is one - which was worthwhile for me to pursue because
TransferSpreadsheet had qualities/features that CopyFromRecordset
doesn't.

Typically I have pasted some data into the spreadsheet that is in a
format that transferspreadsheet does not "like." Here is how to
reproduce this error (with a solution):

1) Take a blank spreadsheet. Add a few columns and rows of information
(it seems like the more complex the spreadsheet is, the more screwed up
it gets). Save this on the desktop as "test.xls". Close it.

2) Use Transferspreadsheet to export a query into "Test.xls"
(DoCmd.TransferSpreadsheet acExport, , "qxtbQuery", "C:\Documents and
Settings\Taylor\Desktop\test.xls", True, "qxtbQuery"). At this point,
it should work just fine. You should be able to open "Test.xls" with no
error messages.

3) Now open a query in your Access db. Select a few rows. Copy. Open
"Test.xls" and paste these rows in. Save and exit excel. (Now, I am
under the impression that several different types of data can be at
fault, but this is the only reproducible example I was able to come up
with).

4) Now use Transferspreadsheet again to export a query into "Test.xls"
(again, DoCmd.TransferSpreadsheet acExport, , "qxtbQuery",
"C:\Documents and Settings\Taylor\Desktop\test.xls", True,
"qxtbQuery").

5) Now open "Test.xls" and bask in the glow of "File Error: data may
have been lost" and the resulting gobbldygook. (Gobbldygook may be
minor or even non-existant on this basic test sheet, but on a complex,
multi-sheet/multi-chart worksheet, it is truly spectacular. The error
message seems to be consistent.)

6) Now here's the trick: If you open test.xls and delete/clear the
cells that were pasted directly from the query, you cease to get the
message. If you do this before the TransferSpreadsheet action, you
won't get it in the first place. The exciting implication is that the
"corruption" doesn't go deep - you clear the suspect cells, and viola!
you are up and running.

So what I have done before in cases like this is delete half the
worksheets in my worksheet (this is, of course, done on a 'dummy'
workbook, after having made a backup). Then do a test
TransferSpreadsheet and open the workbook. If no error, your culprit is
somewhere in the deleted sheets. If you get an error, start over (from
a backup) - and delete half the remaining sheets and try again.
Continue with the split-halving until you find the problem (I have even
worked it down to split-halving rows within the sheet).

Hopefully the source of your problems is on only one worksheet. On my
last encounter with this error, 'bad' data was on two worksheets, so it
was a little more time intensive. I think I got lucky because they were
next to each other - I think I would have probably assumed the entire
file was corrupt if both 'halves' had returned errors on the first
try...

Anyway, I agree with Klatuu that CopyFromRecordset is a very viable
method and elegant solution, with two caveats:

1) in my experience, TransferSpreadsheet was much, much faster (IIRC,
you have to open an instance of excel to use this method), so it was
worth my time to troubleshoot b/c the process was going to be repeated
many times over.

2) Again, IIRC, CopyFromRecordset does not provide for an elegant way
to transfer field names. As you can tell from my example "qxtbQuery,"
in a crosstab query losing the ability to generate field names is a
real problem (field names being dynamically genereated by info in the
column heading). CopyFromRecordset may get the data there, but if you
have no way of telling whether the fields are "CompanyName 1991
1992 1993 1994" or "CompanyName 1989 1992 1993 1997"
then you are in a spot of trouble.

Previewing this post, I have a hard time with brevity... but there it
is. Skol!
 
Thanks for the post, I'm glad to know my computer is on drugs. You're right
when you say gobbldygook, it is truly the most "spectacular" error I've ever
seen on Excel.

I'll try your suggestion of parsing down to find the "bad" data, I wonder if
you ever found any logic to the "bad" data. Was it a formula, text, a number?

Anyway, CopyFromRecordset is working great for me now, I don't need to do it
a whole ton and I am exporting about 15 queries to a spreadsheet in <15
seconds so that's not a big deal. Also I'm in the situation where the fields
names were actually a hindrance so I'm not worried about that issue too much
either.

Again thanks for you insightful post, it would be curious to figure out
exactly what causes this mysterious Excel illness.
 
Troy said:
Also I'm in the situation where the fields
names were actually a hindrance so I'm not worried about that issue too much
either.
Click to expand...


Thank you gentlemen for a very insightful thread.

Just a note.
Correct me if i'm wrong, but i dont think that the CopyFromRecordset method
has any problems 'exporting' the Field Names from Access, since this info is
already stored in the ADO recordset (rst.Fields(iCnt).Name).

The example below uses early binding (tziz!), and works assuming an ADO
recordset (rst) has already been opened:

Set AppXL = New Excel.Application
With AppXL
.ScreenUpdating = False
.Visible = False ' XL is hidden
.Workbooks.Add
.DisplayAlerts = False
' Naming the Columns with Access's Field Names
For iCnt = 0 To rst.Fields.Count - 1
.Cells(1, iCnt + 1).Value = rst.Fields(iCnt).Name
Next iCnt

' Copying the data from the ADO recordset into Excel
.Range("A2").CopyFromRecordset _
Data:=rst, _
MaxRows:=.Cells.Rows.Count, _
MaxColumns:=.Cells.Columns.Count
End With

Kind regards,
Alex
 
I like the solution for getting field names into Excel when using the
CopyFromRecordset. It is one of those so obvious you feel stupid for not
thinking of it.
I will agreee that TransferSpreadsheet has some advantages; however, there
is a time and place for everything. I have a couple of cases where I have to
create an entirely new workbook, do a lot of formatting, pull data in from
Access, do subtotals, and create charts. In this situation, the
TransferSpreadsheet is not even a candidate for the job.
 
Good stuff indeed. alexhatzisavas - thanks for the code.

Klatuu, I would like to know more about your process ("create an
entirely new workbook, do a lot of formatting, pull data in from
Access, do subtotals, and create charts") It sounds like some stuff I
am working on/struggling with. Have you posted anything on your
trials/tribulations, or do you have any weblinks that you found useful?

Troy, I think the logic escapes me, but I have found indications that I
can be related to formulas gone evil, as well as data formatting (I'm
going to guess that it is something like, copy->paste from access to
excel pastes in unicode or formatZ or perhaps as and that does not jive
with the expected format, I'll shut up now as that is
speculation-out-the-arse).
 
I had to do a lot of reading, posting questions, trial and error. One trick
is to open an new workbook in Excel, do a Record New Macro, do some of the
things I needed to do, and save the macro. Then I would cut the code created
in hte macro and adapt it to Access. You have to be careful with this,
because of the differences in the ways Excel and Access address objects.

Post back an E-mail address and I will send you a couple of examples.
 
This sounds a lot like what I want to do, I copied the code and have 2
questions:
1) Is this code to be copied & pasted into Excel VB or Access VB
2) I copied this into Excel VB but I get the error "User-defined type not
defined" and then it references line Dim MyFiles As FileDialog .
If you could help that would be great so I can use this.
Thanks!
Stacey
 
Back
Top