Using excel macro from access.

[Homegrownandy]

Bit of a long winded process but its the only way I could get this working.

• I have code that will generate a word document for me, using data within access.
• I have code that will insert new lines to a table from excel to word using a linked access table.

I cant insert into a table directly from access as it has formatting issues im unable to resolve.

So what I want is to have some code in access that will open the excel file and run the macro using the file name that I have just created.

Im happy to have a go at this, im sure ill have problems but ill get it somewhere near working.

Just after some advice/thoughts before I start working on this. Is this bad practice? are there better alternatives?



I'd be looking initially at this post here for some guidance:

https://stackoverflow.com/questions/...m-command-line

Im not comortable with using the newly generated file name as a variable either from excel.

So in access i have this:

' New File Name
sNewFile = "RAM_" & Me.Site_Name & "_" & Me.Site_ID & "_" & Me.RAMS_ISSUE & ".docx"

Is there a way I can use this variable in excel (from access).

feel free to ask questions if i haven't been clear.

Thanks.

[Minty]

Daft question but (and I know Access reports are a bit limited/ fiddly to work with) why does it has to be a word doc, couldn't an Access report create the document?

[Homegrownandy]

It's a risk assesement document, what this is doing here is putting in all the location information and "preparer" information automatically into one of the templates.

Then we need to put into a table the associated risks and how we deal with them. So, ive made it easy wehre the user selects the type of works (can be multiple) and this will be available in the excel table for pasting over.

Its possible to do from an access report but not everyone here has access. Doing it this way allows for people to make their own templates for similar works. (if that makes sense).

Cheers, Andy.

[Homegrownandy]

Just incase this is usefull to someone (can be found elsewhere online too):

Dim xl As Object
 
'Step 1:  Start Excel, then open the target workbook.
   Set xl = CreateObject("Excel.Application")
    xl.Workbooks.Open ("\\SERVER\general\Documents\!Management\VBA_Templates_do_not_modify\HAZARDS.xlsm")
 
'Step 2:  Make Excel visible
   xl.Visible = True
 
'Step 3:  Run the target macro
   xl.Run "ThisWorkbook.ExcelDataToWord"
 
'Step 4:  Close and save the workbook, then close Excel
   xl.ActiveWorkbook.Close (True)
    xl.Quit
 
'Step 5:  Memory Clean up.
   Set xl = Nothing

[Minty]

Just to mention that you can download the Access runtime for free, and that will let your Access application be run by anyone.
(Assuming they have an office install of some sorts for the excel / word automation)

[Homegrownandy]

Hi Minty,

Everyone has access runtime, they are just not able to create templates for reports. They are able to create templates in word. The "template" for the report will be unique for each project.

If we were to use access reports we wouldnt have the flexability to make new templates quickly (especially if i wasn't in one day). Depending on what the customer wants the document can be completly different.

(although most of them can/will be different they all have: Risks, Location information, preparer information)

Thanks.

[Minty]

Got it, I now understand the leaping through a number of hoops approach.

[Homegrownandy]

This is part of the code that generates the new file.

' New File Name
sNewFile = "RAM_" & Me.Site_Name & "_" & Me.Site_ID & "_" & Me.RAMS_ISSUE & ".docx"

'destination folder path
sDFolder = "\\SERVER\general\RAMS\RAM_RAMS\" & sNewFile

I believe what i need to do is store sNewFile as a global variable. Then call that in excel to run the code from there?

(never used global variables not even sure if what im saying is right)

Is that the right way to do this? If so ill find something online and have a go.

Cheers.