How can I remove the quotes from a file path string?

[ransomedbyfire]

I am using the code

Set oFS = New FileSystemObject

TempFolder = oFS.GetSpecialFolder(TemporaryFolder)

to find the user's temp folder.

And then I am using this code to retrieve data from some text files I've written elsewhere in the macro.

For filenum = 1 To symbols
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & TempFolder & "\MyFileName" & filenum & ".CSV", Destination:=Sheet1.Cells(2, filenum + 1))
        .Refresh BackgroundQuery:=False
    End With
    
Next filenum

But I keep getting errors because Excel insists on putting the quotes from TempFolder right after "TEXT;" when this line of code obviously prefers just to have "TEXT:C:\....", Destination.... etc.

What can I do to fix this?

Thanks!

 

[tweedle]

try:

TempFolder = Replace(TempFolder, Chr(34), "")

before getting into your For loop.

 

[ransomedbyfire]

Hmm. Thanks a lot for the suggestion! But I think it'll need some tweaking since this code may get used in different computers where the Temp Folder path is not the same as mine. So, I'm thinking, I could use Len a few times or something to get the positions exact?

 

[tweedle]

Can you post the string as presented by the code?

 

[ransomedbyfire]

Oh wow. This is odd. Apparently, the quotations aren't my problem.

The code I've posted actually returns this:

<table border="0" cellpadding="0" cellspacing="0" width="64"> <col width="64"><tbody><tr height="19"> <td style="height: 14.4pt; width: 48pt;" height="19" width="64">TEXT;C:\Users\MyName\AppData\Local\Temp\Filename1.CSV</td> </tr> </tbody></table>

So, I'm not sure what's messing me up after all.

 

[ransomedbyfire]

Okay, so I feel pretty stupid right now. Come to find out, the error had nothing to do with quotes. Thank you, tweedle, for getting me to look deeper into it. I think I've got it fixed now.