Clueless!!

[knice11]

Hello!

I need help creating a form in access. The form needs to have a button that can be pressed to export to excel # of units received by fiscal week whih comes from two different sheets in excel. One sheet is the master dates which has all of the fiscal weeks in it and the other is the PO Status report which has the #of units ordered.

 

[VilaRestal]

If you're new to Access then I guess new to VBA programming.

In which case I'd advise the easiest way is to import all the data into access tables (and refine them a bit) and redesign the spreadsheets to link to tables and queries in the database.

The other way - exporting from Access into a spreadsheet - would require quite a lot of programming.

The other advantage to the first way is that you wouldn't need buttons doing these things. The spreadsheet will refresh itself each time it's opened (perhaps a button in Access just to open the spreadsheet).

Redesigning the sheet would be fiddly and Excel will make it as awkward as it can but it wouldn't take anywhere near as long as writing all the code to do the exporting.

 

[knice11]

I have had programming in vb not vba. Don't ask. My professors deemed it unnecessary to teach us vba. I have some programming knowledge in my head but just need to be refreshed. I don't have time for the refreshment and I don't want you to spend a lot of time trying to help with that. Now when you say link to table and queries are these ones I am creating or ones that access will create if I start the link process?

 

[VilaRestal]

Step 1: Import the data from the spreadsheets into Access: In Access > Get External Data > From Excel > Select the workbook > Select the worksheet > etc > etc). Only import uncalculated fields. The result will be a table in Access. You might need to do some work on the table after.

Step 2: Modify the spreadsheet (a copy of the spreadsheet) so that those fields (columns) that were imported are now a table (an Excel table) that links to the table in Access: In Excel > Data > Access > Browse to the database > Link (rather than Import) > Select the table > Set the connection options to Read only (you don't want people entering and editing data in Excel in my opinion) and voila. Except excel will probably make you redo some formulas and chart ranges. It would be wise to move all the columns that the fields represent on the spreadsheet together and then import over them. Hopefully excel wouldn't mess up any formulas that way.

How tricky it will be depends how complex the spreadsheet is. But that's true of writing code to export to it and this way will always be quicker and easier.