Macro to Generate Documents from Series of Templates Line Error

Question

I am using code user @Alain wrote a while back as the basis for a new script to write data to MS Word templates from an Excel Dashboard. Please review the original post and solution here. I have one compiling error I cannot quite get rid of.

The problem is concerning a line of code in the GenerateDocumentation module, specifically the srcPath string.

I do not recognize FindConstant("") as VBA code. From what I know, findConstant is a Java command. Is there a library in Tools>References I am forgetting to select? In fact there seems to be a few subs or functions I don't recognize, e.g. CreateDocGenPath, GetNextEndorsementNumber, et cetera. Are these functions of other libraries, or custom subs?

I should be able to rewrite this line as srcPath = Application.GetSaveAsFilename and get a dialog box to select the directory... will this work?

You are likely missing some methods from the original code - these may not have been included in the post. Alain mentions it is 50k lines of code altogether. That's **far** more than he posted. — Tim Williams, Mar 19 '13 at 20:26
Yes, I noticed the immensity of the project. I was sincerly hoping someone would recognize the likely-user defined items as functions in external libraries to minimize the coding I would have to do... — endowdly, Mar 19 '13 at 20:29

score 1 · Answer 1 · edited Jul 25 '14 at 10:03

I believe I've created a function that likely replicates User @Alain's custom defined function, FindConstants("Message Here"). The trick to getting VBA to call a directory path is to run through the Windows API. This function calls the file DLL to pull up a directory browser. It then saves the selection to DirPath(). I'm not sure how to call these declarations on a 64 bit machine, and this is assuming you're running Windows 7. Here's what I have so far:

Option Explicit
Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type


'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) _
  As Long


Declare Function SHBrowseForFolder Lib "shell32.dll" _
    Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Function DirPath(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer
     bInfo.pidlRoot = 0&  

'   Window title information   
 If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
   Else
       bInfo.lpszTitle = Msg
   End If

'   Directory information to return
   bInfo.ulFlags = &H1

'   Display the browsing dialog
   x = SHBrowseForFolder(bInfo)

'   Parse
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
    pos = InStr(path, Chr$(0))
    DirPath = Left(path, pos - 1)
Else
    DirPath = "" ' Saves no directory if nothing is selected!
End If
End Function

Here's a Sub to test the function above:

Sub Test()
Dim Msg As String
Msg = "Please select a template repository."
MsgBox GetDirectory(Msg)
End Sub

Sorry that's a little messy, I dunno how to clean up code blocks with Markdown... this isn't a complete answer and took me a while to get to because I'm not familiar with calling through the API. Plus, the MSDN is written for C+ in mind, which I do not know, so I had to find some tabled resources that equate C+ declarations with VBA ones (As Long vs. *FAR, et cetera). I'll keep the question open in case anyone can contribute or identify a function they spot in Alain's code as a add-on or as a well-known UDF. I'll edit this answer with more as I create and learn.

score 0 · Answer 2 · answered Mar 19 '13 at 20:20

0

I think FindConstant() is a function Alain wrote to return the path from a list of settings somewhere, but I don't see that SrcPath is used anywhere. The other routines you mention are also user-supplied.

answered Mar 19 '13 at 20:20

grahamj42

2,752
3
25
34

I was afraid that would be the case. & `srcPath` is a declaration in the `GenerateDocumentation` module. He Dimd `srcPath` as a string. – endowdly Mar 19 '13 at 20:51
So perhaps you could tell us a bit more about *your* problem (by editing your question) and perhaps someone can suggest something simpler (maybe me, but others might get there first!) – grahamj42 Mar 20 '13 at 21:48
Mmm... I believe the general answer to my question is those functions and subs are custom and user-defined. I was hoping someone might recognize them as an external library function, hence why I haven't chosen this as the answer, though it is _nearly_ complete one. I understand I simply have to derive the function of Alain's custom subs and functions and just write my own function block from scratch. This is something I am in the process of albeit slowly. – endowdly Mar 25 '13 at 20:23
Incomplete question ... incomplete answer. I hope you solve your problem. – grahamj42 Mar 25 '13 at 20:25
I believe that is a wee unfair. The question is not incomplete and asked an accurate direct question ("What are these functions; where can I find them?". The ans is not incomplete either. It is, however, only an assumption as I see it. Someone may recognize those funtions as an external function in a library, OR the original creator (Alain) can further clarify. And thank you, I believe with time I will. I suppose I could clarify and list all the functions I am unsure of in his code, and perhaps other users could brainstorm their purpose; but that might be too much of a forum for SO? – endowdly Mar 25 '13 at 20:47
I'm sorry if I caused any offence, it was certainly not meant that way. – grahamj42 Mar 25 '13 at 20:58
No worries @grahamj42, none was taken. – endowdly Mar 28 '13 at 21:39

Macro to Generate Documents from Series of Templates Line Error

2 Answers2