Export to Excel ... 2

[educate889]

Hello experts...

I need to be able to export multiple queries to an Excel workbook and have each query create a new worksheet tab.

I can get it to export but the last query is all that is left after it overwrites the one before. Is there a way to create/name a new tab on the fly??? I hope so. . .

I appreciate any help you may give!

'------------------------------------------------------------
' Export queries to Excel Spreadsheet in users H:
'
'------------------------------------------------------------
Public Function Create_Export()
On Error GoTo Create_Export_Err


DoCmd.SetWarnings False
DoCmd.OpenQuery "XCM102_SLSTOTALS", acViewNormal, acEdit
DoCmd.OutputTo acQuery, "", "MicrosoftExcel(*.xls)", "H:\XCM_RawData2.xls", False, ""
DoCmd.Close acQuery, "XCM102_slsTOTALS", acSavePrompt
DoCmd.OpenQuery "XCM101_ICTOTALS", acViewNormal, acEdit
DoCmd.OutputTo acQuery, "", "MicrosoftExcel(*.xls)", "H:\XCM_RawData2.xls", True, ""
DoCmd.Close acQuery, "XCM101_ICTOTALS", acSavePrompt
DoCmd.SetWarnings True

Create_Export_Exit:
Exit Function

Create_Export_Err:
MsgBox Error$
Resume Create_Export_Exit

End Function

 

[PaulBricker]

Use the TransferSpreadsheet Method and in the TableName argument just put the name of your query. Excel will put each query on a separate sheet and name the sheet the name of the query. If you had a list of the queries, you wouldn't need to open them with the OpenQuery method, you would just have to loop thru the query name and something like this.

Dim strName as String
For "loopStart" to "loopEnd"
strName = "FirstQueryName"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strName,"H:\XCM_RawData2.xls", False
Next

What you have to do is have some way to set the value for strName to the name of each of your queries. Otherwise, you can hardcode the names in the TransferSpreadsheet Method and just run one after the other.

Paul

 

[educate889]

Can you someone spell this out a little more . . . I am a still "cutting my teeth" with VBA programming and modules.

Especially, how I could loop through the various queries I have to run.

Plus when I run this is errors saying "external table in not in expected format!"
Please help,,,,my deadline is quickly approaching.

 

[PaulBricker]

Before you try the loop thing, lets get the TransferSpreadsheet method debugged. Post the line you are using for the TransferSpreadsheet Method. Also, is there anything different about the queries. Are the tables linked or something.

Paul

 

[educate889]

Here is that line of code

DoCmd.TransferSpreadsheet acExport, 8, "XCM101_ICTOTALS", "H:\XCM_RawData2.xls", False


This qry queries two other queries bringing all needed info together. . . table they query is in database not linked . . .

using Office 97 sr2

 

[lewatkin]

Paul is exactly right. you need to pass the names of the queries to a variable. that can be done simply with by putting the query names in a table. that way, you could set that table as a recordset then loop through the recordset. this would allow you to pass the query name as a variable to run the transferspreadsheet code, then move to the next record in the table. For example....

Your table would have 1 field that contains the query name:

tblQueries
strQuery, Data Type = Text
Query1
Query2
Query3
Query4 (etc) - as many as you need

Function MyExport()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strMyQry as String 'this is the query name variable

