GetOpenFilename error with spaces in file

[hoffey]

I'm not sure why but the following code results in an error when the file you point to includes spaces. It works fine otherwise.

Any thoughts? I'm thinking maybe I can replace the spaces with nil?

     Filename = Application.GetOpenFilename("Adobe PDF (*.pdf),*.pdf")
     
     destinLOC = Filename
     destinLOC = Replace(destinLOC, "pdf", "txt")
     
     destinPLN = Right(Filename, Len(Filename) - InStrRev(Filename, "\"))
     destinPLN = Replace(destinPLN, ".pdf", "")
     
     destinTXT = Right(Filename, Len(Filename) - InStrRev(Filename, "\"))
     [B]destinTXT = Replace(destinTXT, "pdf", "txt")[/B]
     
     taskid = Shell("C:\pdf\pdftotext.exe " & Chr(34) & Filename & Chr(34) & " " & destinTXT, vbNormalFocus)
             
     WAIT = Timer
     While Timer < WAIT + 2
        DoEvents  'do nothing
     Wend
     
     'Importation
     'destinTXT is the source below for some unexplained reason :)
     Workbooks.OpenText Filename:=destinTXT, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
        Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3 _
        , 1)), TrailingMinusNumbers:=True

This is the result of the debug immediately after the bolded line above..

Debug.Print destinTXT
Jun 09 PSR Lvl 2.txt
Debug.Print destinLOC
C:\Jun 09 PSR Lvl 2.txt
debug.Print destinPLN
Jun 09 PSR Lvl 2

The error is occurring during the Workbooks.OpenText event. I'm assuming it has to do with passing the wrong arguments to the Shell event in which case there is nothing for it to open. Actually, I'm certain that is what is happening.

Any thoughts or ideas are greatly appreciated! Thanks in advance..

 

[hoffey]

This fixed it.. Sorry for the post! I guess sometimes you just have to ask the question..

     destinLOC = Filename
     destinLOC = Replace(destinLOC, "pdf", "txt")
     
     destinPLN = Right(Filename, Len(Filename) - InStrRev(Filename, "\"))
     destinPLN = Replace(destinPLN, ".pdf", "")
     [B]destinPLN = Replace(destinPLN, " ", "")[/B]
     
     destinTXT = Right(Filename, Len(Filename) - InStrRev(Filename, "\"))
     destinTXT = Replace(destinTXT, "pdf", "txt")
     [B]destinTXT = Replace(destinTXT, " ", "")[/B]

 

[rishidoshi]

Hi,
I have a similar issue. But in my case i cannot replace/change the filename.

I open a file (the name will have spaces in it). then perform some operations. e.g.

sheet_target = ThisWorkbook.Name
bt_shapes.OnAction = sheet_target & "!add_form_select"

basically i assign a macro named "add_form_select" to an image (button). This macro resides in this workbook only!
this code work perfectly on some machines and does not work on some.
The ones on which it doesnt work, i remove spaces from the filename and re-run. IT WORKS!
But i cannot chage it each time as this software is used by clients at their ends.
I need to trace the error as to why it works on some and doesnt on some PCs, and solve it forever. :P
Regards