Thread: Open file with macro

Hello all!
This is the first time I use a macro so bear with me

Im using Excel 2011 for mac.

What I'm trying to do is to open a CSV file but I want the macro to use a cell value as filename. That is, I want the macro to open a CSV with the filename stated in a cell value. So, in the code below I would like to change the filename to a variable that is populated from a cell. Also, I would like to create a variable for the path as this may change. See my code below. The first example works and if I change the filename and path to variables it doesnt work, see second example.

Any suggestions how to change my code are appreciated.

Br
cristian

I have this code:

Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = "CSV"
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Excel Test\hello.csv", Destination:=Range( _
        "$A$1"))
        .Name = "hello"
        .FieldNames = True
       ...
       ...
       ..

    End With

And I want to change to something like this (this doesn't work):

    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = "CSV"
    Dim FName As String
    FName = Range("R42").Value
    Dim FPath2 As String
    FPath2 = Range("R52").Value
   
    With ActiveSheet.QueryTables.Add(Connection:= _
 TEXT;FPath2 & "\" & FName & ".csv", Destination:=Range( _
        "$A$1"))
        
        .Name = FName
        
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
...
..
.

Thank you Winon. It doesn't help. The error message is " Expected separator" now....It seems that it expects some kind of separator and it points out the dot in ".csv"

"TEXT;FPath2 & "\" & FName & ".csv"

The value of cell R42 is "FileName" without any extention. Hmm...I will test this tomorrow on a Win-computer.

ok,
This seems to work I think. But, the variables are empty. The variables are in sheet1 and the new sheet created with name CSV is the active one. How do I populate the variable from cell R42 in Sheet1?

 Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = "CSV"
    Dim FName As String
    FName = Range("R42").Value
    Dim FPath2 As String
    FPath2 = Range("R52").Value
   
    With ActiveSheet.QueryTables.Add(Connection:= _
 FPath2 & FName & ".csv", Destination:=Range( _
        "$A$1"))

Ok. Now I got it. The problem with pointing out the wrong sheet was my bad. But now I have a new problem.
Error:
"Excel cannot find the text file to refresh this external data range. Check to make sure the text file has not been moved or renamed, then try the refresh again."

Code in red is highlighted.

 Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = "CSV"
    Dim FName As String
    FName = ThisWorkbook.Sheets("Sheet1").Range("R42").Value
    Dim FPath2 As String
    FPath2 = ThisWorkbook.Sheets("Sheet1").Range("R52").Value
   
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FPath2 & "\" & FName & ".csv", Destination:=Range("$A$1"))
        
        .Name = FName
       ... 
       ...
        .Refresh BackgroundQuery:=False
    End With

TestWorkBook.xlsm


I have checked the backslashes. Nothing there.

The value of R42 is just "FileName". That is, I want the filename to be FileName.xsl

Value of R52 is "C:\Excel Test\FileName.csv"

Regarding the sample workbook I thought it would be enough with my code. Here's a sample doc. I dont manage to attach the CSV file.

Br
cristian

Hello!
Thank you all for you patience and help! It works!!!!

I did a misstake on my path when working with different computers - Mac/Win. Forward slash/backward slash...

BR
cristian

Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = "CSV"
    Dim FName As String
    FName = ThisWorkbook.Sheets("Sheet1").Range("R42").Value
    Dim FPath2 As String
    FPath2 = ThisWorkbook.Sheets("Sheet1").Range("R52").Value
   
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FPath2 & "\" & FName & ".csv", Destination:=Range("$A$1"))
        
        .Name = FName
       ... 
       ...
        .Refresh BackgroundQuery:=False
    End With