Hello,
I have an excel workbook with an excel Table with bunch of queries. I want to be able to have a user to create his own copy of the table only, i.e. without all the connections to the queries.
Here is the coordinate of the excel table: Worksheets("ESTIMATES_ROLLUP").Range("Table_ESTIMATES_ROLLUP[#All]")
Below is a code snippet i used from an old existing thread. Modified as much as i could to meet my requirements. Above is the excel table's details. A user needs to be able to create a copy of that table.
Here is what i want in a nutshell:
1. To be able to create a new workbook with a copy of the Excel Table "Table_ESTIMATES_ROLLUP" only (below is the location details) and without any connections to the queries:
Worksheets("ESTIMATES_ROLLUP").Range("Table_ESTIMATES_ROLLUP[#All]")
2. I want to have a button that activates the code
As you can see, the code exports a copy to the existing file path and defines the file name with a date stamp, I want to keep that too. When I activate this code, it gives me error saying there is a bug.
Thank you in advance.
***********************************Beginning of Code*******************************************************************************************************************************
***********************************End of Code*******************************************************************************************************************************
I have an excel workbook with an excel Table with bunch of queries. I want to be able to have a user to create his own copy of the table only, i.e. without all the connections to the queries.
Here is the coordinate of the excel table: Worksheets("ESTIMATES_ROLLUP").Range("Table_ESTIMATES_ROLLUP[#All]")
Below is a code snippet i used from an old existing thread. Modified as much as i could to meet my requirements. Above is the excel table's details. A user needs to be able to create a copy of that table.
Here is what i want in a nutshell:
1. To be able to create a new workbook with a copy of the Excel Table "Table_ESTIMATES_ROLLUP" only (below is the location details) and without any connections to the queries:
Worksheets("ESTIMATES_ROLLUP").Range("Table_ESTIMATES_ROLLUP[#All]")
2. I want to have a button that activates the code
As you can see, the code exports a copy to the existing file path and defines the file name with a date stamp, I want to keep that too. When I activate this code, it gives me error saying there is a bug.
Thank you in advance.
***********************************Beginning of Code*******************************************************************************************************************************
VBA Code:
Public Sub ExportNewUpdate()
Dim newWb As Workbook
Dim wbConn As WorkbookConnection
Dim wbQuery As WorkbookQuery
Dim links As Variant, i As Long
Dim xStrDate As String
Dim btn As Shape
Application.ScreenUpdating = False
xStrDate = Format(Now, "yyyymmdd_HH-mm")
ThisWorkbook.Worksheets(Array("ESTIMATES_ROLLUP")).Copy
Set newWb = ActiveWorkbook
'Delete workbook connections
For Each wbConn In newWb.Connections
wbConn.Delete
Next
'Delete workbook queries
For Each wbQuery In newWb.Queries
wbQuery.Delete
Next
'Delete workbook button
For Each btn In newWb.Shapes
btn.Delete
Next
links = newWb.LinkSources(xlExcelLinks)
If links <> Empty Then
For i = 1 To UBound(links)
newWb.BreakLink links(i), xlLinkTypeExcelLinks
Next
End If
Application.DisplayAlerts = False 'suppress warning message displayed if new workbook already exists
newWb.SaveAs ThisWorkbook.Path & "\PE Rollup Report " & " " & xStrDate & ".xlsx"
Application.DisplayAlerts = True
newWb.Close False
MsgBox ("Your new report has been exported to the same location with filename: " & "PE Rollup Report " & " " & xStrDate & ".xlsx")
Application.ScreenUpdating = True
End Sub
***********************************End of Code*******************************************************************************************************************************