Import Data into New Excel Spreadsheets?? 1

[jennuhw]

We have a standard document that needs to be filled out for a customer for every quote made to them. Is there a way to automate inputing the new data and saving from another spreadsheet or Access database? The original document always keeps the same cell placement, but the data changes constantly. I hope I explained this right....

 

[DaleWatson123321]

Hi jennuhw,

If importing the data from Access, you will need a query in Access which exports the data to an Excel file - call it "ImportFile.xls" for this example.

Then in your Customer File (Excel file), you should add a new sheet - call it "ImportedData" for this example.

In the following code, set up to activate when the Excel file is opened, the code will merge the data from "ImportFile.xls" into the "ImportedData" sheet.

It is of course mandatory that the data being imported ALWAYS has the same structure. This is required so that the formulas in your "CustomerQuote" sheet can always "point" to the proper cells in the "ImportedData" sheet.

This is the code:

Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open:="C:\data\ImportFile.xls"
Range("A1:AA10&quot.Select
Selection.Copy
ActiveWindow.ActivateNext
Sheets("ImportedData&quot.Select
Range("A1&quot.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks("ImportFile.xls&quot.Close SaveChanges:=False
Application.DisplayAlerts = True
Sheets("CustomerQuote&quot.Select
Range("A1&quot.Select
Calculate
Application.ScreenUpdating = True
End Sub

I hope this "gets you started".

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[jennuhw]

Now will this work if I need a new copy for each import? I need the data imported into certain cells not changing others. The document that needs edited is a quote form for a customer. We save these under item numbers and use the template. So, another words, I need certain info pulled into the spreadsheet, most to stay the same. When a new record shows, I need a new spreadsheet started. I don't think I am explaining this right. Let me know if this makes sense. Thanks!

 

[DaleWatson123321]

Hi jennuhw,

Yes, I believe this approach will work. You should modify your "template" file with the changes I mentioned - i.e. insert a separate sheet called "ImportedData" (or whatever name you want). Once you have modified your formulas on the "quote" sheet to point to the cells in the "ImportedData" sheet, save this file as your (new) template.

After making this change, you should be able to follow the same practice as you described - i.e. that of saving the template file as a separate, unique file ...under "item number".

In reference to your "When a new record shows, I need a new spreadsheet started", this tells me that you need to create a query which will export the data containing (ONLY) this new record to the "ImportFile.xls" - from where it is imported into your "template" file.

Hope this helps

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca