Table

[D Willett]

Using VBA, I'm populating a worksheet from an Access Database, the record count could be many or less, that parts not important for now.

On the worksheet which receives the data, I have changed the range to a table manually through the insert manu, with the assumption that the table structure will grow or reduce dependant on the data it receives.
It doesn't, the table structure is not retained when I run my vba code and reverts to a standard worksheet.

(Hope I've explained this accurately )

The vba code to populate the sheet is below just in case it has any relevance:

Code: Select all

Sub RunCycleQuery()
 
    'Step 1: Declare your variables
    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset
    Dim i As Integer
    ProgressBar.Show
     
    'Step 2: Identify the database and query
    Set MyDatabase = DBEngine.OpenDatabase _
    (ActiveWorkbook.Path & "\Autoflow-Dash.mdb")
    Set MyQueryDef = MyDatabase.QueryDefs("qryK2K")
     
    'Step 4: Open the query
    Set MyRecordset = MyQueryDef.OpenRecordset
     
    'Step 5: Clear previous contents
    Range("Cycle!A1:CC10000").ClearContents
     
    'Step 6: Copy the recordset to Excel
    Range("Cycle!A2").CopyFromRecordset MyRecordset
     
    'Step 7: Add column heading names to the spreadsheet
    For i = 1 To MyRecordset.Fields.Count
    Sheets("Cycle").Cells(, i).Value = MyRecordset.Fields(i - 1).Name
    
    Next i
    
    Sheets("Dashboard").Select
     
 
End Sub

[HansV]

You could add the table again, below the line Next i:

Code: Select all

    Sheets("Cycle").ListObjects.Add SourceType:=xlSrcRange, _
        Source:=Sheets("Cycle").Range("A1").CurrentRegion, _
        XLListObjectHasHeaders:=xlYes

[Rudi]

In my attempt I remove the table and add it again...

Code: Select all

Sub RunCycleQuery()

    'Step 1: Declare your variables
    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset
    Dim i As Integer
    ProgressBar.Show

    'Step 2: Identify the database and query
    Set MyDatabase = DBEngine.OpenDatabase _
    (ActiveWorkbook.Path & "\Autoflow-Dash.mdb")
    Set MyQueryDef = MyDatabase.QueryDefs("qryK2K")

    'Step 4: Open the query
    Set MyRecordset = MyQueryDef.OpenRecordset

    'Step 5: Clear previous contents
    On Error Resume Next
    Sheets("Cycle").ListObjects(1).Unlist
    Sheets("Cycle").Range("A1").CurrentRegion.Clear

    'Step 6: Copy the recordset to Excel
    Range("Cycle!A2").CopyFromRecordset MyRecordset

    'Step 7: Add column heading names to the spreadsheet
    For i = 1 To MyRecordset.Fields.Count
        Sheets("Cycle").Cells(, i).Value = MyRecordset.Fields(i - 1).Name
    Next i

    Sheets("Cycle").ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes).Name = "Table1"
    Sheets("Dashboard").Select

End Sub

[D Willett]

Thanks guys.
Solved the problem, greatly appreciated.

Kind Regards