VBA Referencing a folder

[rplohocky]

Hello,
I'm using this code to go through a specific folder and converts from .csv to .xlsx. The folder changes all the time so I added a reference line. The issue is it doesn't seem to be working, it doesn't go to that folder. Can anyone look at the code to see if something is missing?

Sub ConvertCSVToXlsx()
'This macro loops through all workbooks in one folder and changes the file extension from csv to xlsx.

    Dim myfile As String
    Dim oldfname As String, newfname As String
    Dim workfile
    Dim folderName As String
    

XXXXXXX THIS IS THE WORKBOOK THAT HOLDS THE CELL LOCATION FOR THE FILE NAME XXXXXXXXXXXX
'Below sets a WORRKBOOK reference to ("6251 Vivint Rental Report.xlsm") workbook.
    Dim src As Workbook
    Set src = Workbooks("6251 Vivint Rental Report.xlsm")
    
'Below set a CELL reference to the("6251 Vivint Rental Report.xlsm") workbook.
'The numbers below refer to row number and column number respectivly. (33, 4) 33= row, 4 = column.
    Dim valueTo As String
    valueTo = src.Worksheets("Data").Cells(17, 14)
XXXXXXXXXXXXXXXX DOWN TO HERE XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX



    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
'   Capture name of current file
    myfile = ActiveWorkbook.Name





XXXXXXXXX THIS IS THE PATH TO THE FOLDER XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 
'   Set folder name to work through
    folderName = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\6251 Vivint Rental report\" & valueTo
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX



    
'   Loop through all CSV filres in folder
    workfile = Dir(folderName & "*.CSV")
    Do While workfile <> ""
'       Open CSV file
        Workbooks.Open Filename:=folderName & workfile
'       Capture name of old CSV file
        oldfname = ActiveWorkbook.FullName
'       Convert to XLSX
        newfname = folderName & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=newfname, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Close
'       Delete old CSV file
        Kill oldfname
        Windows(myfile).Activate
        workfile = Dir()
    Loop
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

 

[PatOBrien198]

do you have a trailing back-slash at the end of the cell holding the "valueTo"?

 

[rplohocky]

do you have a trailing back-slash at the end of the cell holding the "valueTo"?

No I don't, when I put one there the code turns red. What would it look like?

'   Set folder name to work through
    folderName = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\6251 Vivint Rental report\" & cellValue\  '????????

 

[PatOBrien198]

No, I meant in:

  valueTo = src.Worksheets("Data").Cells(17, 14)

does your entry in cells(17, 14) contain a backslash at the end?

 

[PatOBrien198]

you could do something like this....

'   Set folder name to work through
    folderName = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\6251 Vivint Rental report\" & valueTo

    if Right(folderName, 1) <> "\" then
          folderName = folderName & "\"
    end if

 

[rplohocky]

you could do something like this....

'   Set folder name to work through
    folderName = "\\fleet.ad\data\Data1\VMSSHARE\FS\FPSCOEASSO\Temporary Fleet Reports\6251 Vivint Rental report\" & valueTo

    if Right(folderName, 1) <> "\" then
          folderName = folderName & "\"
    end if

OMG! Your a genius! It worked thanks for the help!