Hidden String

**hoffey** · 07-14-2009, 08:11 PM

     destinLOC = Filename
     MsgBox destinLOC
     destinLOC = WorksheetFunction.Substitute(Instring, "pdf", "txt")
     MsgBox destinLOC

Not sure why, but after the first MsgBox I can clearly see the location of the file, but as soon as I run the Substitute function, it shows up in the second MsgBox empty.

Any ideas? Thanks in advance...

**shg** · 07-14-2009, 08:25 PM

What is Instring?

Once you get that sorted, you can use the VBA native Replace function instead of WF.Substitute.

**hoffey** · 07-14-2009, 08:31 PM

Thanks for the reply SHG. Yes, I'm new to VBA so sometimes the first solution I come across is nowhere near ideal.

Sub PdFN()

Dim taskid As Double
Dim sourcePDF As String
Dim destinTXT As String
Dim Filename As String

     Filename = Application.GetOpenFilename("Adobe PDF (*.pdf),*.pdf")
    
     destinLOC = Filename
     destinLOC = Replace(destinLOC, "pdf", "txt")
    
     destinTXT = Right(Filename, Len(Filename) - InStrRev(Filename, "\"))
     destinTXT = Replace(destinTXT, "pdf", "txt")

     taskid = Shell("C:\pdf\pdftotext.exe " & Chr(34) & Filename & Chr(34) & " " & destinTXT, vbNormalFocus)
    
     'Importation
     Workbooks.OpenText Filename:=destinLOC, 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
   
    Columns("A:AH").Select
    Columns("A:AH").EntireColumn.AutoFit
    ActiveWindow.LargeScroll ToRight:=-1
    Range("A1").Select

End Sub

Changing to Replace did the trick, now however it is not opening correctly. I'm getting a 1004 error in the bolded area. Is there a better way to open the file?

Thanks in advance..