Good Looking Bloke
Registered User.
- Local time
- , 07:45
- Joined
- Oct 15, 2019
- Messages
- 30
I am not bad as a hack in Access, however outside it I get very lost very quickly.
I have been using some code to export various data from Access to Excel. This is in my Access module:
I don't recall where I found this however it is not my own work. I have simply worked with it to export the items I need to excel and it works well.
However now I would like to format the output (filename) as a table within the excel file I am creating. Looking around online I came across this code snippet that I think does what I am wanting to do
However my various attempts to push this onto the Wbook have meet with dismal failure. My understanding of the code is it should select all the populated cells and convert them to a table. The various errors I have had range from file not open to file already open and other variations around this. I think this could is intended to run inside an excel spreadsheet, however as I am creating the spreadsheet from Access I am unlikely to be able to add this code to the spreadsheet.
Is this a valid approach?
Can it be done?
Am I simply missing the obvious?
Thanks for the help
I have been using some code to export various data from Access to Excel. This is in my Access module:
Code:
Dim wbook As Object
Dim createExcel As Object
Dim Wsheet As Object
Dim fieldIdx As Integer
Dim rowIdx As Integer
Dim rstCount As Long
Set createExcel = CreateObject("Excel.Application")
Set wbook = createExcel.Workbooks.Add
Set Wsheet = wbook.Worksheets.Add
' writing column headers
For fieldIdx = 0 To rst.Fields.Count - 1
Wsheet.Cells(1, fieldIdx + 1).value = rst.Fields(fieldIdx).Name
Next fieldIdx
'' looping through rows and writing in spreadsheet
'rstCount = rst.RecordCount
If (lngRstCount > 0) Then
rst.MoveFirst
For rowIdx = 0 To lngRstCount - 1
For fieldIdx = 0 To rst.Fields.Count - 1
Wsheet.Cells(rowIdx + 2, fieldIdx + 1).value = rst(fieldIdx).value
Next fieldIdx
rst.MoveNext
Next rowIdx
End If
wbook.SaveAs filename
wbook.Close True
Set wbook = Nothing
Set Wsheet = Nothing
Set wbook = createExcel.Workbooks.Open(filename)
createExcel.Visible = True
Set createExcel = Nothing
Exit Sub
I don't recall where I found this however it is not my own work. I have simply worked with it to export the items I need to excel and it works well.
However now I would like to format the output (filename) as a table within the excel file I am creating. Looking around online I came across this code snippet that I think does what I am wanting to do
Code:
Private Sub A_SelectAllMakeTable()
Dim tbl As ListObject
Dim rng As Range
Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
tbl.TableStyle = "TableStyleMedium15"
End Sub
However my various attempts to push this onto the Wbook have meet with dismal failure. My understanding of the code is it should select all the populated cells and convert them to a table. The various errors I have had range from file not open to file already open and other variations around this. I think this could is intended to run inside an excel spreadsheet, however as I am creating the spreadsheet from Access I am unlikely to be able to add this code to the spreadsheet.
Is this a valid approach?
Can it be done?
Am I simply missing the obvious?
Thanks for the help