export file to excel in microsoft access

[Export data from access qry to excel]

would like to export data from an access query to excel using a do while
technique

how do i do that? should i set up do while, then movenext (for the record)

i currently am using the transferspreadsheet technique
thanks for your help.


here is my code:



Dim xlfiledest As String: xlfiledest = CurrentProject.Path & "\aa-inventory\"
'Dim xlfiledest As String: xlfiledest = CurrentProject.Path

Dim xlfilenamea As String, xlfilenameb As String, xlfilenamec As String
Dim xlfileparta As String, xlfilepartb As String, xlfilepartc As String
MsgBox xlfiledest
Dim xlapp, xlsheet, xbook As Object

xlfileparta = Format(Date, "yyyy-mm-dd") & " - daily detail.xls"
xlfilepartb = Format(Date, "yyyy-mm-dd") & " - totalcount.xls"
xlfilepartc = Format(Date, "yyyy-mm-dd") & " - totalFTE.xls"
xlfilenamea = xlfiledest & xlfileparta
xlfilenameb = xlfiledest & xlfilepartb
xlfilenamec = xlfiledest & xlfilepartc


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qry_0bprodcodesort_total", xlfilenamea, True, ""

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qry_2aAgeCountCTB_total", xlfilenameb, True, ""
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qry_2bageFTECTB_total", xlfilenamec, True, ""

Set xlapp = CreateObject("excel.application")
Set xlsheet = xlapp.Workbooks.Open(xlfilenamea).Sheets(1)

xlsheet.Range("a1:AA1").WrapText = True
xlsheet.Range("a1:aa65000").Font.Name = "ARIAL"
xlsheet.Range("a1:aa65000").Font.Size = 9
xlsheet.Range("a:b").ColumnWidth = 16
xlsheet.Range("c:d").ColumnWidth = 9
xlsheet.Range("e:f").ColumnWidth = 20
xlsheet.Range("g:i").ColumnWidth = 16
xlsheet.Range("j:j").ColumnWidth = 14
xlsheet.Range("k:k").ColumnWidth = 18
xlsheet.Range("l:n").ColumnWidth = 10
xlsheet.Range("o").ColumnWidth = 22
xlsheet.Range("p").ColumnWidth = 9
xlsheet.Range("q:q").ColumnWidth = 17
xlsheet.Range("r:r").ColumnWidth = 20
xlsheet.Range("s:t").Delete
xlsheet.Name = "daily work"
xlapp.ActiveWorkbook.Save
xlapp.ActiveWorkbook.Close xlfilenamea
xlapp.Quit



Set xlapp = CreateObject("excel.application")
Set xlsheet = xlapp.Workbooks.Open(xlfilenameb).Sheets(1)
xlsheet.Range("a1:AA1").WrapText = True
xlsheet.Range("a1:aa500").Font.Name = "ARIAL"
xlsheet.Range("a1:aa500").Font.Size = 9
xlsheet.Range("a:j").ColumnWidth = 10
xlsheet.Range("B:j").NumberFormat = "##,##0"
xlsheet.Name = "total count"
xlapp.ActiveWorkbook.Save
xlapp.ActiveWorkbook.Close xlfilenameb
xlapp.Quit

Set xlapp = CreateObject("excel.application")
Set xlsheet = xlapp.Workbooks.Open(xlfilenamec).Sheets(1)
xlsheet.Range("a1:AA1").WrapText = True
xlsheet.Range("a1:aa500").Font.Name = "ARIAL"
xlsheet.Range("a1:aa500").Font.Size = 9
xlsheet.Range("a:j").ColumnWidth = 10
xlsheet.Range("B:j").NumberFormat = "##,##0.00"
xlsheet.Name = "total fte"
xlapp.ActiveWorkbook.Save
xlapp.ActiveWorkbook.Close xlfilenamec

xlapp.Quit


MsgBox "processing is complete - check the folder for output"

End Sub

 

[Gina Whipp]

Not much to go on, so try here...

