Getting data from CSV file into a workbook

[AventAClue]

Hi All,

Hoping someone may be able to help out with some knowledge and code.

I have an excel workbook that I'd like to automate the process of me going to a csv file, copying it's data, and pasting it back to a worksheet within my workbook.

Ive just had some great help from Peter SSs to manipulate some data from worksheet to worksheet within the workbook, but now hope to go that little further in being able to have the csv data part of it included in the process.

Thinking along the lines of pressing a macro button within my workbook it:

-Asks which file i wish to open
-Upon me locating and selecting the csv, copies the sheet contents (can be the whole csv file sheet)
-Closes the csv file and pastes the copied data to worksheet called "DataDownload" in my workbook

It could then continue on and run the code that Peter SSs provided for me.

The thread that covers my previous 'worksheet to worksheet' code which may help in explaining what I'm after is located at: http://www.mrexcel.com/forum/showthread.php?p=2874620#post2874620


Thanks very much

 

[Arul.rajesh]

what is the name of the workbook that contains the sheet "DataDownload".. OR do you want to be able to select this file as well?

 

[jkpieterse]

Maybe this page gets you started?
http://www.jkp-ads.com/articles/importtext.asp

 

[Arul.rajesh]

sub copy_CSV()

Dim srcfile as Workbook
Dim sourcefile
sourcefile = Application.GetOpenFilename(,,"Select CSV file")
Workbooks.Open sourcefile
Set srcfile = ActiveWorkbook
srcfile.sheets(1).Copy Destination:=Workbooks("filenmame that contains Datadownload").sheets("Data download").Cells(1, 1)
srcfile.Close

end sub

This code will prompt you for the csv file. Copy the entire sheet to the "DataDownload" sheet. Also the destination workbook will have to be open.

 

[AventAClue]

what is the name of the workbook that contains the sheet "DataDownload".. OR do you want to be able to select this file as well?

Hi Arul.rajesh

Wow! I had hardly finished typing the question lol

The workbook is a template I use called BillingPeriod_Template.xlsm however as I start each new period I save the template for that period accordingly and use that one, saving the template for use again next time.

As I'll already be working within the workbook when I run the macro is it possible it can somehow flag it as open workbook or something rather than requiring the workbook name, because that name would differ depending on which billing period I was in?


Thanks

 

[AventAClue]

sub copy_CSV()

Dim srcfile as Workbook
Dim sourcefile
sourcefile = Application.GetOpenFilename(,,"Select CSV file")
Workbooks.Open sourcefile
Set srcfile = ActiveWorkbook
srcfile.sheets(1).Copy Destination:=Workbooks("filenmame that contains Datadownload").sheets("Data download").Cells(1, 1)
srcfile.Close

end sub

This code will prompt you for the csv file. Copy the entire sheet to the "DataDownload" sheet. Also the destination workbook will have to be open.

Thanks again. Your replies are there before I can finish my question. lol

Ok, I tried that code and I get a runtime error 9 Subscript out of range on this part
srcfile.sheets(1).Copy Destination:=Workbooks("filenmame that contains Datadownload").sheets("Data download").Cells(1, 1)

I tried placing the name of my workbook complete with file extension between the quotation marks of this bit ("filenmame that contains Datadownload") but it still doesn't like that. Or did i get that wrong?

 

[Arul.rajesh]

sub copy_CSV()

dim DestFile as workbook
Dim srcfile as Workbook
Dim sourcefile
set DestWB=Activeworkbook
sourcefile = Application.GetOpenFilename(,,"Select CSV file")
Workbooks.Open sourcefile
Set srcfile = ActiveWorkbook
srcfile.sheets(1).Copy Destination:=DestFile.sheets("DataDownload").Cells(1, 1)
srcfile.Close

end sub

the sheet name must be DataDownload not "Data Download" I think. Also when you run this part the destination file should be the activeworkbook.

 

[AventAClue]

Maybe this page gets you started?
http://www.jkp-ads.com/articles/importtext.asp

Sorry Jan, I missed your reply!

Thankyou. I will check that page out.

 

[jkpieterse]

What about my macro-free approach?

 

[jkpieterse]

Ah, I see you've seen it.