Make Excel Data Format as a Table (1 Viewer)

Good Looking Bloke

Registered User.
Local time
Today, 10:22
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:
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
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:22
Joined
Oct 29, 2018
Messages
21,358
Yeah, "ActiveSheet" is for within Excel, unless, I think, you use the Select method first. Just a guess...
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:22
Joined
Sep 21, 2011
Messages
14,048
I would have thought you would need to declare the Excel object and workbook?, then use those to qualify the statement?

set objxl.objwb.ActiveSheet..... etc
 

Darrell

Registered User.
Local time
Today, 02:22
Joined
Feb 1, 2001
Messages
299
Maybe try this before you close the workbook

Code:
With wbook

Dim tbl As ListObject
Dim rng As Range

Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
Set tbl = Wsheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
tbl.TableStyle = "TableStyleMedium15"

End with
 

Gasman

Enthusiastic Amateur
Local time
Today, 02:22
Joined
Sep 21, 2011
Messages
14,048
Would passing the workbook in and set on that work?
Honestly?, I do not know, but it is something I would try.

If you are going to go with With wBook, you would need the properties to start with a full stop .

Personally I would record the macro for the exact steps in Excel then modify to suit.

HTH
 

ebs17

Well-known member
Local time
Today, 03:22
Joined
Feb 7, 2020
Messages
1,882
Code:
...
    wbook.SaveAs filename
    wbook.Close True
...
    Set wbook = createExcel.Workbooks.Open(filename)
...
Why close to open?

Code:
 ' copy the whole recordset
Wsheet.Cells(2, 1).CopyFromRecordset rst

' call to the procedure
A_SelectAllMakeTable Wsheet
...

Your question: The first code uses late binding, the second uses early binding. You should commit to one.
The call to the procedure is already inserted in the previous snippet.
Code:
Private Sub A_SelectAllMakeTable(MyWorksheet As Object)
    Dim tbl As Object
    Dim rng As Object
    Const xlYes = 1
    Const xlLastCell = 11
    Const xlSrcRange = 1

    With MyWorksheet
         Set rng = .Range(.Range("A1"), .Range("A1").SpecialCells(xlLastCell))
         Set tbl = .ListObjects.Add(xlSrcRange, rng, , xlYes)
         tbl.TableStyle = "TableStyleMedium15"
    End With
End Sub

It should go something like this.

Eberhard
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 02:22
Joined
Jul 9, 2003
Messages
16,245
However now I would like to format the output

You might find this code example useful. Originally from Bob Larson, a regular contributor on Access World Forums (AWF). I have an example which demonstrates how it works somewhere, if you want it let me know...

 

Users who are viewing this thread

Top Bottom