Macro to import a Csv file into an existing workbook - they are both in the same folder

[yorkshirelad]

Apologies for posting again but I urgently need to get this resolved.

I need to import a CSV file (test.csv) into a workbook (data.xlsm) which I have open.

They are both in the same folder.

I cannot use the full directory path as the macro needs to be used on other pcs so the commands must be relative.

I had a suggested answer which brings the data in but it uses a new file rather than the workbook that I have open.

It needs to import into the existing workbook as I have other data in the workbook and macros linked to it

The suggested solution is below but I need it to work as noted above

Sub import_csv_file()
Dim file As String

file = "test.csv"
Workbooks.Open (file)

End Sub


Any assistance would be greatly appreciated.

 

[Weaver]

you can get the address of the current workbok using activeworkbook.path, as in:

fldr=activeworkbook.path
Workbooks.Open (fldr & "/" & file)

 

[yorkshirelad]

Thanks Weaver your help is really appreciated.

Can I just ask how would I use code below to amend the macro I was originally given or do I need to start again?

Sorry for lack of knowledge but I've got an urgent deadline and I've inherited this task at the last minute


fldr=activeworkbook.path
Workbooks.Open (fldr & "/" & file)


Macro provided...

Sub import_csv_file()
Dim file As String

file = "test.csv"
Workbooks.Open (file)

End Sub

 

[Trevor G]

Try this

Sub OpenCSV()
Dim strFile As String
Dim strCSV As String
strFile = ActiveWorkbook.Path
strCSV = "test.cvs"
Workbooks.Open strFile & "\" & strCSV
End Sub

 

[yorkshirelad]

That opens the file which is great but it renames the new file 'test' rather than importing into my original worksheet.

Any assistance would be really appreciated.

Many thanks

 

[Trevor G]

If that is opening the file (which I think is what you are asked for) do you want the data on the sheet to be copied into your main file? If so please state which sheet it is to be copied to. Do you want the CSV file then to be closed?

 

[ponnars]

Try something like this if you need to copy the content from csv

Requires 'data.xlsm' to be open before running the macro


Sub OpenCSV()
Dim strFile As String
Dim strCSV As String
strFile = ActiveWorkbook.Path
strCSV = "test.csv"
Workbooks.Open strFile & "\" & strCSV
Range("A1").Select
Range(Selection, Selection.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("data.xlsm").Activate
Range("A1").Select
ActiveSheet.Paste
Workbooks(strCSV).Close
End Sub


Sorry if this doesn't help

 

[yorkshirelad]

That works a treat ponnars - thanks very much for your help

 

[VoG]

I think this would be more efficient

Sub OpenCSV()
Dim strFile As String
Dim strCSV As String
strFile = ActiveWorkbook.Path
strCSV = "test.csv"
Workbooks.Open strFile & "\" & strCSV
Range("A1").CurrentRegion.Copy Destination:=Workbooks("data.xlsm").Sheets("Sheet1").Range("A1")
Workbooks(strCSV).Close
End Sub

 

[yorkshirelad]

Many thanks - that's excellent, I appreciate the suggestion. It works a treat.