MartyConnelly
martyconnelly at shaw.ca
Mon Nov 8 13:26:36 CST 2004
It gets even more complex from .dotnet framework calling Com via PIA
with Word 2003 and Excel.2003
You might only get away with this, using Application Objects, available
in most MS object models.
For example this code should be callable from any VBA editor Word,
Excel, Access or PowerPoint
Instead of InternetExplorer.application below you could use
Word.Application etc
Try this code for a chuckle useful if you have to display large amount
of scrollable text in Access.
This will need WMI installed on the machine, either WinXP or win2000 for
the WMI portion of
example to run. Win 98 can install WMI 1.5 but may not work
Sub testIE()
'------------------
' qfcheck.vbs ver 1.0 3-Dec-2003
' Check Automatic Update Installation History for Hotfixes
'------------------
'http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/scriptcenter/logs/scrlog08.asp
Dim objExplorer As Object
Dim objDocument As Object
Dim strComputer As String
Dim objWMIService As Object
Dim colLoggedEvents As Object
Dim objEvent As Object
Dim dtmDate As Variant
Dim strReturn As String
Set objExplorer = CreateObject("InternetExplorer.Application")
objExplorer.Navigate "about:blank" 'need this to open IE correctly or
use url
objExplorer.Toolbar = 0 'set to 1 to turn on IE toolbar
objExplorer.StatusBar = 0 'set to 1 to turn on
objExplorer.Width = 800
objExplorer.Height = 570
objExplorer.Left = 0
objExplorer.Top = 0
objExplorer.Visible = 1
Do While (objExplorer.Busy)
Loop
Set objDocument = objExplorer.Document
objDocument.Open
objDocument.Writeln "<html><head><title>Automatic Updates Installation
History</title></head>"
objDocument.Writeln "<body bgcolor='white'>"
objDocument.Writeln "<table width='100%'>"
objDocument.Writeln "<tr>"
objDocument.Writeln "<td width='20%'><b>Computer Name</b></td>"
objDocument.Writeln "<td width='50%'><b>Installed Update(s)</b></td>"
objDocument.Writeln "<td width='50%'><b>Date and Time Installed</b></td>"
objDocument.Writeln "</tr>"
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
'IE Event code=19 WinXP=4377
Set colLoggedEvents = objWMIService.ExecQuery _
("SELECT * FROM Win32_NTLogEvent WHERE Logfile = 'System' AND " _
& "EventCode = '19' OR EventCode = '4377'")
Dim i As Long
For Each objEvent In colLoggedEvents
dtmDate = objEvent.TimeWritten
strReturn = WMIDateStringTodate(dtmDate)
objDocument.Writeln "<tr>"
objDocument.Writeln "<td width='20%'>" & objEvent.ComputerName
& "</td>"
objDocument.Writeln "<td width='50%'>" & objEvent.Message & "</td>"
objDocument.Writeln "<td width='50%'>" & strReturn & "</td>"
objDocument.Writeln "</tr>"
i = i + 1
Next
Debug.Print "no of events=" & i
objDocument.Writeln "</table>"
objDocument.Writeln "</body></html>"
'objDocument.Write()
objDocument.Close
MsgBox "finished"
Set objExplorer = Nothing
Set objDocument = Nothing
Set objWMIService = Nothing
Set colLoggedEvents = Nothing
Set objEvent = Nothing
End Sub
Function WMIDateStringTodate(dtmDate)
'sorts out odd date structure
WMIDateStringTodate = CDate(Mid(dtmDate, 5, 2) & "/" & _
Mid(dtmDate, 7, 2) & "/" & Left(dtmDate, 4) _
& " " & Mid(dtmDate, 9, 2) & ":" & _
Mid(dtmDate, 11, 2) & ":" & Mid(dtmDate, _
13, 2))
End Function
John W. Colby wrote:
>I am proposing a book to a publisher I just finished writing for, which will
>be about reusable code for Microsoft Access specifically, modules, classes,
>libraries and frameworks.
>
>I was hoping to find a way to make the code accessible to different
>platforms should someone be more comfortable working in Word or Excel.
>While a com object would certainly accomplish this, it involves tools (and
>expertise) outside of the office suite.
>
>John W. Colby
>www.ColbyConsulting.com
>
>Contribute your unused CPU cycles to a good cause:
>http://folding.stanford.edu/
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Michael R Mattys
>Sent: Sunday, November 07, 2004 11:09 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Modules and libraries in Office
>
>
>Maybe I'm misunderstanding, but ...
>
>(From Q230225)
>COM add-ins are a new feature in Microsoft Office 2000
>and are supported by all Office programs, including Outlook.
>
>Key benefits of COM add-ins include:
>
>COM add-ins run in-process with the host program, so custom code generally
>runs faster than code implemented in Visual Basic for Applications.
>
>The basic COM add-in architecture is consistent across all Office programs,
>whereas in previous versions of Office, each program had its own add-in
>architecture.
>
>You can create one COM add-in to use with more than one Office program.
>
>Example:
>
>http://support.microsoft.com/kb/238228
>
>----
>
>Michael R. Mattys
>Mattys MapLib for Microsoft MapPoint http://www.mattysconsulting.com
>
>
>
>----- Original Message -----
>From: "John W. Colby" <jwcolby at colbyconsulting.com>
>To: "'Access Developers discussion and problem solving'"
><accessd at databaseadvisors.com>
>Sent: Sunday, November 07, 2004 10:46 PM
>Subject: RE: [AccessD] Modules and libraries in Office
>
>
>
>
>>No, I'm talking about an application framework where the code is
>>available to any Office application.
>>
>>John W. Colby
>>www.ColbyConsulting.com
>>
>>Contribute your unused CPU cycles to a good cause:
>>http://folding.stanford.edu/
>>
>>-----Original Message-----
>>From: accessd-bounces at databaseadvisors.com
>>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Michael R
>>
>>
>Mattys
>
>
>>Sent: Sunday, November 07, 2004 10:16 PM
>>To: Access Developers discussion and problem solving
>>Subject: Re: [AccessD] Modules and libraries in Office
>>
>>
>>John,
>>
>>Are you talking about the designers?
>>
>>I thought you could have a common standard or class module within the
>>dll, and just pass in the Application object as Object. Then just
>>Select Case
>>
>>
>to
>
>
>>perform the appropriate action. I'll look again, but I'm pretty sure
>>
>>
>that's
>
>
>>it.
>>
>>----
>>
>>Michael R. Mattys
>>Mattys MapLib for Microsoft MapPoint http://www.mattysconsulting.com
>>
>>
>>
>>----- Original Message -----
>>From: "John W. Colby" <jwcolby at colbyconsulting.com>
>>To: "'Access Developers discussion and problem solving'"
>><accessd at databaseadvisors.com>
>>Sent: Sunday, November 07, 2004 8:36 PM
>>Subject: RE: [AccessD] Modules and libraries in Office
>>
>>
>>
>>
>>>Holy cow, is that a mish mash or what? The point of my questions
>>>was to
>>>
>>>
>>try
>>
>>
>>>to find a common ground where code could be stored and run from all
>>>of the various office applications. Not to be it would seem. It
>>>does seem
>>>
>>>
>>bizarre
>>
>>
>>>that if I had some function that should be able to run in any of the
>>>apps
>>>
>>>
>>I
>>
>>
>>>have to save the same thing in 5 different places so that Access,
>>>Excel, Word, Powerpoint and Outlook could use it.
>>>
>>>John W. Colby
>>>www.ColbyConsulting.com
>>>
>>>Contribute your unused CPU cycles to a good cause:
>>>http://folding.stanford.edu/
>>>
>>>-----Original Message-----
>>>From: accessd-bounces at databaseadvisors.com
>>>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
>>>MartyConnelly
>>>Sent: Sunday, November 07, 2004 2:03 PM
>>>To: Access Developers discussion and problem solving
>>>Subject: Re: [AccessD] Modules and libraries in Office
>>>
>>>
>>>In Excel you can store functions and modules as Public in .xla
>>>files. Old Excel pre 97 didn't store the code in xla it looked like
>>>an mde file. Matter of fact you can call these .xla files from
>>>Access. I have called functions from the Excel Statistical and
>>>Financial Analysis Packs.
>>>
>>>In Outlook 2000 and any code that you create in the Outlook VBA
>>>environment is stored in a file named VBAProject.otm. Even though
>>>you can copy the VBAProject.otm file, that's not a good way to
>>>distribute Outlook macros company-wide. The recommended method is to
>>>create an Outlook or Word COM add-in. Talk to Shamil he has been
>>>upgrading com addins to dotnet. http://www.outlookcode.com/d/vb.htm
>>>http://www.outlookcode.com/d/comaddins.htm
>>>
>>>Word uses templates either user or workgroup (to be shared) in a
>>>specific folder A template contain macros or other customizations
>>>such as toolbars, it works better from some locations than others.
>>>Again these com addin's can be used in place of macro's. see
>>>http://word.mvps.org/FAQs/index.htm
>>>
>>>Just for future reference this method is also partially available
>>>under word and excel
>>>
>>>Under the Tools/References menu in the VBE, you need to check the
>>>
>>>
>>Microsoft
>>
>>
>>>Visual Basic for Applications Extensibility 5.3 object library.
>>>Microsoft Visual Basic for Applications Extensibility 5.3 C:\Program
>>>Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
>>>
>>>
>>>The following code exports the contents of the Code Module to a file
>>>
>>>
>>called
>>
>>
>>>test.bas. This can be run from Word or Excel
>>>
>>>Application.VBE.ActiveVBProject.VBComponents("Module1").Export("test
>>>.b
>>>as")
>>>I think you can also import code this way but never tried it or got it
>>>to work or something.
>>>
>>>John W. Colby wrote:
>>>
>>>
>>>
>>>>In Access we use MDA/Es to store libraries of reusable code. Is
>>>>there an equivalent for Word / Excel / Outlook etc (the rest of
>>>>Office)? Do these other platforms have references like we do in
>>>>Access? If so, can you reference an MDA/E to use code in it?
>>>>
>>>>John W. Colby
>>>>www.ColbyConsulting.com
>>>>
>>>>Contribute your unused CPU cycles to a good cause:
>>>>http://folding.stanford.edu/
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>--
>>>Marty Connelly
>>>Victoria, B.C.
>>>Canada
>>>
>>>
>>>
>>>--
>>>_______________________________________________
>>>AccessD mailing list
>>>AccessD at databaseadvisors.com
>>>http://databaseadvisors.com/mailman/listinfo/accessd
>>>Website: http://www.databaseadvisors.com
>>>
>>>
>>>
>>>--
>>>_______________________________________________
>>>AccessD mailing list
>>>AccessD at databaseadvisors.com
>>>http://databaseadvisors.com/mailman/listinfo/accessd
>>>Website: http://www.databaseadvisors.com
>>>
>>>
>>>
>>--
>>_______________________________________________
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>
>>
>>
>>--
>>_______________________________________________
>>AccessD mailing list
>>AccessD at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/accessd
>>Website: http://www.databaseadvisors.com
>>
>>
>>
>
>
>
--
Marty Connelly
Victoria, B.C.
Canada