Log In
Register
Home
December 4, 2021
Offline
I have a VBA script that partially works. My primary issue is with the queries. I want the queries to be disabled (RefreshAll). But for some reason 2 of the queries are duplicated, then show as connection only. The second screenshot shows the Refreshall that should be unchecked in tghe properties for each query. I got it this far, I just cannot figure out this issue.
Sub SaveAsXLSX()
Dim ws As Worksheet
Dim path As String
Dim fileName As String
Dim dateStr As String
Dim newFileName As String
Dim originalWorkbook As Workbook
Dim copyWorkbook As Workbook
' Set references to the relevant worksheets
Set ws = ThisWorkbook.Sheets("Notes")
' Read values from cells M1, M2, and M3
path = ws.Range("M1").Value
fileName = ws.Range("M2").Value
dateStr = Format(ws.Range("M3").Value, "MM-DD-YYYY")
' Construct the new file name
newFileName = path & "\" & fileName & " - " & dateStr & ".xlsx"
' Disable calculations and suppress alerts
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
' Save a copy of the workbook as XLSX
On Error Resume Next
Set originalWorkbook = ThisWorkbook
Set copyWorkbook = Workbooks.Add
originalWorkbook.Sheets.Copy Before:=copyWorkbook.Sheets(1)
copyWorkbook.SaveAs newFileName, FileFormat:=xlOpenXMLWorkbook
On Error GoTo 0
' Remove the blank "Sheet1"
On Error Resume Next
copyWorkbook.Sheets("Sheet1").Delete
On Error GoTo 0
' Hide specified sheets
copyWorkbook.Sheets("Control").Visible = xlVeryHidden
copyWorkbook.Sheets("Job Costing").Visible = xlVeryHidden
copyWorkbook.Sheets("Employee Time Reports").Visible = xlVeryHidden
copyWorkbook.Sheets("Opp & Estimate").Visible = xlVeryHidden
copyWorkbook.Sheets("Notes").Visible = xlVeryHidden
' Disable query refresh on Refresh All
For Each query In copyWorkbook.Connections
query.RefreshWithRefreshAll = False
Next query
' Re-enable calculations and alerts
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub
Trusted Members
October 17, 2018
Offline
I use these code snippets I found a long time ago to remove all the connections that are created, maybe they can help you
Public Sub RemoveConnections()
Dim Cn As Variant
With Application
.EnableEvents = False
.ScreenUpdating = False
.DisplayAlerts = False
end WithFor Each Cn In ThisWorkbook.Connections
.Delete
Next Cn
For Each Cn In ActiveSheet.QueryTables
Cn.Delete
Next CnWith Application
.EnableEvents = True
.ScreenUpdating = True
.DisplayAlerts = True
End With
End SubPublic Sub deleteActiveWorkbookConnections()
Dim conn As WorkbookConnection
For Each conn In ActiveWorkbook.Connections
conn.Delete
Next conn
End Sub
December 4, 2021
Offline
It kicked out with this error, and I circled the portion of the code that was highlighted since there was no traditional debugging.
December 4, 2021
Offline
@Hans Hallebeek,
I just got this to work making some modifications on my own. I thought I would share my final code with you and anyone else on the forum that may benefit.
Sub SaveAsXLSX()
Dim ws As Worksheet
Dim path As String
Dim fileName As String
Dim dateStr As String
Dim newFileName As String
Dim originalWorkbook As Workbook
Dim copyWorkbook As Workbook
' Set references to the relevant worksheets
Set ws = ThisWorkbook.Sheets("Notes")
' Read values from cells M1, M2, and M3
path = ws.Range("M1").Value
fileName = ws.Range("M2").Value
dateStr = Format(ws.Range("M3").Value, "MM-DD-YYYY")
' Construct the new file name
newFileName = path & "\" & fileName & " - " & dateStr & ".xlsx"
' Disable calculations and suppress alerts
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
' Save the workbook as XLSX without pop-up
ThisWorkbook.SaveAs newFileName, FileFormat:=xlOpenXMLWorkbook, ConflictResolution:=Excel.XlSaveConflictResolution.xlLocalSessionChanges
' Hide specified sheets
ThisWorkbook.Sheets("Control").Visible = xlVeryHidden
ThisWorkbook.Sheets("Job Costing").Visible = xlVeryHidden
ThisWorkbook.Sheets("Employee Time Reports").Visible = xlVeryHidden
ThisWorkbook.Sheets("Opp & Estimate").Visible = xlVeryHidden
ThisWorkbook.Sheets("Notes").Visible = xlVeryHidden
' Delete all Queries in an Excel Workbook
Dim Qus As WorkbookQuery
For Each Qus In ActiveWorkbook.Queries
Qus.Delete
Next Qus
' Re-enable calculations and alerts
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
Trusted Members
October 17, 2018
Offline
Great to read.
Happy it helped one way or another.
May I ofer you a tip, I have similar files wher I hide and unhide multiple worksheets and the thing is if you add another worksheet that needs to be included, you have to edit the entire VBA code.
I always use a worksheet named Admin or whatever name you want to give it and place the list of worksheets in a named range or a table (preferably a table) and in the column next to it the initial worksheet state and the final worksheet state.
You all you need to do is loop thtpugh each range of the column with the worksheet names and set the visible state to the column next to it
Hope I have explainde the idea in an understandable way.
Happy coding
"IT" Always crosses your path ...
