Exporting query results to Excel from Access97

[CumbrianCanary]

Exporting query results to Excel

Hi,

I am writing some code to export results from a query to Excel. I have managed to get the correct Excel file open and can write test to it no problem. The issue comes when I want to use data from a query rather than set text that is hard coded.

I have replaced the set text section in the code with

strSQL = CurrentDb.QueryDefs("qryMyQuery").SQL
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If Not rst.BOF Then
rst.MoveFirst
'For this template, the data must be placed in the appropriate cells of the spreadsheet
Do While Not rst.EOF
With strWorkbookName
.Sheets("Sheet1").Range("A3") = rst.Fields("FieldName1")
.Sheets("Sheet1").Range("B3") = rst.Fields("FieldName2")
.Sheets("Sheet1").Range("C3") = rst.Fields("FieldName3")
.Sheets("Sheet1").Range("D3") = rst.Fields("FieldName4")
.Sheets("Sheet1").Range("E3") = rst.Fields("FieldName5")
End With
rst.MoveNext
Loop
rst.Close
End If

But it is causing an 424 Object required error. I think it may be due to the

strSQL = CurrentDb.QueryDefs("qryMyQuery").SQL

Does this work in Access97? It comes up with some square boxes within the string. It seemed the easy way to convert the SQL into VBA, but I expect I need to learn how to do this. Am I barking up the wrong tree with this one?

Thanks in advance.

CC

 

[HiTechCoach]

Exporting query results to Excel

Hi,

I am writing some code to export results from a query to Excel. I have managed to get the correct Excel file open and can write test to it no problem. The issue comes when I want to use data from a query rather than set text that is hard coded.

I have replaced the set text section in the code with



But it is causing an 424 Object required error. I think it may be due to the



Does this work in Access97? It comes up with some square boxes within the string. It seemed the easy way to convert the SQL into VBA, but I expect I need to learn how to do this. Am I barking up the wrong tree with this one?

Thanks in advance.

CC

CC,

Why do you need to convert the store query to a string in VBA? I would not do that.

I would do it this way:

change this

strSQL = CurrentDb.QueryDefs("qryMyQuery").SQL
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

to be

Set rst = CurrentDb.OpenRecordset("qryMyQuery", dbOpenSnapshot)

This may also be helpful:

Export Data To Excel

 

[boblarson]

By the way, the code you currently have will keep overwriting the same cells every time you go to a new record. You can't have static ranges if you want to copy more than one record in.

 

[HiTechCoach]

By the way, the code you currently have will keep overwriting the same cells every time you go to a new record. You can't have static ranges if you want to copy more than one record in.

Nice catch Bob!

I had not looked into the code that far.

 

[CumbrianCanary]

Thanks for the replies. I have changed the section of code you suggested and it still comes up with the same error.

Hadn't thought about the looping, thanks for pointing that out. Fairly sure I can fix that no problem, once I can get the query into excel!!

 

[MStef]

For your task I can offer "DemoQryInExcA97.mdb" (attachment, zip).
Open Form and try. Look at VBA, Query, Table.

 

[CumbrianCanary]

Thanks MStef,

This exports the whole of the query into excel. The problem I have is the data needs to go into specific cells within an pre-formatted spreadsheet, which is why I have been trying to find a code to send individual fields into specific cells. I think I am close, but still keep getting the same error :-(

 

[CumbrianCanary]

Just to update this, the code comes up with the error when it runs the strWorkbookname line of code. It uses this earlier in the code and has no problems with it. The spreadsheet exists in the correct folder so I am not sure what to do next. Any help would be gratefully received.

CC

Set rst = CurrentDb.OpenRecordset("qryMyQuery", dbOpenSnapshot)
If Not rst.BOF Then
rst.MoveFirst
Do While Not rst.EOF
With strWorkbookName
.Sheets("Sheet1").Range("A3") = rst.Fields("FieldName1")
.Sheets("Sheet1").Range("B3") = rst.Fields("FieldName2")
.Sheets("Sheet1").Range("C3") = rst.Fields("FieldName3")
.Sheets("Sheet1").Range("D3") = rst.Fields("FieldName4")
.Sheets("Sheet1").Range("E3") = rst.Fields("FieldName5")
End With
rst.MoveNext
Loop
rst.Close
End If

 

[boblarson]

It should be referring to the app object and then:

With apXL.Workbooks(strWorkbookName)

 

[boblarson]

Although I can't tell for sure because you haven't included the ENTIRE code you are using INCLUDING the declarations.

 

[CumbrianCanary]

Thanks Bob,

The reason I didn't include the rest of the code was that it worked fine when I just had a section in here saying put "This Text" in A1. I then tried to expand to put results from the query into the excel cells and this is where is went wrong. I have found that if I remove the with statement and use

objActiveWksh.Cells(1, 1) = rst.Fields("Field1")

This works and I am guessing this is because I have already set this variable, so you are right it would have helped if I had shown all the code.

Thanks for your time in helping me with this problem, as always this is a great resource