Using VARs to Populate a Word Document form...

[Rathgar2]

What is the best way to use VAR's that have been collected by a RegExMatch to fill out a form in MS Word and MS Excel? In the below thread I have (with the patient help of kon and sinkfaze) written a nifty script that allows me to scrape data from my company's' web CRM and to store it away to a set of Var's. These in turn are loaded into the clipboard for pasting and several hotkeys for selectively pasting out these data points.

https://autohotkey.com/boards/viewtopic.php?f=5&t=12941

The next thing I need to do with them is to generate a printable form (I work for lawyers and they lurve the paper) with all the data plugged in already. With the above script I can do this step by hand and already save time and increase accuracy but if the task can be further automated then that would be swell. So any ideas?

I have marked the spots where I'd like to populate the RegExMatched Data. One of the forms looks like this:



And another looks like this:



Thanx in advance.

[kon]

AHK programs can use COM to programmatically communicate with MS office programs.
This is an example of how to set the value in an Excel cell.

Code: Select all

MyVar := "Hello World!"
WorkbookPath := A_ScriptDir "\MyWorkbook.xlsx"
xlApp := ComObjCreate("Excel.Application") ; Create an instance of Excel
xlApp.Visible := true ; Make Excel visible
MyWorkbook := xlApp.Workbooks.Open(WorkbookPath) ; Open a workbook and store a reference to it in MyWorkbook var
MyWorkbook.Sheets("Sheet1").Range("A1").Value := MyVar

For more info see MS Office COM Basics <- There are links at the bottom to the basic Excel tutorial and the COM object reference pages too.

Word might be a bit more difficult than Excel (but still doable), since in Excel each cell has a specific row and column. Word on the other hand is not organized that way so navigating the various paragraphs can be less straightforward. You could make a Word template and put a placeholder word in it where you want to insert your values. Then do a find-and-replace to insert your values.

[Rathgar2]

kon once again you are a true gentleman and a scholar. When I grow up I wanna be like kon. Since my name is Ken I only have to change 1 letter. I shall study your code and the MS Office COM Basics. Later after I have progressed I'll post my code in hopes it helps others.

[Rathgar2]

kon, I have reviewed your guide and several of your archived posts on the Excel Save As process and got my code to populate an Excel document properly. This post of yours was very helpful:

//autohotkey.com/board/topic/100823-save-excel-document-using-com/?p=630464

Here is my code:

Code: Select all

; CRTL+SHIFT+g Object COM Tester
^+g::
KeyWait, g
WorkbookPath := A_ScriptDir "\!!VIEWER work order (Template).xlsx"
objWorkBook := objExcel.Workbooks.Add
xlApp := ComObjCreate("Excel.Application") ; Create an instance of Excel
xlApp.Visible := true ; Make Excel visible
VideoViewerWorkOrder := xlApp.Workbooks.Open(WorkbookPath) ; Open a workbook and store a reference to it in VideoViewerWorkOrder var
VideoViewerWorkOrder.Sheets("Viewer Worksheet").Range("A1").Value := "VIDEO VIEWER"
VideoViewerWorkOrder.Sheets("Viewer Worksheet").Range("C4").Value := _DLinkJobNumber
VideoViewerWorkOrder.Sheets("Viewer Worksheet").Range("I4").Value := DLinkJobDate1
VideoViewerWorkOrder.Sheets("Viewer Worksheet").Range("C5").Value := DLinkCaseCaption1
VideoViewerWorkOrder.Sheets("Viewer Worksheet").Range("C6").Value := DLinkDeponentInformation15 " " DLinkDeponentInformation17
VideoViewerWorkOrder.Sheets("Viewer Worksheet").Range("C10").Value := DLinkLawFirm1AccountName2
VideoViewerWorkOrder.Sheets("Viewer Worksheet").Range("C11").Value := DLinkLawFirm1bAccountAddress3
VideoViewerWorkOrder.Sheets("Viewer Worksheet").Range("C12").Value := DLinkLawFirm1CityAccountAddress3 ", "DLinkLawFirm1StateAccountAddress3 " "DLinkLawFirm1ZipCodeAccountAddress3
VideoViewerWorkOrder.Sheets("Viewer Worksheet").Range("C13").Value := DLinkNoticingAttorney1
VideoViewerWorkOrder.Sheets("Viewer Worksheet").Range("C14").Value := DLinkAccountPhone1

WorkbookPathSave := A_ScriptDir "\" _DLinkJobNumber " Video Viewer.xlsx"
MsgBox % WorkbookPathSave
xlApp.ActiveWorkbook.SaveAs(WorkbookPathSave)
;not closing the Excel document to allow for tweaking and printing.

Return

Thanx again for your input!

[kon]

You're welcome. Glad to hear it's working Nice job.

[kon]

Just noticed a minor thing; I think you copied this line from the forum post you linked to, but it is not doing anything in your script:
objWorkBook := objExcel.Workbooks.Add

And, just an option to consider; I have a script similar to this, and it prints a pdf to the same folder it saves the workbook to. It would look something like this:

Code: Select all

MySheet := VideoViewerWorkOrder.Sheets("Viewer Worksheet")
MySheet.Range("A1").Value := "VIDEO VIEWER"
;...
PdfPathSave := A_ScriptDir "\" _DLinkJobNumber " Video Viewer.pdf"

; Not using OpenAfterPublish so it was removed.
MySheet.Range("A1:G20").ExportAsFixedFormat(0 ; xlTypePDF = 0
        , PdfPathSave, 0, false, false)    ;,,, OpenAfterPublish:=true) ; xlQualityStandard = 0

See Range.ExportAsFixedFormat for more info.

[Rathgar2]

You are right I copied it and did not at first have the script working and when it finally did I did not want to upset the cart...so to speak. Will take a look at doing the PDF, but I think our workflow will need to have manual tweaks before it finally needs to be saved. The Save As will ensure that the proper naming convention will be followed. I have a co-worker who is sloppy about this step. Thanks again!

[Jovannb]

HI,

I'm doing what you would like too, by

a) setting textmarks/bookmarks in that winword-document template

b) open that template via AHK

c) jump to that textmarks/bookmarks

d) put the AHK-VAR-Text there

thats it.

Here is a small excerpt of my code

Code: Select all

ww_update_bookmark(ww_document_object, bm_name, bm_content)
{
	bmark:=ww_document_object.bookmarks.item(bm_name).Range   ;  get access to that textmarks
	bmark.text:=bm_content								; set new (VAR) content of that textmark
	ww_document_object.bookmarks.add(bm_name, bmark)		; set/re-set that former textmark again, otherwise there is no longer a textmark}

Function-Parameters are:
"ww_document_object", selfexplaining is a COM-WindWord-Object
"bm_name", winword-bookmark-name (bookmark=textmark)
"bm_content", new bookmark/textmark content - your variable value which you would like to put on a certain place in a winword document

Maybe it helps you a little bit

regards
J.M.

[Rathgar2]

Thanx Jovannb, this will help. The next form for me to work on will be a Word Doc. At the rate I am going with general workload it may be about two weeks before I can tackle it.