Import csv data source to pre-formatted excel template sheet

[embla]

Hi there urgent help required.

I need to have a csv data source fed into a template on a consistent basis.

the template has specified headings. how can i do this.

I believe i need a macro but i am not sure how.

 

[Trevor_S]

Could you please provide details of the CSV fields and the template columns? Are there numeric field that need to be formatted in any specific way? Should the data be fed into the top of the template (if so, starting with which row) or be added to the end of existing data?

 

[embla]

Imgur: The most awesome images on the Internet

 

[embla]

It should be fed into the top of the table

 

[Trevor_S]

Thanks. The csv screen picture is a bit small for me to see all the detail - but I can see enough to give you a start, and let you know what you need to complete.

It seems that the first row (excluding headers and blank row) on the csv data file is row 3, and the first row on the template to be completed is row 5. Also template columns A, B and C are from csv columns B, C and F.

If so, put this macro into the Template workbook:

Sub ImportCsv()

Dim TemplateWb As Workbook
Set TemplateWb = ActiveWorkbook
Dim DataWb As Workbook
Set DataWb = Workbooks.Open("C:\Data.csv") 'If csv file always has same name/path, use this line and edit filename
Set DataWb = Workbooks.Open(Application.GetOpenFilename("CSV Files (*.csv), *.csv")) 'If csv filename / location changes, use this line

LastDataRow = DataWb.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

For DataRow = 3 To LastDataRow
    TemplateRow = DataRow + 2
    TemplateWb.ActiveSheet.Range("A" & TemplateRow).Value = DataWb.ActiveSheet.Range("B" & DataRow)
    TemplateWb.ActiveSheet.Range("B" & TemplateRow).Value = DataWb.ActiveSheet.Range("C" & DataRow)
    TemplateWb.ActiveSheet.Range("C" & TemplateRow).Value = DataWb.ActiveSheet.Range("F" & DataRow)
    'Repeat these lines with appropriate columns for each data item to enter onto template
Next

DataWb.Close (False)

End Sub

I've given you two options for opening the csv file - you'll need to delete the option that you don't want:

• If the csv file always has the same name and location, keep this line: Set DataWb = Workbooks.Open("C:\Data.csv") ... You'll need to change the filename/location to the correct one.
• If the csv file could have different filenames and/or locations, keep this line: Set DataWb = Workbooks.Open(Application.GetOpenFilename("CSV Files (*.csv), *.csv")) ... A standard File Open window will appear when the macro is run, allowing you to select the csv file.

You'll also need to complete the mapping of csv columns to template columns. I've done three, based on the assumptions above. You'll need to add additional lines where I've put 'Repeat these lines with appropriate columns for each data item to enter onto template, so that you have a line for each Template column that you want data brought in to.

Before you run the macro, the sheet containing the Template must be selected.

Hope this helps!

 

[embla]

Omg!!! This is excellent... Perfect