asked on

Export 5 Access Tables to 1 Excel File using VBA

Hello,

I am using code that I found on EE and it is working so far. However it works for 1 export to 1 file ratio. I need to export 5 tables from one Access file to one Excel file. Each table export would have its own tab in the Excel File.

Possible?

Sub Export_Data()
Dim rst As DAO.Recordset
Dim xlApp As Object
Dim xlWB As Object
Dim xlWs As Object
Dim xlRng As Object
Dim strTblName As String
Dim strXLFileName As String
Dim I As Long
     
    strTblName = "YAV_FORECAST"
 
    strXLFileName = "C:\VSS Excel Export\" & Month(Format(Now, "mm")) & " VSS Results"
 
    Set rst = CurrentDb.OpenRecordset(strTblName)
 
    Set xlApp = CreateObject("Excel.Application")
 
    Set xlWB = xlApp.Workbooks.Add(xlWBATWorksheet)
 
    Set xlWs = xlWB.Worksheets(1)
 
    Set xlRng = xlWs.Range("A1")
     
    For I = 0 To rst.Fields.Count - 1
        xlRng.Offset(, I).Value = rst.Fields(I).Name
    Next I
     
    xlRng.Offset(1).CopyFromRecordset rst
 
    rst.Close
 
    Set rst = Nothing
 
    Set xlRng = xlWs.UsedRange
 
    With xlRng
 
        .Font.Name = "Verdana"
        .Font.Size = 8
 
        With .Borders
            .ColorIndex = xlAutomatic
            .LineStyle = xlContinuous
            .Weight = xlThin
        End With
 
        With .Rows(1)
            .Font.Bold = True
            .Interior.ColorIndex = 15
        End With
 
        .WrapText = False
 
        .EntireColumn.AutoFit
 
    End With
 
    xlWB.SaveAs strXLFileName, True
 
    xlApp.Visible = True
 
    Set xlApp = Nothing
 
End Sub

Open in new window

pwdells

ASKER

Side Note:

I don't mind setting up a loop to run this code for each table that I need to export. I just don't know where to find the syntax to export into an existing file and to make an additional tab for exporting/importing.

sanofi-aventis

This might work. A simple Next loop with a CHOOSE function. Also add these references to your project would help you a lot. I changed all the "as Object" references. You lose all the intelisense if you use "as Object"

Sub Export_Data()

Dim rst As ADODB.Recordset
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWs As Excel.Worksheet
Dim xlRng As Excel.Range

Dim strTblName As String
Dim strXLFileName As String
Dim intX As Integer

strTblName = "YAV_FORECAST"

strXLFileName = "C:\VSS Excel Export\" & Month(Format(Now, "mm")) & " VSS Results"

For intX = 1 To 5

Set rst = CurrentProject.OpenRecordset(Choose(intX, "Table1", "Table2", "Table3", "Table4", "Table5"))

Set xlApp = New Excel.Application

If xlApp.Workbooks.Count < intX Then
Set xlWB = xlApp.Workbooks.Add(xlWBATWorksheet)
Else
Set xlWB = xlApp.Workbooks(intX)
End If

Set xlRng = xlWs.Range("A1")

For I = 0 To rst.Fields.Count - 1
xlRng.Offset(, I).Value = rst.Fields(I).Name
Next I

xlRng.Offset(1).CopyFromRecordset rst

rst.Close

Set rst = Nothing

Set xlRng = xlWs.UsedRange

With xlRng

.Font.Name = "Verdana"
.Font.Size = 8

With .Borders
.ColorIndex = xlAutomatic
.LineStyle = xlContinuous
.Weight = xlThin
End With

With .Rows(1)
.Font.Bold = True
.Interior.ColorIndex = 15
End With

.WrapText = False

.EntireColumn.AutoFit

End With

Next intX

xlWB.SaveAs strXLFileName, True

xlApp.Visible = True

Set xlApp = Nothing

End Sub

T-Bone

Experts-Exchange.bmp

pwdells

ASKER

Hi T-Bone,

I filled in the Choose() Statement as follows:

 Set rst = CurrentProject.OpenRecordset(Choose(intX, "S786", "YAV_FORECAST", "YSOC_CONSOLIDATE"))

Open in new window

I get an error:

Object doesn't support this property or method (Error 438). The explanation is quite vague. Have you seen this happen before with the Choose()?

Wendee

pwdells

ASKER

P.S. That Choose() fx is pretty nifty...if I can get it working! :0)

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

pwdells

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pwdells

ASKER

I wanted to keep this dynamic, because the names of the tables to be exported are coming from a table. So this had to be in a loop to be able to work dynamically from a recordset.

But Cap had a real cut and dry answer.