Passing variable to Access from Excel

[NikToo]

Is it even possible to pass a variable from Excel to Access? I have a report which has the source directory for all the files in cell M5, I then need to run a query in Access which needs to pick up this source directory in order to open and save all the files. I thought maybe declaring it as Public would work but somehow I doubt it's that easy. The Access macro is run with a simple .run command.

 

[baitmaster]

Yes it is

You need to create a connection to Access using e.g. ADODB. You then use SQL to pass data to and from Access. You can do this in VBA, but the code will be unfamiliar to you - there's a useful free download at Case studies & example files - Spreadsheet Wizard Ltd which contains all the code you need to connect and read/write data

 

[NikToo]

Yes it is

You need to create a connection to Access using e.g. ADODB. You then use SQL to pass data to and from Access. You can do this in VBA, but the code will be unfamiliar to you - there's a useful free download at Case studies & example files - Spreadsheet Wizard Ltd which contains all the code you need to connect and read/write data

Thanks. I've downloaded it and had a look. I don't fully understand how it works but it looks like it actually writes data into the database. All I need is to pass a variable with a directory to it. Not sure if I can see a method for doing that in the code.

 

[baitmaster]

Sorry yes, looks like I've misread your original question. The example file is for passing data to and from a database

Try this thread for simply calling Access macros from Excel http://www.mrexcel.com/forum/excel-questions/608462-call-access-macro-module-excel.html