VB to select path from cell

[gaz_chops]

I have the following macro I recorded, I need to amend it so that the 'Path' is populated from a cell which the user can enter -

Sub XeroCSV()
'
' XeroCSV Macro
'

'
    Dim FileName As String
    Dim Path As String
    Path = "\\?????????\home\????????\?????????\Documents\Xero Reports\Payroll Data for Xero\Xero CSV Imports\"
    FileName = Range("G2").Value & ".csv"
    Sheets("Xero Import").Select
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:=Path & FileName, FileFormat:=xlCSV, Local:=True
    'ActiveWorkbook.SaveAs Path & FileName, xlCSV
    Application.DisplayAlerts = True
    ActiveWindow.Close
    Sheets("Journal").Select
    Range("G5").Select
End Sub

 

[Joe4]

Let's say the cell in "A2".
Then just change this line:

Path = "\\?????????\home\????????\?????????\Documents\Xero Reports\Payroll Data for Xero\Xero CSV Imports\"

to this:

MyPath = Range("A2")

(Note: You should NOT use reserved words like "Path" as the name of variables! It can cause errors and other unexpected behavior!)

Make sure that the last "\" is at the end of that path.
I will sometimes do the following, in case people forget to do that.

MyPath = Range("A2")
If Right(MyPath,1)<>"\" Then MyPath = MyPath & "\"

 

[gaz_chops]

Thanks Joe, for some reason it is saving it into the original files 'Path' not the one noted in the cell!

 

[Joe4]

Thanks Joe, for some reason it is saving it into the original files 'Path' not the one noted in the cell!

You need to change this line here too:

    ActiveWorkbook.SaveAs FileName:=Path & FileName, FileFormat:=xlCSV, Local:=True

like this:

    ActiveWorkbook.SaveAs FileName:=MyPath & FileName, FileFormat:=xlCSV, Local:=True

to use the new name we picked for the variable.
Otherwise, it will use the reserved value ("Path" is the path of the current file)!

This is a good example why not to use reserved words as variables - your code can produce enexpected/unintended consequences!

 

[gaz_chops]

Think I found it, do i need to change this bit as well - Path to MyPath

ActiveWorkbook.SaveAs FileName:=Path & FileName, FileFormat:=xlCSV, Local:=True

 

[gaz_chops]

Yep that sorted it, many thanks.

Gaz

 

[Joe4]

You are welcome.