Capture name of data file when importing into another file

[Joe4]

I am using the following code to browse to a CSV file and import it into my current workbook. How can I “capture” the name of the text file (myFile) for future use?

    myFile = Application.GetOpenFilename("All Files,*.csv")
    If myFile = False Then
        Exit Sub
    End If
    
'   Open file
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & myFile, Destination:=Range("A1"))
        .Name = "DATA"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "|"
        .TextFileColumnDataTypes = Array(1)
        .Refresh BackgroundQuery:=False
    End With

Thanks

 

[Norie]

Don't you already have it in the variable myFile?

 

[Joe4]

Don't you already have it in the variable myFile?

Its not a string. It must be some sort of object.

I tried:

Range("A1")=myFile

and

MsgBox myFile

and

MsgBox myFile.Name

but none of them worked.

 

[Norie]

It's not an object, it's a string.

That is unless you've hit Cancel or selected multiple files, and I don't think the code will allow multiple selections as it is.

 

[Joe4]

Have you actually tried it yourself?
Can you get the file name to appear in a Message Box or be written to a cell in the file?
If you have, please post the code.

 

[Norie]

Yes I did try it.

The only thing I changed was the file extension - don't have any CSVs kicking around.

Mind you I probably do but Vista's hiding them from me.

 

[VoG]

Tested using a CSV file

Sub test()
Dim MyFile
MyFile = Application.GetOpenFilename("All Files,*.csv")
    If MyFile = False Then
        Exit Sub
    End If
MsgBox TypeName(MyFile)
MsgBox MyFile
End Sub

MyFile is returned as type String and contains the full path to the file.

 

[Joe4]

I think my computer is messing with me, and trying to make me look bad. I tried VoGII's code, and it worked. Then I tried the code that I posted earlier that I had commented out because it errored out, and it also works now.

I can't explain why it didn't work before, but it works now, so I guess "all is well that ends well".

Thanks to the both of you.

 

[Norie]

No problem.

If you think your computer is messing with you, you'll not believe the problems I've been having since getting a new machine with Vista as the OS.

Couple of days ago I lost thousand of files that I'd transferred from the old hard drive.

Still got the old hard drive though.

 

[Joe4]

Yeah, I have been having a number of wacky things since I got this new laptop, especially with Copy/Paste. For some reason, it doesn't like CTRL-C and CTRL-V sometimes.