Getting data from CSV file into a workbook

A

AventAClue

New Member
Joined
Sep 4, 2011
Messages
21
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
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?
 
Upvote 0
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.
 
Upvote 0
Arul.rajesh said:
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?
Click to expand...


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 :)
 
Upvote 0
Arul.rajesh said:
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.
Click to expand...

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?
 
Upvote 0
Code: 
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Create an CSV or ICS File
Replies
2
Views
94
RRaively1
R
S
  • Question Question
Merge two table from different csv files
2 3
Replies
24
Views
647
Soly_90
S
D
  • Locked
  • Question Question
VBA to copy only matching data from 2 csv workbooks into the "Master Workbook"
Replies
1
Views
338
RoryA
RoryA
T
  • Question Question
How can I reduce file size of workbook referencing sheets in another workbook?
Replies
4
Views
229
bobsan42
bobsan42
D
  • Solved
VBA to copy only matching data from 2 csv workbooks into the "MasterCSV"
2
Replies
18
Views
945
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,540
Messages
6,179,417
Members
452,912
Latest member
alicemil

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top