Transferspreadsheet Problems 2

[pd2004]

Hello,

I have a database from which I am using the following line to export data:

Application.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "output_query_1" , "G:\GroupA\AAA\Compliance.xls", True

This method worked for a long time, but today, for some reason, it treats each field in the query as if it were a parameter( opens an input box requesting values to be entered for each field name as though I were using [fieldname]). Strangely, if I change the fields to aliases (I think that is the correct term) so that Field1 is now Field1: Field1, then it works.

Also, without making the alias change above, the "outputto" command does export the query just fine. The query also opens without incident when just calling the Docnd.Openquery function.

Can someone tell me what is going on here? I suspect it may be a missing reference to a library, but I don't know. Any help is much appreciated.

Thank you,

Pat

 

[Remou]

Just guessing, but can you try exporting to a different directory or file name and see if that works?

http://lessthandot.com

 

[pd2004]

Hi Remou,

Thank you for your response. However, I have tried that and all other things I could think of. As I say, the outputto method works on the same query. The main thing I am focused on is why the query will export if the fields are set up as aliases. Very strange.

Thanks for your help.

Pat

 

[henniec]

Hi Pat,

Why don't you declare a recordset and the set your record set

eg.

Dim rs As Recordset

Do the necessary to open your Excel file and set the Worksheeet.

Set rs = db.OpenRecordset("output_query_1", dbOpenSnapshot)

And then use the function

.Range("A2").CopyFromRecordset rs

I find this easier with less problems than the one you are using.

Hennie

It transfers the whole query to where you want it.

 

[pd2004]

Thank you so much, Hennie. I really appreciate your help. I will give it a try. If it is more reliable I will be using it from now on.

Thank you,

Pat

 

[henniec]

Its a pleasure. I think you will find there is less hassles. I assume all the other declarations has been done
Use it as a With objSht (your spread sheetname).

Hennie

 

[pd2004]

Hello Hennie,

Could you provide a little guidance? I have included the code that I tried, but I get an error message saying " Run-time error '430': Class does not support Automation or does not support expected interface". I appreciate any suggestions you could offer.

Thank you,

Pat


Sub test2()

Dim rs As Recordset

Dim appExcel As Excel.Application
Set appExcel = CreateObject("Excel.application")

appExcel.Workbooks.Add
appExcel.Visible = True

ActiveWorkbook.SaveAs "C:\Myfolder\Myfile.xls"


Set rs = Application.CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)
appExcel.ActiveWorkbook.ActiveSheet.Range"A2")
.CopyFromRecordset rs
End Sub

 

[PHV]

I'd try this:

Sub test2()
Dim rs As DAO.Recordset
Dim appExcel As Excel.Application
Set appExcel = CreateObject("Excel.application")
appExcel.Workbooks.Add
appExcel.Visible = True
Set rs = Application.CurrentDb.OpenRecordset("Query1", dbOpenSnapshot)
appExcel.ActiveWorkbook.ActiveSheet.Range("A2").CopyFromRecordset rs
appExcel.ActiveWorkbook.SaveAs "C:\Myfolder\Myfile.xls"
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pd2004]

Thank you, PH. The change that I can see in the code is adding the DAO to the Dim rs As DAO.Recordset line. I am interested in learning how this effects the change. I do have the Microsoft DAO 3.6 Object Library as a checked available reference. Does that have something to do with it? I appreciate your help and I will try the code right now.

Thank you again,

Pat

 

[henniec]

Hi Pat,
I think PHV gave you the information you need working with one worksheet and I do believe your code is working. Below I added some additional information should you need to work with more than one worksheet in a workbook. For some projects I have about 10 worksheets and it become critical to specify each correctly.
Some pointers to work with more than one worksheet. I do believe you need to set each object you are working with. In this case the workbook and the worksheet. I feel it is important to activate the worksheet in question and as such it becomes the one on the screen. When you move to the next worksheet, set and activate it. What is very important is that your worksheet name, whether it is “worksheet1 or worksheet2” or “ABC or DEF” make sure it is exactly the same as on the worksheet tab (objWkb.Worksheets("RSP").Activate).
Good luck with future transfers.
Hennie

Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object
Dim rs As Recordset

Set rs = db.OpenRecordset("tblMisaRSP", dbOpenSnapshot)
'Set the object variable to reference the file you want to see.

With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
Set objSht = objWkb.Worksheets(“RSP”) 'RSP
objWkb.Worksheets("RSP").Activate
objWkb.Windows("RSP").Visible = True
With objSht
'Copy data from the record set
On Error Resume Next
.Range("A2").CopyFromRecordset rs
End With
.
.
.
End With

 

[pd2004]

Thank you, Hennie and PHV. I appreciate all of your help.

Pat