So I have a workbook that someone else created to which there are already connections created. One of the connections retrieves data from an access database and then outputs it to a pivottable.
If I goto Data > Existing Connections > Select my connection (the access one) > click open > Table > New worksheet, it will output the entire table which is exactly what I want; however, I want to do this with VBA automated. If I use the macro recorder it ends up with:
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
Connections("Access G183"), Destination:=Range("$A$1")).TableObject
.RowNumbers = False
.PreserveFormatting = True
.RefreshStyle = 1
.AdjustColumnWidth = True
.ListObject.DisplayName = "Table_Data"
.Refresh
End With
End Sub
Whenever I run the macro again, it errors at either the .ListObject.DisplayName or .Refresh lines. If I comment these out, then it simply loads no data. Has anyone found a way to automate this before? The tricky part is that the workbook no longer has the original database.