VBA: Specify drive letter for filepath to open?

[Coyner]

Hi,

Im trying to get the user to specify which drive they have a filepath mapped to in order to open and manipulate an excel spreadsheet but I can't get it right, any ideas where the below code is wrong?

sDrive = InputBox("What drive letter have you mapped to?")

sDrive = sDrive & ":"
'Open downloaded file
ChDir "sDrive\test folder\2021"
Workbooks.OpenText Filename:="sDrive\test folder\2021"

 

[Joe4]

Welcome to the Board!

It is important to note that in VBA, anything enclosed within double-quotes is treated as literal text.
Therefore, all variables must be outside of the double-quotes to be treated as variables and not literal text.

So your references to:

"sDrive\test folder\2021"

should be:

sDrive & "\test folder\2021"

 

[Fluff]

Along with what Joe has said, that won't work unless the drive is the active drive, you should use

chDrive sDrive
ChDir sDrive & "\test folder\2021"

although you don't need to drive the drive/directory to open a file

 

[Coyner]

So is this looking right:

sDrive = InputBox("What drive letter have you mapped to?")

sDrive = sDrive & ":"
'Open downloaded file
ChDrive sDrive
ChDir sDrive & "\Test file\2021"
Workbooks.OpenText Filename:=sDrive & "\Test file\2021\namedworkbook"


Also, i then want to save the amended file in a folder, how should i structure that code? Below is giving an error? Really appreciate this advice.

ActiveWorkbook.SaveAs Filename:= _
([sDrive & \Test file\2021\"]) & Format(Date, "MMMM YYYY") & " Structure" & ".xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False

 

[Fluff]

Try it like

ActiveWorkbook.SaveAs sDrive & "\Test file\2021\" & Format(Date, "MMMM YYYY") & " Structure" & ".xlsx", 51

 

[Coyner]

Thank you both very much, that works a charm!

 

[Fluff]

Glad we could help & thanks for the feedback.