Auto-fill excel report or form from access

[davidkassa]

Hi,

I am currently developing a database where I want to be able to export automatically data to a premade form, almost a template in excel.

ie. UNIT table has unitID, MODEL table had Model, OPERATOR table has opID, etc. Each of these need to go to various cells in the excel form, in no particular order.

If more information is needed, let me know.
Thanks in advance for everyone that responds.

David Kassa

 

[Dugantrain]

You can do this from the Excel side or from the Access side, depending on your preference. The first thing that you will need to do is to build a query which will give you the correct results in Access. Then, you can have an Excel worksheet linked to this query and use it as a Datasource: Data, Get External Data, New Database Query, Your Database, Your Query. After correctly setting this up, you can have the worksheet relink to the query and refresh its data every time you open the worksheet simply by selecting Data Range Properties and then Refresh on Open.
From the Access side, you can build a form and then place a command button on that form. Look in the Access help files for the TransferSpreadsheet method. I don't know your experience with VBA, but this particular coding is not too difficult.

 

[davidkassa]

Thanks!

I'll try that! My VBA is pretty good, and I've been seeing more and more of the transferSpreadsheet.

Excellent - seems not too tough. Do you know off hand if there is a way to do that backwards? In other words, import from Excel to Access? (Now, I know I can import, but the problem lies in the fact that the form isn't just a regular datasheet)

Thanks again!

David Kassa

 

[Corticus]

Hi,

I would suggest code as follows:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "YourQuery/Table", Savename, True

Have the template refer to the first sheet in your spreadsheet, the one that will receive data from Access.

Do you know off hand if there is a way to do that backwards? In other words, import from Excel to Access? (Now, I know I can import, but the problem lies in the fact that the form isn't just a regular datasheet)

To export or import from Excel or Access, you data must be in a datasheet form. Access won't let you make it any other way, and Excel won't recognize data in any other form. Now someone's going to post and prove me wrong but we'll see.

I would suggest a 'template' (in Excel) that consists entirely of references. Make the template in datasheet form, and have Acces import that.

HTH,

 

[davidkassa]

Thanks again!

I'll work on that tomorrow and let you know how it goes.

What does HTH in your closing mean?

 

[Corticus]

Hope This Helps!



HTH,