A learning experiene

[Evenflow]

Hello. Let me start off by saying I know very little about VBA, but I'm learning something new every day and I'm having fun with it. Now, I'm trying to Automate our database a little and get some queries to export to excel, the code below is what I have so far. I know this code will only do 1 query to 1 sheet, but I figure if I can get it working, I could mess around with it and get it to export multiple queries to mult. sheets. Right now this will bring up a new excel workbook and go to sheet 1, but it wont pull the query data. If someone could give me a hint, it would be appreciated. Thanks

Sub FunkyColdMedina()

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Const conMAX_ROWS = 20000
Const conSHT_NAME = "Sheet1"
Const conWKB_NAME = "workbook location"
Const conRANGE = ?

Set db = DBEngine.OpenDatabase("My database")
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("Query1", dbOpenSnapshot)
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
objSht.Range(conRANGE).CopyFromRecordset rs
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing
End Sub

 

[DJN]

Maybe a silly question, but are you sure you the query is returning data?

 

[Evenflow]

Yes, the query does return data.

 

[Bat17]

What does happen when you run the code?
Have you tried adding a range for it to copy the data to?

Peter

 

[Evenflow]

When the code runs...it opens a new excel workbook and goes to sheet1 of the workbook. It isn't pulling the data from the query. Not knowing enough about VBA, I've pretty much just winged my way though this. If you're talking about the Const conRANGE = ?, I don't know what I should add there.

 

[Bat17]

before we chase red-herrings, how do you know that the query is not pulling data?

Peter

 

[Bat17]

try:-
Const conRANGE = "A:1"

It is where you want to put the data in the Worksheet
if you look up CopyFromRecordset in help in excel it gives detail on how to use it.

Peter

 

[Evenflow]

I just got it bat. I replaced:

Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
objSht.Range(conRANGE).CopyFromRecordset rs

With

Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
objSht.Select
objSht.Range(conRANGE).CopyFromRecordset rs

Doing this did the trick. Thanks for your time and effort.

 

[Evenflow]

It was creating a new sheet, but not actually selecting the sheet to place the information.

 

[Pat Hartman]

Wouldn't it have been easier in this instance to have used TransferSpreadsheet?

 

[Bat17]

not tested, but I think that if you had added the range then you could add the recordset with out having to select the sheet which will let the code run faster.

Peter

 

[Jibbadiah]

Try looking at the following post... similar to your question

http://www.access-programmers.co.uk/forums/search.php?searchid=477915