Open a CSV file from string given in cell of active workbook 1

[Zac5]

Dear All,

1. My cell refernce (1,1) contains a string e.g. myfilename (without the extension e.g. myfilename.csv) I want my macro in the active workbook to open myfilename.

Workbooks.Open Filename:=Range(1, 1) does not work because its a csv. How can I open it, I don't want to have to convert it or anything.

2. I will then want to do some copy/pasting from it into my active workbook, do I need to convert the csv to an excel workbook before I can do this, if so, how can I convert?

 

[PHV]

What about this ?
Workbooks.Open Filename:=Trim(Range(1, 1)) & ".csv"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

I would not use the Workbooks.Open method.

Rather on a blank sheet, do this FIRST... Data > Import External Data > IMPORT... and import the .csv file, parsing as appropriate.

THEN, turn on your macro recorder and record eiding the query table you just added to your sheet... Data > Import External Data > Edit Text Import do the import and FINISH.

Now you have recorded some VBA code. Post back with your recorded code to get help modifying to fit your requirement.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Zac5]

didn't work

 

[Zac5]

For rrng = 2 To LastRow

Workbooks.Open Filename:=Cells(rrng, 1)

'fails at next line because (rrng, 2) is of type csv

Workbooks.Open Filename:=Cells(rrng, 2)
Workbooks.Open Filename:=Cells(rrng, 3)

Next rrng

 

[bubba100]

What about something like?
Workbooks.Open Filename:=Sheets("Sheet1").Cells(1, 1).Value & ".csv"

 

[PHV]

'fails at next line because (rrng, 2) is of type csv
I'd say; fails because the active workbook has changed ...
What about this ?

Set ws = ActiveSheet
For rrng = 2 To LastRow
  Workbooks.Open Filename:=ws.Cells(rrng, 1)
  Workbooks.Open Filename:=ws.Cells(rrng, 2)
  Workbooks.Open Filename:=ws.Cells(rrng, 3)
Next rrng

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Zac5]

Thanks a lot all PHV's last post has solved my problem

Thanks PHV