Hi all,
First of all I will try to explain what I want to achieve.
In my Access database I will export some queries to serveral worksheets within the same workbook.
However I want the different worksheets immediately in a table format.
This is the solution I have at the moment:
With regard to the export I use:
Which is working fine.
After the export of the different sheets I will run another module where the Excel file will be opened and the tables will be defined and formatted.
However something is wrong with this code. It works, however the Excel process won't be closed. If I delete the 2 lines of code beneath "' Macro Code" everything is working fine.
Now, my questions:
1) is this the easiest and best way to do the formatting?
2) is there an easy way to define the ranges for the table formatting? Is it possible to search for active cells or something like that?
3) if the range needs to be defined, what's the easiest way to define the range based on the row & column count of the queries?
Thanks in advance
First of all I will try to explain what I want to achieve.
In my Access database I will export some queries to serveral worksheets within the same workbook.
However I want the different worksheets immediately in a table format.
This is the solution I have at the moment:
With regard to the export I use:
Code:
.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Queryname", strFullPath, False, "Queryname"
After the export of the different sheets I will run another module where the Excel file will be opened and the tables will be defined and formatted.
Code:
Option Compare Database
Option Explicit
Public Sub FormatXLS()
Dim appExcel As Excel.Application
Dim WBK As Excel.Workbook
Dim WKS As Excel.Worksheet
Dim strFile As String
strFile = "C:\Temp\Testtt\Book1.xlsx"
' Opens Excel and makes it Visible
Set appExcel = CreateObject("Excel.Application")
' Opens up a Workbook
Set WBK = appExcel.Workbooks.Open(strFile)
Set WKS = WBK.Worksheets(1)
appExcel.Visible = False
With WKS
' Macro Code
.ListObjects.Add(xlSrcRange, Range("$C$1:$C$5"), , xlYes).Name = "Table1"
.ListObjects("Table1").TableStyle = "TableStyleMedium2"
End With
' Close and Cleanup
Call WBK.Save
Call WBK.Close
appExcel.Visible = True
Call appExcel.Quit
Set WKS = Nothing
Set WBK = Nothing
Set appExcel = Nothing
End Sub
Now, my questions:
1) is this the easiest and best way to do the formatting?
2) is there an easy way to define the ranges for the table formatting? Is it possible to search for active cells or something like that?
3) if the range needs to be defined, what's the easiest way to define the range based on the row & column count of the queries?
Thanks in advance
Comment