Transferspreadsheet export to specific excel sheet 1

[Shippwreck]

Hey All,

I'm trying to export the result of three queries to an excel file. The sheets that the queries need to end up in are named sheets... lets call them apple, pear, orange. Below is the code that i'm using:

strSql = "select query here"
Set rs3 = dbs.OpenRecordset(strSql, dbOpenSnapshot)
With dbs
    Set qdf = .CreateQueryDef("tmpapple", strSql)
    DoCmd.OpenQuery "tmpapple"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tmpapple", File_path, False, "apple"
    .QueryDefs.Delete "tmpapple"
End With

Obviously thats just one of them, but they are all identical with only name and SQL changes...

Now here's where it gets interesting... if i run the code and set file_path to the correct file with correctly named sheets, it doesn't put the data there... instead it creates new sheets called apple1, pear1, orange1 etc... ok, so i tried doing it without correctly named sheets and it then says that the sheet objects don't exist...

Additionally the export code is taken from a different project that does exactly the same thing and runs perfectly every day (its a daily report)... its code is below:

File_path2 = ahtCommonFileOpenSave()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Main Report", File_path2, False, "data 1"

That exports perfectly to the sheet called data 1... someone please help as this has been frustrating me for 2 days now...

Really ANY help is appreciated!

Thanks

http://www.simpleits.co.uk

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[Bong]

I don't really know much about database operations or SQL but one difference (between the code that works and the code that doesn't) is that in the latter you have:
File_path2 = ahtCommonFileOpenSave() and then later,
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Main Report", [red]File_path2[/red], False, "data 1", whereas in the former I see no setting of File_path.

_________________
Bob Rashkin

 

[Shippwreck]

Sorry, file_path is set in exactly the same way... just earlier on in the code and so it wasn't copied...

http://www.simpleits.co.uk

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[Shippwreck]

Ok then... can i ask a different question... if you had a table that was anywhere between 6000 and 18000 lines long and 29 fields across how would you export it via VBA/Macro to a specific sheet in a specific workbook?

Thanks

http://www.simpleits.co.uk

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[AnotherHiggins]

Turn on you macro recorder (Tools > Macro > Record new macro) and go through the steps of copying the sheet to another workbook. (Hint: I'd probably right click on the sheet tab and copy the entire sheet to a new workbook instead of tying to copy individual cells.)

Post generated code if you need help cleaning it up.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[combo]

In excel (or excel automation) you can try CopyFromRecordset method, that applies to excel range object. See excel vba help for details.

combo

 

[Shippwreck]

Hi anotherhiggins, i thought about doing that but it does feel like a bit of a bodge... it would mean that the user runs the macro, selects the first excel file to export to, that exports, has an auto run macro on startup that then asks the user for the final excel file to export to and then finishes... (yes it has to ask where to export to as many different users will be using this and WILL keep the files in different places as users like to make our lives difficult I think i'll be keeping that as my last resort

Hi combo, hmmm copyfromrecordset looks promising... as long as it can dump the whole recordset in one go and not do it line by line it might be a winner... (i have used one before that did line by line... you can imagine how long 18000 lines took!)

Thanks for your help guys!

http://www.simpleits.co.uk

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me

 

[combo]

There's no need to copy record by record. Moreover, it is possible to limit table size. Here's an example:

http://support.microsoft.com/kb/246335.

combo

 

[Shippwreck]

ahhhh... genius... thanks a lot i have it working now and its much simpler:

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open (File_path)

strSql = "Select query"
Set rs1 = dbs.OpenRecordset(strSql, dbOpenSnapshot)
oApp.Sheets("apples").Range("A1").CopyFromRecordset rs1

http://www.simpleits.co.uk

http://www.tazforum.thetazzone.com

"Google is god...of the internet" - Me