MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 272|Reply: 5

Pretty printing VBA code

[Copy link]

6

Topics

40

Posts

100

Integral

Conqueror

Rank: 3Rank: 3

Integral
100
Post on 9-14-2016 18:33:01 | All posts |Read mode
For some time I have wanted to create a utility (CodePrint) that would print the code in a given module (or modules) in a useful format (with index, line numbers, etc). So as part of defining the requirements and user interface, I have a requirement to be able to open any Office document (SourceDoc), and offer the code modules that are in it,from which I can select one or many, for printing.
The selected modules are then copied to a separate Word document (TargetDoc) where the formatting is carried out. This leads to a potential problem if the selected SourceDoc is in fact a previous TargetDoc! Obviously in this case there will be no code modules associated with TargetDoc, but I want to find a way of learning that this is a TargetDoc. Hence an earlier post about CustomProperties. The idea is to create for the SourceDoc a set of CustomProperties that would include the Version of CodePrint, the date of the CodePrint run, and the name of the TargetFile created. The Target file would have a similar set of properties, plus an indication that this is an output file, and so should not be used as a source file.
OK - this research is under way - and already it seems that the various Office Apps have different ways of dealing with Document Properties. Well - consistency is perhaps not Microsoft's strongest point. The second area where I'm running into problems is getting access to the VBE. Before I go too far down this path (which I have trodden once before, but some time ago), I would be interested in learning if the VBE is common and consistent across all the Office apps, or whether this is yet another area where different apps have different approaches.
Of course - someone may already have done this (if so, a pointer would be helpful), which would make this exercise of limited interest except as a research project.
So, bottom line, I have two questions:
1)  Is there any good source for learning how the different Office apps can handle a common way of dealing with Custom Properties, and if not, what is the best way of adding these few properties to an Office file?
2)  Are there any known inconsistencies between the VBE for different Office apps? I'm assuming (dangerous..) that the structure of the VBE is pretty consistent over different versions, but how about between applications?
Any pointers will be gratefully welcomed!
Tony




Reply

Props Report

2

Topics

188

Posts

466

Integral

Conqueror

Rank: 3Rank: 3

Integral
466
Post on 9-14-2016 19:46:43 | All posts



The following code shows how you might identify and open a target document, then select and process a folder full of source documents without the risk of re-opening the target document.
Sub Demo()
Application.ScreenUpdating = False
Dim DocSrc As Document, DocTgt As Document
Dim strFolder As String, strFile As String, strDocNm As String
'Choose a target document
With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
  .Title = Select the target file
  .AllowMultiSelect = False
  If .Show = -1 Then
    Set DocTgt = Documents.Open(.SelectedItems(1), ReadOnly:=False, AddToRecentFiles:=True)
  Else
    MsgBox No target file selected. Exiting, vbExclamation
    Exit Sub
  End If
