VBA Substitute Drive Path for Cell.Value

[Pinaceous]

Hi All,

I'm working with the drive path within a sub:

FSO.MoveFile Source:="C:\Users\Desktop\Test(2020).xlsm", Destination:=sMain & "\Test(2020).xlsm" ' copy and delete from source

I'm interested in a code that can indicate the name of the file as seen above referenced within the workbook itself.

For example:

FSO.MoveFile Source:="C:\Users\Desktop\ &ThisWorkBook.Sheets(1)Range("A1").Value", Destination:=sMain & "\&ThisWorkBook.Sheets(1)Range("A1").Value" ' copy and delete from source

This is the idea that I'm exploring:

&ThisWorkBook.Sheets(1)Range("A1").Value

where the drive path references the name of the file as a location in the workbook.

Please let me know, if this is possible.

Thank you,
pinaceous

 

[Michael M]

AS you haven't provided ALL of your code, I'm guessing something like

FSO.MoveFile Source:="C:\Users\Desktop\" & Sheets(1).Range("A1").Value & ", Destination:= sMain & "\" & Sheets(1).Range("A1").Value

Depends on what sMain holds though ?

 

[Pinaceous]

Hi Michael,

I'm glad that you posted. Here is the code that I'm working with:

Sub CreateFolders()

Dim FSO As Object, sourceFolder As Object, file As Object

  Dim sPath As String, sMain As String, sFolder As String
  Dim i As Long

    Set FSO = CreateObject("Scripting.FileSystemObject")
    sPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    sMain = sPath & "\" & ThisWorkbook.Sheets(1).Range("B11").Value
        'T1
  If Dir(sMain, vbDirectory) <> "" Then 'if the folder exists
  'Do nothing
    MsgBox "Duplicate Folders!"

   
  Exit Sub

  Else ' 'if the folder not exists
      MkDir sMain

        FSO.MoveFile Source:="C:\Users\Desktop\ "& Sheets(1).Range("A1").Value & ", Destination:= sMain & "\" & Sheets(1).Range("A1").Value
       
    End If
   
Set FSO = Nothing

For i = 11 To 23
    sFolder = sMain & "\" & Sheets(1).Range("C" & i).Value
        'T1
    If Dir(sFolder) = "" Then
      MkDir sFolder
    End If
  Next


End Sub

I put in your line of the code from Post#2, could you help me with the syntax of it?

Thank you,
pinaceous.

 

[Michael M]

Sorry Pinaceous
Don't have Excel atm, hopefully someone will jump in and help out ...