Export To Excel losing macro's

[Skip Bisconer]

I am not getting what I need with this function. It sends the data to Excel OK but I need the Excel file to retain the macros set into it and this function seems to make a new workbook each time.

These spreadsheet files get updated monthly and emailed to the CC holder for authorization of charges. The holder fill in GL Expense codes and customer information from a combobox in the worksheet and email the file back to the administrator who has the Excel file linked into his database.

Can someone point me in the correct direction? Thanks for looking at my problem

   DoCmd.OutputTo acOutputQuery, "CurrentCharges11012", "Excel97-Excel2003Workbook(*.xls)", _
    "C:\Amex\Young.xls", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "CurrentCharges11020", "Excel97-Excel2003Workbook(*.xls)", _
    "C:\Amex\Murphy.xls", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "CurrentCharges11038", "Excel97-Excel2003Workbook(*.xls)", _
    "C:\Amex\Hyler.xls", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "CurrentCharges11046", "Excel97-Excel2003Workbook(*.xls)", _
    "C:\Amex\Donati.xls", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "CurrentCharges11079", "Excel97-Excel2003Workbook(*.xls)", _
    "C:\Amex\Watkins.xls", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "CurrentCharges11095", "Excel97-Excel2003Workbook(*.xls)", _
    "C:\Amex\O'Boyle.xls", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "CurrentCharges11103", "Excel97-Excel2003Workbook(*.xls)", _
    "C:\Amex\Brun.xls", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "CurrentCharges11111", "Excel97-Excel2003Workbook(*.xls)", _
    "C:\Amex\Bourgeois.xls", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "CurrentCharges11152", "Excel97-Excel2003Workbook(*.xls)", _
    "C:\Amex\Bisconer.xls", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "CurrentCharges12002", "Excel97-Excel2003Workbook(*.xls)", _
    "C:\Amex\Kowaleski.xls", False, "", , acExportQualityScreen
    DoCmd.OutputTo acOutputQuery, "CurrentCharges12085", "Excel97-Excel2003Workbook(*.xls)", _
    "C:\Amex\Briare.xls", False, "", , acExportQualityScreen

 

[nIGHTmAYOR]

oh , if you need to just edit an excel than rather creating a whole new one to retain specific functions use the following code instead :

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set xlWB = objXL.Workbooks.Open("YourPathToYourExcelFileHere")
Set xlWS = xlWB.Worksheets("YourWorkSheetNameHere")
 
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("YourQueryHere")
rs.MoveFirst
For i = 1 to rs.RecordCount
  With xlWS
  ' assign records to specific cells
  .Range("A" & i ).Value = rs.Fields("YourQueryHereF[COLOR=black]ieldName1[/COLOR]").Value
  .Range("B" & i ).Value = rs.Fields("YourQueryHereF[COLOR=black]ieldName2[/COLOR]").Value
  .Range("C" & i ).Value = rs.Fields("YourQueryHereF[COLOR=black]ieldName3[/COLOR]").Value
  ' etc
  End With
rs.MoveNext
Next i

 

[Skip Bisconer]

Thanks for responding.

When I ran this code all I received was the first line of data from the query. I also need to run all the queries (11 of them) at one time. Can this be combined, or do I need to build this for each query?

Private Sub Command0_Click()
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set xlWB = objXL.Workbooks.Open("C:\Amex\Bisconer.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges11152")
 
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11152")
rs.MoveFirst
For i = 1 To rs.RecordCount
  With xlWS
  ' assign records to specific cells
  .Range("A" & i).Value = rs.Fields("LastName").Value
  .Range("B" & i).Value = rs.Fields("FirstName").Value
  .Range("C" & i).Value = rs.Fields("CardNumber").Value
  .Range("D" & i).Value = rs.Fields("Date").Value
  .Range("E" & i).Value = rs.Fields("Commodity").Value
  .Range("F" & i).Value = rs.Fields("BusinessType").Value
  .Range("G" & i).Value = rs.Fields("SupplierName").Value
  .Range("H" & i).Value = rs.Fields("Amount").Value
  .Range("I" & i).Value = rs.Fields("BusinessPurpose").Value
  .Range("J" & i).Value = rs.Fields("Customer").Value
  ' etc
  End With
rs.MoveNext
Next i
End Sub

 

[ajetrumpet]

i think that this stuff:

.Range("J" & i)

should be this:

.Range("J" & cstr(i))

i also think you should have a .EOF loop instead of the type of loop you have now. I am tired right now and can't think, but it comes to mind...

 

[nIGHTmAYOR]

ok then try changing loop methode, example :

Private Sub Command0_Click()
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set xlWB = objXL.Workbooks.Open("C:\Amex\Bisconer.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges11152")
 
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges11152", , dbOpenDynamic) '  , dbOpenDynamic is a meaningless optional addition but i am trying to be carefull here
rs.MoveFirst
i = 1
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i).Value = rs.Fields("LastName").Value
  .Range("B" & i).Value = rs.Fields("FirstName").Value
  .Range("C" & i).Value = rs.Fields("CardNumber").Value
  .Range("D" & i).Value = rs.Fields("Date").Value
  .Range("E" & i).Value = rs.Fields("Commodity").Value
  .Range("F" & i).Value = rs.Fields("BusinessType").Value
  .Range("G" & i).Value = rs.Fields("SupplierName").Value
  .Range("H" & i).Value = rs.Fields("Amount").Value
  .Range("I" & i).Value = rs.Fields("BusinessPurpose").Value
  .Range("J" & i).Value = rs.Fields("Customer").Value
  ' etc
  End With
i = i + 1
rs.MoveNext
Loop
End Sub

as for multiple queries hmmm you can always create an array to store all your queries and then loop above script through-out the array.

 

[nIGHTmAYOR]

hi ajetrumpet
cstr wouldnt change much , concatinating strings to integers is always smooth when you start by a string , so no need to add extra bytes to the code

as for the loop interesting isnt it how a a perfectly scripted loop wouldnt run i tried several other loops until this new one worked , if anybody has a comment as to why previous one wouldnt feel free to post.

 

[ajetrumpet]

sorry about that. just throwing my 2 cents in. It aint worth much. the dollar aint high right now

 

[Skip Bisconer]

Thanks NightMayer this worked.