End With
strDocNm = DocTgt.FullName
'Choose a folder of source documents
strFolder = GetFolder
If strFolder =  Then Exit Sub
strFile = Dir(strFolder  \*.doc, vbNormal)
While strFile  
  'Target document is open and not to be processed
  If strFolder  \  strFile  strDocNm Then
    Set DocSrc = Documents.Open(FileName:=strFolder  \  strFile, _
      AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
    With DocSrc
      'process the source document
      '
      With DocTgt
        'update the target document
        '
      End With
      .Close SaveChanges:=False
    End With
  End If
  strFile = Dir()
Wend
With DocTgt
  'process the target document
  '
End With
Set DocSrc = Nothing: Set DocTgt = Nothing
Application.ScreenUpdating = True
End Sub
Function GetFolder() As String
Dim oFolder As Object
GetFolder =
Set oFolder = CreateObject(Shell.Application).BrowseForFolder(0, Choose a folder, 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function











Reply Support Opposition

Props Report

6

Topics

40

Posts

100

Integral

Conqueror

Rank: 3Rank: 3

Integral
100
 Author| Post on 9-14-2016 20:23:22 | All posts



Thanks, Paul, for the quick response. Here is the procedure I'm using to take a filename, and (try to) get access to the VBE.
Most of the variables are defined globally to the form module (which is used for getting the source file name, and displaying the list of code modules that are available for any given SourceFile.
I'm doing initial work using Excel, because that's the environment with which I'm most familiar. (The actual CodePrint work is, naturally, in Word).
Sub LoadSourceFileIntoForm()
'   Take the public variable SourfeFileName, and split it into path, filename and application components
'   Put the name components back into the form, and then gain access to the source file, and get a list
'   of the available modules in the VBE
Dim Status As Long
Dim AppTest As String
Dim SourceVBE As vbe                    '   Is the structure of the VBE common to all Office apps?
TraceProc LoadSourceFileIntoForm, SourceFileFullName

'   Parse out the FullFileName into fomponents. AppName is the extension, which is used to
'   determine the Office App that created the document
Status = SDLUtilities.SDLParseFileName(SourceFileFullName, SourceFilePath, SourceFileName, SourceFileAppName)
'   Put values back into the form, and get ready to load a list of modules
Me.txtSourcePath = SourceFilePath
Me.txtSourceFileName = SourceFileName  .  SourceFileAppName
'   Translate the file type into the App Name
'   The different applications use slightly different ways of opening the source file (Boo Hiss)
Set SourceApp = Nothing
AppTest = Left(SourceFileAppName, 2)
If AppTest = md Or AppTest = ac Then
   SourceFileAppName = Access
   Set SourceApp = New Access.Application
ElseIf AppTest = xl Then
   SourceFileAppName = Excel
   Set SourceApp = New Excel.Application
   SourceApp.Workbooks.Open SourceFileFullName
   Set SourceVBE = SourceApp.ActiveWorkbook.vbe
ElseIf AppTest = pp Then
   SourceFileAppName = PowerPoint
   Set SourceApp = New PowerPoint.Application
ElseIf AppTest = do Then
   SourceFileAppName = Word
   Set SourceApp = New Word.Application
   
'  Consider Outlook and Publisher for future incorporation?
Else
End If
If SourceApp Is Nothing Then
Else
End If
Me.lblSourceApp.Caption = SourceFileAppName
SourceApp.Quit                                '   Is this a consistent feature across apps? Works for Excel!
Set SourceApp = Nothing
End SubThe line that causes problems at the moment is:
   Set SourceVBE = SourceApp.ActiveWorkbook.vbe
The message is that Object doesn't support this property or method.
Questions:
1)  Is the VBE under the application, or under the open document? (I may have answered this, because I seem to recall that when I have two excel workbooks open at the same time, that their modules, etc get scrambled up in one interface. Leading me to the supposition that the VBE resides in the Application, not the WOrkbook. Good to have this validated somehow!)
2)  A slightly different approach gave an error 1004, along with the cryptic message Access to VBE is not trusted. Any idea what this means?
Thanks for your patience,
Tony




Reply Support Opposition

Props Report

2

Topics

188

Posts

466

Integral

Conqueror

Rank: 3Rank: 3

Integral
466
Post on 9-14-2016 20:56:08 | All posts



To extract the code modules, instead of:
Set SourceVBE = SourceApp.ActiveWorkbook.vbe
you might use something like:
Sub ExportCode(VBSource As Object, StrSrcName As String)
'Note: Trusted access to the VBA project object model is required to use this code.
'See under File|Options|Trust Centre|Trust Centre Settings|Macro Settings
Dim VBColn As Collection, VBComp
With VBSource.VBProject
  Set VBColn = .VBComponents
    For Each VBComp In VBColn
      VBComp.Export Split(StrSrcName, .)(0)  _  VBComp.Name
  End With
Next VBComp
Set VBColn = Nothing
End Subwhere you pass both the file to be processed and its full name to the sub.
As for your questions, I wonder if you're really up to a project of this nature if you have to ask them or questions like those in your previous post. The VBE is an application-level environment and nothing is any more 'scrambled' than your workbooks might be. Just as you can have multiple workbooks open, each with multiple worksheets, so to can the VBE have multiple projects open. It's up to you to pay attention to which one you're working in. As for the 'cryptic message' that's because programmatic access to code modules requires trusted access to the VBA project object model. See comments in the code.
Reply Support Opposition

Props Report

2

Topics

804

Posts

1720

Integral

King

Rank: 6Rank: 6

Integral
1720
Post on 12-22-2016 10:45:43 | All posts



The answer is yes
But I am not sure even if you need VBA
Can you:
- explain how the statistics will be updating - are they different ranges in the workbook? or is the workbook itself being updated (eg from internet)?
Also
- please amend the thread Title into something more meaningful
(think of someone doing a Google Search - VBA Code would bring up a few million results)
thanks




Reply Support Opposition

Props Report

6

Topics

473

Posts

1104

Integral

King

Rank: 6Rank: 6

Integral
1104
Post on 12-22-2016 12:25:28 | All posts



Your post does not comply with Rule 1 of our Forum
RULES
. Your post title should accurately and concisely describe your problem, not your anticipated solution.
Use terms appropriate to a Google search. Poor thread titles, like
Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Reply Support Opposition

Props Report

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 9-24-2017 21:23 , Processed in 0.278085 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!