Solved Access Query to Existing Formatted Excel Spreadsheet (1 Viewer)

[allen675]

Helloooooooooo,

The title pretty much says all! I have an Excel Spreadsheet (SS) that the directors have put together in a format they are happy with (please see attached). Lets say I have a table with all the information needed for the SS plus a few little extras that I have added PLUS a Query which only contains the information required for the SS. I am fairly new to VBA so not something I feel I can write myself. Essentially I need the code to get the info from the Query to the SS. Please assume that all fields in the DB are the same as the SS. I plan to store the SS under the DB i.e. in the DB & SS in same folder.

Thanks in advance

 

[June7]

Review http://accessmvp.com/KDSnell/EXCEL_Export.htm

 

[allen675]

Review http://accessmvp.com/KDSnell/EXCEL_Export.htm

Thanks for your prompt response.

I've read through the website you sent and to be honest I really don't know what option applies. Although I am happy to experiment with pre written code I really would like my hand held on this one and what would be really useful is if I could see an example/code that relates to the spreadsheet uploaded.

Regards

 

[allen675]

Thanks for your prompt response.

I've read through the website you sent and to be honest I really don't know what option applies. Although I am happy to experiment with pre written code I really would like my hand held on this one and what would be really useful is if I could see an example/code that relates to the spreadsheet uploaded.

Regards

Scrap that might have just missed it, I'm going to fourth option on that link. I'll let you know how I get on

 

[allen675]

Brilliant thank you @June7 that worked a treat. Now a couple of questions if I may please?

Is there anyway to reference the current project file path for this line rather than including a file path. Ultimately this DB will be distributed to the team and I need a universal place for the SS to live i.e. as per my first post?

Set xlw = xlx.Workbooks.Open("C:\Users\David\Desktop\Unbiased ADB\UnbiasedTracker")

Also would it be possible for the code to then take the update workbook and attach it to an email to then send to the directors?

Regards,

David

 

[bastanu]

Use CurrentProject.Path & "\" to get the path to the running VBA project.

 

[allen675]

Use CurrentProject.Path & "\" to get the path to the running VBA project.

@bastanu thank you That took care of that part. Just to add the send as email attachment part now

 

[Gasman]

That topic add attachments has been asked many times here. Search for it, or look at Similar threads at the bottom of your post for that issue, so make a new post?