http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Export data from access qry to excel" <Export data from access qry to excel
@discussions.microsoft.com> wrote in message
would like to export data from an access query to excel using a do while
technique

how do i do that? should i set up do while, then movenext (for the record)

i currently am using the transferspreadsheet technique
thanks for your help.


here is my code:



Dim xlfiledest As String: xlfiledest = CurrentProject.Path &
"\aa-inventory\"
'Dim xlfiledest As String: xlfiledest = CurrentProject.Path

Dim xlfilenamea As String, xlfilenameb As String, xlfilenamec As String
Dim xlfileparta As String, xlfilepartb As String, xlfilepartc As String
MsgBox xlfiledest
Dim xlapp, xlsheet, xbook As Object

xlfileparta = Format(Date, "yyyy-mm-dd") & " - daily detail.xls"
xlfilepartb = Format(Date, "yyyy-mm-dd") & " - totalcount.xls"
xlfilepartc = Format(Date, "yyyy-mm-dd") & " - totalFTE.xls"
xlfilenamea = xlfiledest & xlfileparta
xlfilenameb = xlfiledest & xlfilepartb
xlfilenamec = xlfiledest & xlfilepartc


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qry_0bprodcodesort_total", xlfilenamea, True, ""

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qry_2aAgeCountCTB_total", xlfilenameb, True, ""
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qry_2bageFTECTB_total", xlfilenamec, True, ""

Set xlapp = CreateObject("excel.application")
Set xlsheet = xlapp.Workbooks.Open(xlfilenamea).Sheets(1)

xlsheet.Range("a1:AA1").WrapText = True
xlsheet.Range("a1:aa65000").Font.Name = "ARIAL"
xlsheet.Range("a1:aa65000").Font.Size = 9
xlsheet.Range("a:b").ColumnWidth = 16
xlsheet.Range("c:d").ColumnWidth = 9
xlsheet.Range("e:f").ColumnWidth = 20
xlsheet.Range("g:i").ColumnWidth = 16
xlsheet.Range("j:j").ColumnWidth = 14
xlsheet.Range("k:k").ColumnWidth = 18
xlsheet.Range("l:n").ColumnWidth = 10
xlsheet.Range("o").ColumnWidth = 22
xlsheet.Range("p").ColumnWidth = 9
xlsheet.Range("q:q").ColumnWidth = 17
xlsheet.Range("r:r").ColumnWidth = 20
xlsheet.Range("s:t").Delete
xlsheet.Name = "daily work"
xlapp.ActiveWorkbook.Save
xlapp.ActiveWorkbook.Close xlfilenamea
xlapp.Quit



Set xlapp = CreateObject("excel.application")
Set xlsheet = xlapp.Workbooks.Open(xlfilenameb).Sheets(1)
xlsheet.Range("a1:AA1").WrapText = True
xlsheet.Range("a1:aa500").Font.Name = "ARIAL"
xlsheet.Range("a1:aa500").Font.Size = 9
xlsheet.Range("a:j").ColumnWidth = 10
xlsheet.Range("B:j").NumberFormat = "##,##0"
xlsheet.Name = "total count"
xlapp.ActiveWorkbook.Save
xlapp.ActiveWorkbook.Close xlfilenameb
xlapp.Quit

Set xlapp = CreateObject("excel.application")
Set xlsheet = xlapp.Workbooks.Open(xlfilenamec).Sheets(1)
xlsheet.Range("a1:AA1").WrapText = True
xlsheet.Range("a1:aa500").Font.Name = "ARIAL"
xlsheet.Range("a1:aa500").Font.Size = 9
xlsheet.Range("a:j").ColumnWidth = 10
xlsheet.Range("B:j").NumberFormat = "##,##0.00"
xlsheet.Name = "total fte"
xlapp.ActiveWorkbook.Save
xlapp.ActiveWorkbook.Close xlfilenamec

xlapp.Quit


MsgBox "processing is complete - check the folder for output"

End Sub