Save to specified folder, Not original folder

[andymalan]

I have some code that allows the user to select a file from a folder named CSV ("\Desktop\Excalibur QCP21\ExcaliburProPlus\CSV Files")

I want to save as a new workbook to a different folder ("C:\Desktop\Excalibur QCP21\02_ImportToExcalibur")
When I run the code it executes without errors, and the file is saved........but to the wrong folder nl. the csv folder

here is the code that saves the workbook
Sub aSaveToTheNewName()
'Save the new file using the name created in Q2

Dim ThisFile As String
Dim strFileName As String
Dim Usersname As String
Dim myFileName As String
Dim Path As String

'ThisFile = Range("Q2").Value
'ActiveWorkbook.SaveAs FileName:=ThisFile
', FileFormat:=xlNormal
Application.DisplayAlerts = False
Usersname = Environ("USERNAME")
strFileName = "C:\Users\" & Usersname & _
"C:\Desktop\Excalibur QCP21\02_ImportToExcalibur"
myFileName = Range("Q2")
ActiveWorkbook.SaveAs FileName:=Path & myFileName & ".xlsx", FileFormat:=51

ThisFile = Range("Q2").Value
ActiveWorkbook.SaveAs FileName:=ThisFile

I will be grateful for your help

 

[igold]

Well, you have assigned the variables "myFileName" & "ThisFile" to the value of Cell Q2.

The question is: What is in Cell Q2.

 

[andymalan]

Well, you have assigned the variables "myFileName" & "ThisFile" to the value of Cell Q2.

The question is: What is in Cell Q2.

hello igold

Q2 holds the generated filename that the workbookmust be named

 

[igold]

Does this do what you want. The CSV must be the ActiveWorkbook when the code is run. Also the code assumes that the worksheet that contains the name in Cell Q2 is named "Sheet1". If not please change worksheet name in the code to the correct name where indicated.

Sub aSaveToTheNewName()

    'Save the new file using the name created in Q2
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ThisFile As String, strFileName As String, Usersname As String
    Dim myFileName As String, Path As String
   
    Application.DisplayAlerts = False
    Usersname = Environ("USERNAME")
    strFileName = "C:\Users\" & Usersname & _
        "\Desktop\Excalibur QCP21\02_ImportToExcalibur"
    If ActiveWorkbook.Name = ThisWorkbook.Name Then
        MsgBox "The CSV Workbook Must Be the Active Workbook!"
        Application.DisplayAlerts = True
        Exit Sub
    End If
    myFileName = ThisWorkbook.Worksheets("Sheet1").Range("Q2")  'Change worksheet name here
    Path = strFileName
    ActiveWorkbook.SaveAs Filename:=Path & "\" & myFileName & ".xlsx", FileFormat:=51
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
   
End Sub

 

[andymalan]

Hi igold

I get an error in the line
ActiveWorkbook.SaveAs FileName:=Path & "\" & myFileName & ".xlsx", FileFormat:=51

error reads : Runtime error 2004.
Method 'SaveAs' of object'_Workbook' Failed

 

[igold]

I am not sure why you would get that error. The code tested fine for me...

 

[igold]

Also what version of Excel are you using and is it an 1004 or a 2004 error.

 

[Joe4]

Store your whole new file path/name in a variable, and then use a message box to return what it is.
Then maybe it will be evident what the issue is, i.e.

Sub aSaveToTheNewName()

    'Save the new file using the name created in Q2
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ThisFile As String, strFileName As String, Usersname As String
    Dim myFileName As String, Path As String
    Dim newFileName As String
   
    Application.DisplayAlerts = False
    Usersname = Environ("USERNAME")
    strFileName = "C:\Users\" & Usersname & _
        "\Desktop\Excalibur QCP21\02_ImportToExcalibur"
    If ActiveWorkbook.Name = ThisWorkbook.Name Then
        MsgBox "The CSV Workbook Must Be the Active Workbook!"
        Application.DisplayAlerts = True
        Exit Sub
    End If
    myFileName = ThisWorkbook.Worksheets("Sheet1").Range("Q2")  'Change worksheet name here
    Path = strFileName
    newFileName = Path & "\" & myFileName & ".xlsx"
    MsgBox newFileName
    ActiveWorkbook.SaveAs Filename:=newFileName, FileFormat:=51
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
   
End Sub

 

[andymalan]

Store your whole new file path/name in a variable, and then use a message box to return what it is.
Then maybe it will be evident what the issue is, i.e.

Sub aSaveToTheNewName()

    'Save the new file using the name created in Q2
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ThisFile As String, strFileName As String, Usersname As String
    Dim myFileName As String, Path As String
    Dim newFileName As String
  
    Application.DisplayAlerts = False
    Usersname = Environ("USERNAME")
    strFileName = "C:\Users\" & Usersname & _
        "\Desktop\Excalibur QCP21\02_ImportToExcalibur"
    If ActiveWorkbook.Name = ThisWorkbook.Name Then
        MsgBox "The CSV Workbook Must Be the Active Workbook!"
        Application.DisplayAlerts = True
        Exit Sub
    End If
    myFileName = ThisWorkbook.Worksheets("Sheet1").Range("Q2")  'Change worksheet name here
    Path = strFileName
    newFileName = Path & "\" & myFileName & ".xlsx"
    MsgBox newFileName
    ActiveWorkbook.SaveAs Filename:=newFileName, FileFormat:=51
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
  
End Sub

Joe4, thank you for this code it does exactly what I needed. I commented out the "message Box" after first testing.

 

[andymalan]

Also what version of Excel are you using and is it an 1004 or a 2004 error.

igold, thank you for your willingness to solve my problem, joe4 sent me some code which does the job nicely.
kind regards Andy