VBA code for directory names with spaces

**donyc** · 02-23-2011, 06:16 PM

Hi all, could someone please help me with this annoying problem...I download files from ftp and the current Code that works where it downloads everything in the folder where my excel spreadsheet is with the VBA macro: M:\My FTP\New York\

Please Login or Register  to view this content.

However, I want the files to be downloaded to:M:\My FTP\New York\Files

I tried couple things and neither worked:

Please Login or Register  to view this content.

Also with double quotations since there are spaces in the file name

Please Login or Register  to view this content.

Could someone please help. What am I doing wrong?

Thanks so much

**Leith Ross** · 02-23-2011, 11:40 PM

Hello donyc,

I am not sure what you want to accomplish. The code you provided simply writes a line of data into a text file. This has nothing to do with selecting a directory.

**donyc** · 02-24-2011, 12:00 AM

right so it writes the batch which gets executed and file gets downloaded from ftp into the specified folder..it seems it will only download into a folder directory which contains no spaces.. as soon as I list the directory name which contains spaces the macro does not download anything

**donyc** · 02-24-2011, 12:21 AM

Hi to add here is the full code I use..I am creating a batch file which upon execution downloads the files to specified directory from ftp..
The code works when I let it download to the path the current workbook is in but not when I specify the full path 1 folder deeper it does not work.. I use the below code in red( the full code is below)

This is last thing that is keeping me from finishing this project. Any help would be greatly appreciated!

Print #lInt_FreeFile01, "recv myfile.txt" & """M:\test\New York\Manhattan Streets" & "\myfile.txt"""

Please Login or Register  to view this content.

'it works if I use below line, but that just download to the current path(M:\test\New York) where my workbook is saved, I just need the files downloaded 1 folder 'deeper (M:\test\New York\Manhattan Streets)

Print #lInt_FreeFile01, "recv myfile.txt" & ThisWorkbook.Path & "\myfile.txt;"

Please Login or Register  to view this content.

**Leith Ross** · 02-24-2011, 01:48 AM

Hello donyc,

This macro may solve your problem. It converts a Windows long path (with spaces and other characters) to a DOS directory format. Copy all the codebelow into the same VBA module.

Please Login or Register  to view this content.

**donyc** · 02-24-2011, 10:01 AM

thanks Leith..I get Compile Error
"Only comments may appear after End Sub, End FUnction, or End Property"

Am I inserting this in the wrong space within my code?

**Leith Ross** · 02-24-2011, 01:30 PM

Hello donyc,

Here are the instructions to copy the macro into a separate module in your VBA project. You will, of course, need to add the macro GetShortPath to your macro.

Adding the Macro