0

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.

8
  • Have you tried assigning the worksheet to an object and then acting upon that object directly instead of using the active sheet? This would allow you to break the steps up, which might make a difference, especially the refresh. Mar 13, 2018 at 18:31
  • @BillHileman Yes I have tried that. You mean simply declaring the worksheet as a variable first and then using that right?
    – Eric F
    Mar 13, 2018 at 18:35
  • Yes, exactly. What is the error that it is reporting? Mar 13, 2018 at 18:39
  • It is unclear (to me at least) if the newly added worksheet is also the currently active worksheet.
    – Yorik
    Mar 13, 2018 at 19:48
  • @BillHileman Stating that the original data location of the access database can't be accessed.
    – Eric F
    Mar 13, 2018 at 20:39

1 Answer 1

0

The best solution I could find was to do the following:

  1. Create a pivot table from VBA in the original workbook on a new worksheet
  2. Make the pivot table look exactly how I want it still within the original workbook
  3. "Flatten out" the pivot table by copying the columns and rows it occupies to a new tab. This eliminates any unnecessary data to not waste memory. If I didn't do this step, my computer would actually run out of memory for copying the entire data set.
  4. Copy the flattened out worksheet to the new workbook.

A little bit of a work around but this worked for me so I hope it does for others too.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .