Export from Access to Excel using vb

[gj0519]

I am new to using vb and need some help.
I need to export data out of Access into Excel. I need the data to go into different worksheets within the workbook.
Not sure where to start.

Thanks

 

[redapples]

strFolderName = ("D:\access\RSI Most Recent\stats requests\LACharts\ ")
      strBookName = (strFolderName & strName & ".xls")
        For iSheet = 1 To 6
            strQueryName = Choose(iSheet, "RefToSocWork", "SocWork@ref", "MovedintoLAfrom", _
            "MovedintoLAHomeRoof", "MovedintoLASubstance", "MovedYes-No")
            DoCmd.TransferSpreadsheet acExport, 8, strQueryName, strBookName, True
        Next iSheet

In the above example the for next loop exports queries into a excel sheet each query is a different sheet in the same work book. hope this helps.
steve

 

[steph22]

Hi gj0519,

What version of Access are you using? Will you be using an already-saved template or creating a new Excel workbook each time? I use Access 97 and have several modules that I use on a regular basis - all of which will export information to separate worksheets in the same workbook. If you can provide a little more detail (the more the better), I might be able to provide some examples.

 

[gj0519]

I am using Office 2000.
The spreadsheet will be a saved template, it will link to other workbooks.
Thanks,
gj0519

 

[steph22]

gj0519,

Here's one module I use to open a workbook template and then export the results from 3 tables/queries to 3 separate worksheets in the same workbook.
I am using Office 97 so some of the actual VBA syntax may be slightly different for you, but hopefully this will give you a start as far as the format. If you get any error messages or have problems, post them and maybe someone will have a solution.
-Steph22

Function ExportToExcel()

Dim objXL as Excel.Application
Dim objWKB As Excel.Workbook
Dim objSHT As Excel.Worksheet
Dim db As Database
Dim rs as Recordset
Dim strSQL as String
Dim strSQL2 As String
Dim strSQL3 As String
Const conSHEET1= "Sheet1" 'name of your first worksheet
Const conSHEET2= "Sheet2" 'name of second worksheet
Const conSHEET3= "Sheet3" 'name of third worksheet
Const ConWKBK="C:\Data\Excel\Templates\Template_1.xlt" 'full
path to your template file

Set db=CurrentDb
Set objXL = New Excel.Application
With objXL
.Visible = True
Set objWKB = .Workbooks.Open (conWKBK)

'Populate first worksheet

StrSQL = "SELECT [field1], [field2], [field3],
[field4], [field5] FROM [first table or query
name]"
Set rs = db.OpenRecordset(strSQL,dbOpenSnapshot)
Set objSHT = objWKB.Worksheets(conSHEET1)
With objSHTt
.Range ("A2&quot.CopyFromRecordset rs
End With

'Populate second worksheet

StrSQL2 = "SELECT [field1], [field2], [field3],
[field4], [field5] FROM [second table or
query name]"
Set rs = db.OpenRecordset(strSQL2,dbOpenSnapshot)
Set objSHT = objWKB.Worksheets(conSHEET2)
objSHT.Activate
With objSHT
.Range ("A2&quot.CopyFromRecordset rs
End with

'Populate third worksheet

StrSQL3 = "SELECT [field1], [field2], [field3],
[field4], [field5] FROM [third table or query
name]"
Set rs = db.OpenRecordset(strSQL3,dbOpenSnapshot)
Set objSHT = objWKB.Worksheets(conSHEET3)
objSHT.Activate
With objSHT
.Range ("A2&quot.CopyFromRecordset rs
End with

'set focus back to SHEET1 before saving

Set objSHT = objWKB.Worksheets(conSHEET1)
objSHT.Activate

' save the workbook

objWKB.SaveAs "C:\Data\Excel\Files\workbook_1" 'full
path to saved location

End With

Set objSHT = nothing
Set objWKB = nothing
Set objXL = nothing
Set rs = nothing
Set db = nothing

End Function

 

[gj0519]

Thanks steph22,
I will give this a try.
gj0519