Set db = currentdb()
Set rs = db.openrecordset("tblQueries&quot

Do until rs.EOF
strMyQry = rs("strQuery&quot

DoCmd.TransferSpreadsheet acExport, 8, strMyQuery, "H:\XCM_RawData2.xls", False
rs.MoveNext 'move to the next record
Loop

End Function

That should take care of it for you. In the OnClick Event of your command button you would put: =MyExport()

Hope this helps!!

Lee

 

[educate889]

Okay, I see where you (lewakin)are headed... I think.

However, I can't even get the transferspreadsheet to work. I get a runtime error 3274 External table in unexpected format. In some other posts, I am being lead to believe that this won't work with Excel 97 sr2. Do you have any knowledge of this ?

I am very frustrated, and my deadline in month end.

Please help!

 

[PaulBricker]

Try changing the argument "8" to "5" (without the quotes). Or try leaving the argument blank by just inserting the commas (like my two examples).

DoCmd.TransferSpreadsheet acExport, 5, "XCM101_ICTOTALS", "H:\XCM_RawData2.xls", False

DoCmd.TransferSpreadsheet acExport, , "XCM101_ICTOTALS", "H:\XCM_RawData2.xls", False

Paul

 

[educate889]

I got it to export via acSpreadsheetTypeExcel3...

Now I am trying to move on a bit . . .

This way dumps all the queries into one sheet , which is still a problem but better than before. . . any ideas as to how to get in separate sheets within one workbook.

Second, I am prompted for same date range in all queries and would like to only have user enter this once. . .

anyways I appreciate the continuing help

 

[educate889]

Lewakin,

I am trying your method but get a compile error "method or data member not found" (see code below) It points me back to function and highlights the EOF . . .

Please advise!





Function MyExport()
Dim db As DAO.Database
Dim rs As DAO.Database
Dim strMyQry As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("xcm_qrys&quot

Do until rs.EOF
strMyQry = rs("qryName&quot

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, strMyQry, "H:\XCM_RawData.xls", False
rs.MoveNext
Loop

End Function

 

[PaulBricker]

Well to get the info on to separate sheets, the TableName argument has to change each time you run the TransferSpreadsheet method. lewatkin's code has you put the names of your queries in a Table and loop thru the records picking out the name of the query, running it thru the TransferSpreadsheet method, move to the next record and repeat the process. This is not a bad way to do it. It really depends on your level of experience. I would at least try that method first.
1. Create a table named tblQueries
2. Add one field to the table called strQuery, datatype text.
3. Add all the names of your queries to this table.
4. Copy and paste this Function into a new module. Name the module modMyExport
Function MyExport()

Dim rs as Recordset
Dim strMyQry as String 'this is the query name variable


Set rs = CurrentDb.OpenRecordset("tblQueries&quot

Do until rs.EOF
strMyQry = rs("strQuery&quot

DoCmd.TransferSpreadsheet acExport, 3, strMyQuery, "H:\XCM_RawData2.xls", False
rs.MoveNext 'move to the next record
Loop

End Function

5. In the Click Event for a button on a form (any form) put
Call MyExport()

6. Try running it and post back any problems.

Paul

 

[educate889]

I modified my tbl of queries and ran the code and it DOES WORK!

Question #1 -
However, it still dumps it to single worksheet in XCM_RAWDATA file. Is there any way this can be modified to create a new sheet.

Question #2 -
Is there a way to have the user enter the date range once instead of every time (7x) for each query. Could I dim the date (or use input box) and then use it in each query???

Thanks again for your continuing help. . . I can feel it I am almost there.

 

[PaulBricker]

Post your Function so we can see it. Also, look on the Sheet Tab and tell me what name the tab has.

The best way to pass valus to various queries is to have a form open that has the two dates in two textboxes. Then in the queries you would have criteria that says
Between Forms!FormName!StartDate And Forms!FormName!EndDate

Then leave the form open while this is all processing.

Paul

 

[educate889]

Function MyExport()

Dim rs As Recordset
Dim strMyQry As String 'this is the query name variable


Set rs = CurrentDb.OpenRecordset("tblQueries&quot

Do Until rs.EOF
strMyQry = rs("strQuery&quot

DoCmd.TransferSpreadsheet acExport, 0, strMyQry, "H:\XCM_RawData.xls", False
rs.MoveNext 'move to the next record
Loop

End Function



Tab has XCM_RAWDATA

Sorry Paul . . .

 

[PaulBricker]

I've added the line in BOLD so you can try that. In 97 you had to move to the first record so that may be an issue. Also, what does XCM_RAWDATA mean to you in terms of the names of your queries. Is it the name of one of the queries in your Table.
Paul

Function MyExport()

Dim rs As Recordset
Dim strMyQry As String 'this is the query name variable


Set rs = CurrentDb.OpenRecordset("tblQueries&quot
rs.MoveFirst
Do Until rs.EOF
strMyQry = rs("strQuery&quot

DoCmd.TransferSpreadsheet acExport, 0, strMyQry, "H:\XCM_RawData.xls", False
rs.MoveNext 'move to the next record
Loop

End Function

 

[educate889]

Thanks for your speedy help...the XCM_RAWDATA is only the name of the file ... I even changed the path to H:\XCM_RAWDATA21.xls and when I ran it I got this file name with one worksheet called XCM_DATA21. . . .

Here is a list of tblQueries...
strQuery
XCM101_ICTOTALS
XCM102_SLSTOTALS
XCM103_PMTOTALS
XCM104_ICDOLRS
XCM105_ICMARGIN
XCM106_NEWBIZ
XCM107_OntimeSLS
XCM108_OntimePM

I guess this isn't the biggest deal actually, more pressing now is to automate the date range prompts to be only one not 7.

 

[PaulBricker]

I'm out of the office in about 10 minutes so this will have to be fast or wait a couple hours till I'm back. I would create a form that has two textboxes on it that will collect the start and end dates you need. Then from the button that Calls the TransferSpreadsheet code you can open the Form. Then you would move Call MyExport() to a button on the Date form. Then in each of your queries you would add criteria that references this form.

I'll check back later.

Paul

 

[educate889]

Paul

Thanks for the idea . . . have a star

I have gone through and modified all queries to accept these changes and things are running smoothly...

The last thing is the naming of the excel files.. .
In the code we hardcoded the name and dump it into H:

Is there a way to name the file through the command buttons they press...What I mean is that my user is going to have to run the code for each division (3) of our company. However, the way that it is coded now the file H:\XCM_RAWDATA.xls will be overwritten. Is there a way on my form to declare a global variable or something that will automatically name the file depending on which button they pushed for which division. I could put on same form where they will enter the query prompts . . .

I guess the other option would be to copy and paste 2x and rename the hardcoded filename in each. Then when they run for certain division they will actually be executing separate code in each. I am always hesitant to do it this way in case I need to modify code down the road, then I have to remember to make the changes in all the places.

Any more ideas????


I am almost there . . .

 

[lewatkin]

educate889,

sorry i have not gotten back with you - seems you are in great hands though - i caught his thread stating he would be gone - thought i would offer this tidbit. On the form with the hidden property set to false, add a textbox. When the user clicks Button1, set the value of that text box to 1, when the user clicks button 2, set it to 2, etc. etc.
Run your code with a different twist. Use a Select statement to select the value of the textbox, then run the code that way. Kinda long, but the results would be exactly what you are looking for. Example - assuming on the form you had Button1, Button2, Button3 (for each division) and a hidden text box Text1.

Private Sub Button1_Click()
me.text1 = "1"
MyExport
End Sub

Private Sub Button2_Click()
me.text1 = "2"
MyExport
End Sub

Private Sub Button3_Click()
me.text1 = "2"
MyExport
End Sub

Function MyExport()

Dim rs As Recordset
Dim strMyQry As String 'this is the query name variable


Set rs = CurrentDb.OpenRecordset("tblQueries&quot

<BOLD> Select Case Forms!frmName!Text1
Case = 1 'division 1
Do Until rs.EOF
strMyQry = rs(&quot;strQuery&quot

DoCmd.TransferSpreadsheet acExport, 0, strMyQry, &quot;H:\Division1XCM_RawData.xls&quot;, False
rs.MoveNext 'move to the next record
Loop

Case = 2 'division 2
Do Until rs.EOF
strMyQry = rs(&quot;strQuery&quot

DoCmd.TransferSpreadsheet acExport, 0, strMyQry, &quot;H:\Division2XCM_RawData.xls&quot;, False
rs.MoveNext 'move to the next record
Loop

Case = 3 'division 3
Do Until rs.EOF
strMyQry = rs(&quot;strQuery&quot

DoCmd.TransferSpreadsheet acExport, 0, strMyQry, &quot;H:\Division3XCM_RawData.xls&quot;, False
rs.MoveNext 'move to the next record
Loop

End Select


End Function

I would do it a shade different, but with a different coding, but just getting into VBA, this would work perfectly. Just an idea! Hope it helps!

Lee

 

[PaulBricker]

Well, the code looks great for dealing with the different Divisions so I'll skip over that but I assume we are still dealing with not being able to get our queries into different sheets. A couple things to check.
1. Do you have enough sheets in the Excel file to handle all your queries. If not, add enough to do that.
2. Try putting the names of each query on the tabs of your excel sheets and see what that does.
3. It's possible this is a version problem. A97 may not handle the TransferSpreadsheet the same as 2000 which I think Lee and I are both working in. I do have a copy of 97 at the office I can look at but I won't be able to do that till the morning.

Paul