Import multiple data sources into worksheet

[mabbott]

Hi all,

I currently have a workbook which consists of a worksheet for each financial week of the year to which I manually copy daily volume and vehicle capcities into according to which week they refer to. After the data is copied over I am then adding VLOOKUP and other formuals to analyze this data

I would like to create a marco that automatically imports the data and formats it according to my needs and also adding the formulas that I'm using.

The data that I wish to import gets sent to me as an .xls file and is named according to the day it covers for example "9418GR - OTOLQL - Shipment Volume - Mon-12-Jul-10.xls".

My questions more refers to actually the import process as the file name changed daily...can this be written into the marco? Also how do I specify which worksheet the data gets copied to.

Thanks for all help in advance!!

 

[John Davis]

Hi all,

I currently have a workbook which consists of a worksheet for each financial week of the year to which I manually copy daily volume and vehicle capcities into according to which week they refer to. After the data is copied over I am then adding VLOOKUP and other formuals to analyze this data

I would like to create a marco that automatically imports the data and formats it according to my needs and also adding the formulas that I'm using.

The data that I wish to import gets sent to me as an .xls file and is named according to the day it covers for example "9418GR - OTOLQL - Shipment Volume - Mon-12-Jul-10.xls".

My questions more refers to actually the import process as the file name changed daily...can this be written into the marco? Also how do I specify which worksheet the data gets copied to.

Thanks for all help in advance!!

You could probably use something like this to capture the changing days.


Workbooks.Open Filename:="D:\Common\data\excel\9418GR-OTOLQL-Shipment Volume- " & Format(DateAdd("d", 0, Date), "ddd-dd-mmm-yy") & ".xls"

And you could use the macro recorder to record a session for placing your formulas and vlookups. At least that would get you started.