-1

I'm looking to understand how to accomplish a task I've been set at work where i have quite an odd situation in terms of the data being entered and how that is maintained over the year.

Scenario: I have an Office 2013 Access Database (stored in a network shared drive) which contains several tables (example names below)

  • table1MD (master data)
  • table2 (lookup table)
  • table3 (lookup table)
  • table4 (lookup table)
  • table5 (lookup table) etc...

Every quarter i need to upload data into table1MD and would like to ensure the data going into the table is consistent with the lookup tables in the database.

My plan: To construct an excel workbook that you can get your new quarterly data ready in, pull in the access database table info then merge this altogether ready to be uploaded into the database.

My Question to Stack Overflow users: Using Excel VBA, what is the best way to download in a single click all my database tables into its respective Worksheet.

i.e. table1MD (from access) would download into the table1MD worksheet and vice versa

At this point i'm not concerned with getting the information back into Access, i'm merely looking for assistance on the best/easiest way to achieve a simple download of a database into Excel so i can use that information.

My knowledge with VBA is extremely new, I come from a HTML/CSS background so picking things up as i go along and i can't find anything that works hence i'm reaching out to Stack Overflow users.

Please let me know if there's anything else you need from me.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Blakey
  • 1

1 Answers1

0

I don't think so You need to use VBA for it. There Excel solution to get data from Access to Excel. Look for Data Sources for Excel.

Gadziu
  • 657
  • 7
  • 21
  • Hi Gadziu, I know Excel has data connections, but i want to do it all via VBA to remove any user interaction required. This should be a 'open the template and hit the 'refresh tables' button. I do not want the user to have any input over what happens, it all needs to be pre-determined via VBA – Blakey Jan 03 '17 at 10:51
  • So you need to add a button to excel sheet (Developer->Controls->Insert->FormControls->Button), asing new macro. Code in macro is `ActiveWorkbook.RefreshAll` – Gadziu Jan 03 '17 at 10:54
  • I can create a button in Excel, thats simple. What i'm looking for is assistance with VBA code on how i can achieve my goal. – Blakey Jan 03 '17 at 10:54
  • ActiveWorkbook.RefreshAll would be using data connections in the Excel file correct? I do not want to do it via this method Gadziu. I need help to write VBA to import access table data into my excel worksheet – Blakey Jan 03 '17 at 11:01
  • But what for??? You have complete, built in Excel solutions to get data from Access. Any other custom solution would be much more less efficient. But you can look here http://stackoverflow.com/questions/9083232/writing-excel-vba-to-receive-data-from-access – Gadziu Jan 03 '17 at 11:04
  • I'm toying with the data connection refreshes now, its not what i originally had in mind but if it works... – Blakey Jan 03 '17 at 11:22