CODEWITHSUNDEEP
excelvbasave-as
Christian
Christian

Reputation: 33

Save to User's download file and update file without popup window asking to over-ride

Right now, the VBA Macro works great on 'my' PC be/c I have it saving as a specific file to my download folder. I need this to work on anyone else's PC that runs this Macro and have the same function. I tried to google, and I am not finding exactly what I need.

Below is the code I am using:

Sub UnLoad_UnPick()
'
' UnLoad_UnPick Macro
'

'
Range("Q:Q").Style = "CURRENCY"


Columns("O:O").Select
    Selection.Cut

Columns("A:A").Select
    Selection.Insert Shift:=xlToRight

Range("A1").Select

Application.DisplayAlerts = False
     ChDir "C:\\users\"1st name"."last name"\downloads"
     ActiveWorkbook.SaveAs Filename:= _
          "C:\users\"1st name"."last name"\downloads\Transaction_Details.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
'
End Sub

Upvotes: 1

Views: 44

Answers (1)

Michal
Michal

Reputation: 5725

There is a number of ways in which your code could be improved, some of them are:

  • Error Handling - Added an On Error GoTo ErrorHandler block to handle any runtime errors gracefully.
  • Removed unnecessary Select statements to make the code cleaner and faster.
  • Added a message box to inform the user where the file has been saved.
  • Format only relevant cells in Column Q instead of the entire column:
Sub UnLoad_UnPick()

    Dim lastRow As Long
    Dim downloadsPath As String
    ' Get the current user's Downloads folder path
    ' (or use another method as suggested above)
    downloadsPath = Environ("USERPROFILE") & "\Downloads"

    On Error GoTo ErrorHandler

    ' Format only the relevant cells in column Q
    lastRow = Cells(Rows.Count, "Q").End(xlUp).Row
    If lastRow > 1 Then
        Range("Q2:Q" & lastRow).Style = "Currency"
    End If

    Columns("O:O").Cut
    Columns("A:A").Insert Shift:=xlToRight
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=downloadsPath & "\Transaction_Details.xlsx", _
                          FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Application.DisplayAlerts = True

    MsgBox "The file has been saved successfully in: " & downloadsPath, vbInformation, "Save Successful"

    Exit Sub

ErrorHandler:
   
    Application.DisplayAlerts = True
    MsgBox "An error occurred: " & Err.Description, vbCritical, "Error"
End Sub

Upvotes: 0

Related Questions