Exporting a query to an existing spreadsheet

[CBrighton]

I have a spreadsheet with 13 worksheet (12 months & the 13th worksheet for a user guide). Each of the 12 month worksheets follows the same template, B2:K2 contain field names.

I need to export a years worth of data from 2 tables in a 1 to many relationship (1 - staff data, Many - Breach data, link is purely to get data like staff name, location, etc from the staff data table) into the appropriate worksheet based on the month of one of the fields (Date/Time format).

I have been playing with both OutputTo and TransferSpreadsheet but I am having trouble getting either to populate an existing worksheet in an existing workbook.

I spent a good 3-4 hours this morning working on a different Excel automation in VBA and am really hoping I can get this working through a standard function rather than having to code another load of automation.

Google has been limited help as things like "access export existing spreadsheet" are commonly used words.

Any help anyone can give about populating existing spreadsheets with data from Access without automating it in VBA would be greatly appreciated.

 

[SOS]

Bob Larson has code for doing just this thing. You would set your query to be whatever month you need and then export it using this code. You can iterate through to get the whole thing done if you want.

 

[CBrighton]

Thanks.

I finish work in 7 mins so I'll look into this tomorrow.

 

[vbaInet]

Perhaps this example on Bob's (aka boblarson) website might provide some insight:

http://www.btabdevelopment.com/ts/default.aspx?PageId=49

Edit: Talk about slow

 

[CBrighton]

I've been trying this now, using the link provided by SOS (the one provided by vbaInet has strPath defined in the code rather than as a parameter), but I get "Error 1004 - Select method of Range class failed" on the line containing "xlWSh.Range("A1").Select" (just before the For...Next loop which populated the field names, 7th line after the block of Dim statements).

Anyone know why?

Access 2003 btw.

 

[CBrighton]

I seem to have fixed it by replacing the offending line with "xlWSh.Select", so it selects the worksheet rather than cell A1 on the worksheet.

 

[boblarson]

I've been trying this now, using the link provided by SOS (the one provided by vbaInet has strPath defined in the code rather than as a parameter), but I get "Error 1004 - Select method of Range class failed" on the line containing "xlWSh.Range("A1").Select" (just before the For...Next loop which populated the field names, 7th line after the block of Dim statements).

Anyone know why?

Access 2003 btw.

That's strange. I just tested (with Access 2003) and it worked fine for me. The one provided by SOS that is.

 

[CBrighton]

Don't know what to tell you.

I had to modify it heavily anyway as I am using an array to hold a query for each month of the year which are then looped through populating a different tab depending on which query is being used.

It was a very useful place to start though, thanks.

 

[boblarson]

Don't know what to tell you.

I had to modify it heavily anyway as I am using an array to hold a query for each month of the year which are then looped through populating a different tab depending on which query is being used.

You shouldn't have had to modify that function in any way shape or form. In fact, it is bad you did. What you SHOULD have done is to create your own function which loops but just calls that function each time. That is what reusable code is for. So, now if you ever need to do an export from somewhere else, you'll need to modify something again instead of just reusing the function that was there.

 

[CBrighton]

True I suppose, what I should have done is had 2 versions of the function, one with a saveas & close at the end and a second with a save & close at the end.

I wanted to populate a template with 12 querries but not save over the template, as such I would need to run the first query using a function with saveas and then run the remaining 11 on the new file using the function with save.

Still, it's a rarity that I am asked for a spreadsheet export into an existing document, generally speaking OutputTo or TransferSpreadsheet does what I need.

Besides, I still have a copy of the original function retained for if I need it, plus it's online.

 

[boblarson]

Again just for your programming knowledge - You don't use two versions of the function but you can add optional parameters to be able to make it function in many different ways. So, you could have added a

Optional blnSaveAs As Boolean

And then if blnSaveAs is False, you use the Save but if true you use the SaveAs. Then you have one function but it handles multiple things.