Export Several Queries to Excel sheet and specify which cells (1 Viewer)

[Hayley Baxter]

I have set up a master spreadsheet which contains lots of tables of data with certain formats set up.

I have set up a form with a command button

I am looking to export the values for these tables from several access queries into this master spreadsheet so I will need to somehow state which cells each piece of data sits in. I am not quite sure how this is done. I know how to export one query to an excel sheet but do not know how to go about exporting all of the queries I need into the one excel sheet to specific cells at the click of one cmdbutton.

Hope the above is clear

Any help is much appreciated.

Thanks
Hay

 

[KenHigg]

Hum...

Maybe you can export each to it's own .xls and the reference these from a master .xls(?)

 

[Hayley Baxter]

Thanks for the reply Ken.

I'd ideally like to avoid exporting each to it's own. Will keep trying different things and see if I can get something to work for me.

If you think of anything else yourself please let me know.

Hay

 

[david.brent]

Exporting to Excel

Hayley,
In my previous job (Management Information - I'm a DBA now), I used to do this sort of thing everyday. The key to making well presented excel spreadsheets from your Access table data is embedding Excel VBA in your Access Functions/Subs. To do this you must set a reference to the Excel object library. Now look at this snippet of code...

Dim appExcel As New Excel.Application
Dim ExcelBook As Excel.Workbook
Dim ExcelSheet As Excel.Worksheet

Set ExcelBook = appExcel.Workbooks.Add

Set ExcelSheet = ExcelBook.Worksheets(1)

This gives you a nice new excel workbook and sets the sheet to sheet1.

Excel is not visible, it's in the background. I would then normally set up a recordset from the first table in question (Set myRecs = Currentdb.OpenRecordset...)

Then populate the spreadsheet something with something like this.
With myRecs
Do While not .eof
With ExcelSheet
.Range("A1").value = myRecs.Fields(0)
.Range("B1").value = myRecs.Fields(1)

and so on...
End With

.movenext
Loop
End With
You can use other loops counters to set the range...

.Range("A" & rowLoop).value = ...
.Range(Chr$(colLoop) & "2").value = ...
and so on.

You can tart things up - format cells add color, images boxes etc with Excel VBA. The way I leared was to do Macros in Excel then edit them and look at the code. It will give you no end of headaches but the resulting spreadsheet is only limitted by your imagination.

At the end off the code...

ExcelBook.Close True, Filename
Set appExcel = Nothing
Frees everything up nicely and saves the spreadsheet.

Couple of other things.
Set the variables at Function/Sub level only. Scope them at any other level and it will bugger Excel up.
If you're debugging and you want to see the spreadsheet type appExcel.Visible = true and it will appear as if by magic.

I don't know what level you're at with Access, I might be teaching you to suck eggs. If it's a bit involved or I'm not making much sense let me know and I'll try to help a bit more specifically (look at the tables and do so sample code maybe). I'm doing this from memory but I have lots of examples of code tucked away somewhere.

Take Care and great win last night.

Carl

 

[Hayley Baxter]

Thanks for the detailed response Carl, that has certainly helped point me in the right direction. I will have a bash at this tomorrow.

If I need anything else I will be sure to let you know.

Hay

PS Excellent win last night - wasn't pretty but hell do I care

 

[KenHigg]

Hum... Interesting - Thanks Carl!

 

[wypowersmoke]

Carl,

I am new to VB and need to do this exact thing, spit out information from acess to certain cells in excel. But I don't know how to make your code work. Do you put it in a new sub? I don't understand how to set the variables at the function level. Basically could you explain how to do this for a complete idiot.

Thank you very much.