Hi All
I know this is a topic that is regularly touched on and the current solution I have has been put together from snippets on this site but it doesn’t quite tick all my boxes.
It relates to exporting table records from 2 separate tables into an Excel workbook, into predefined worksheets within said workbook. These worksheets are linked within the workbook to populate a template in the desired final presentation format.
The problem I have is I want to export all tables at the same time, as in the same macro (button click event on form). I tried the “docmd” method with one line of code for each table but when it ran it wouldn’t copy the data into the existing workbook tabs, but create new ones ie: destination tab “ExportResultGL” would not get populated but a new tab would be created “ExportResultGL1”.
The current solution I have gone for is two separate modules, one for each export table, the problem is that I currently have 2x form buttons one for each module and I execute it exports fine but opens excel each module, thus I end up with 2x instances of Excel open when what I want is the process to be a “one hit wonder”. I have tried placing both modules in a single macro but it still opens Excel twice, once for the first export table and again for the second.
My question is, based on my code (below, 2x standalone exports) is there any way to have the code below as a single module but process/exporting a number of different tables (I suppose in the same way as multiply lines of the “docmd transfer” statement method). Or failing that anyway of exporting the first table without opening Excel and Excel only opening on the final, second table export. – hope it makes a little sense on what I am trying to achieve
First Mod:
Option Compare Database
Function ExportGLResults2()
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Dim strPath As String
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
'Location of Spreadsheet to export table to
strPath = "C:\Users\alex.withers\Desktop\InvestDBs\Export_Results.xlsx"
'Name of Access table or query which you want to export
Set rst = CurrentDb.OpenRecordset("tblGL_AccountRESULTS")
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True
'Name of worksheet within Excel workbook which you are exporting to
Set xlWSh = xlWBk.Worksheets("ExportedGL")
xlWSh.Activate
'if below commented out headers will remain
xlWSh.Range("A1").Select
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select
rst.Close
' This is included to show some of what you can do about formatting. You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Calibri"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select
End Function
******Second Module******
Option Compare Database
Function ExportSLResults2()
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Dim strPath As String
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
'Location of Spreadsheet to export table to
strPath = "C:\Users\alex.withers\Desktop\InvestDBs\Export_Results.xlsx"
'Name of Access table or query which you want to export
Set rst = CurrentDb.OpenRecordset("tblSL_MONTH_RESULTS")
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True
'Name of worksheet within Excel workbook which you are exporting to
Set xlWSh = xlWBk.Worksheets("ExportedSL")
xlWSh.Activate
'if below commented out headers will remain
xlWSh.Range("A1").Select
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select
rst.Close
' This is included to show some of what you can do about formatting. You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Calibri"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select
End Function
Any ideas???
I almost need to do:
'Name of Access table or query which you want to export
Set rst = CurrentDb.OpenRecordset("tblGL_AccountRESULTS")
Set rst = CurrentDb.OpenRecordset("tblSL_AccountRESULTS")
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True
'Name of worksheet within Excel workbook which you are exporting to
Set xlWSh = xlWBk.Worksheets("ExportedGL")
Set xlWSh = xlWBk.Worksheets("ExportedSL")
I know this wouldn't function but just to try to explain a little bit better, as in single script to execute multiple exports
thanks
I know this is a topic that is regularly touched on and the current solution I have has been put together from snippets on this site but it doesn’t quite tick all my boxes.
It relates to exporting table records from 2 separate tables into an Excel workbook, into predefined worksheets within said workbook. These worksheets are linked within the workbook to populate a template in the desired final presentation format.
The problem I have is I want to export all tables at the same time, as in the same macro (button click event on form). I tried the “docmd” method with one line of code for each table but when it ran it wouldn’t copy the data into the existing workbook tabs, but create new ones ie: destination tab “ExportResultGL” would not get populated but a new tab would be created “ExportResultGL1”.
The current solution I have gone for is two separate modules, one for each export table, the problem is that I currently have 2x form buttons one for each module and I execute it exports fine but opens excel each module, thus I end up with 2x instances of Excel open when what I want is the process to be a “one hit wonder”. I have tried placing both modules in a single macro but it still opens Excel twice, once for the first export table and again for the second.
My question is, based on my code (below, 2x standalone exports) is there any way to have the code below as a single module but process/exporting a number of different tables (I suppose in the same way as multiply lines of the “docmd transfer” statement method). Or failing that anyway of exporting the first table without opening Excel and Excel only opening on the final, second table export. – hope it makes a little sense on what I am trying to achieve
First Mod:
Option Compare Database
Function ExportGLResults2()
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Dim strPath As String
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
'Location of Spreadsheet to export table to
strPath = "C:\Users\alex.withers\Desktop\InvestDBs\Export_Results.xlsx"
'Name of Access table or query which you want to export
Set rst = CurrentDb.OpenRecordset("tblGL_AccountRESULTS")
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True
'Name of worksheet within Excel workbook which you are exporting to
Set xlWSh = xlWBk.Worksheets("ExportedGL")
xlWSh.Activate
'if below commented out headers will remain
xlWSh.Range("A1").Select
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select
rst.Close
' This is included to show some of what you can do about formatting. You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Calibri"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select
End Function
******Second Module******
Option Compare Database
Function ExportSLResults2()
Dim rst As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object
Dim fld As DAO.Field
Dim strPath As String
Const xlCenter As Long = -4108
Const xlBottom As Long = -4107
'Location of Spreadsheet to export table to
strPath = "C:\Users\alex.withers\Desktop\InvestDBs\Export_Results.xlsx"
'Name of Access table or query which you want to export
Set rst = CurrentDb.OpenRecordset("tblSL_MONTH_RESULTS")
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True
'Name of worksheet within Excel workbook which you are exporting to
Set xlWSh = xlWBk.Worksheets("ExportedSL")
xlWSh.Activate
'if below commented out headers will remain
xlWSh.Range("A1").Select
For Each fld In rst.Fields
ApXL.ActiveCell = fld.Name
ApXL.ActiveCell.Offset(0, 1).Select
Next
rst.MoveFirst
xlWSh.Range("A2").CopyFromRecordset rst
xlWSh.Range("1:1").Select
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select
rst.Close
' This is included to show some of what you can do about formatting. You can comment out or delete
' any of this that you don't want to use in your own export.
With ApXL.Selection.Font
.Name = "Calibri"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
ApXL.Selection.Font.Bold = True
With ApXL.Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
' selects all of the cells
ApXL.ActiveSheet.Cells.Select
' does the "autofit" for all columns
ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
' selects the first cell to unselect all cells
xlWSh.Range("A1").Select
End Function
Any ideas???
I almost need to do:
'Name of Access table or query which you want to export
Set rst = CurrentDb.OpenRecordset("tblGL_AccountRESULTS")
Set rst = CurrentDb.OpenRecordset("tblSL_AccountRESULTS")
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
ApXL.Visible = True
'Name of worksheet within Excel workbook which you are exporting to
Set xlWSh = xlWBk.Worksheets("ExportedGL")
Set xlWSh = xlWBk.Worksheets("ExportedSL")
I know this wouldn't function but just to try to explain a little bit better, as in single script to execute multiple exports
thanks
Last edited:
