Trouble with VBA references

[John Musbach]

Hi, I'm running Office 2002 SP3 and am having trouble with running VBA code
contained in a access database made in Office 2003 SP3. My references are:
http://img339.imageshack.us/img339/9433/referencesneededtk9.gif but the
version numbers do not match the known working references used in the
original Office 2003 SP3 environment:
http://img339.imageshack.us/img339/2307/therefsde1.jpg . A sample of the code
is:

Private Sub Transform(sourceFile, stylesheetFile, resultFile)

Dim source As New MSXML2.DOMDocument30
Dim stylesheet As New MSXML2.DOMDocument30
Dim result As New MSXML2.DOMDocument30

'Dim source As Object
'Dim stylesheet As Object
'Dim result As Object

'Set source = CreateObject("MSXML2.DOMDocument30")
'Set stylesheet = CreateObject("MSXML2.DOMDocument30")
'Set result = CreateObject("MSXML2.DOMDocument30")

' Load data.
source.async = False
source.Load sourceFile

' Load style sheet.
stylesheet.async = False
stylesheet.Load stylesheetFile

If (source.parseError.errorCode <> 0) Then
MsgBox ("Error loading source document: " & source.parseError.reason)
Else
If (stylesheet.parseError.errorCode <> 0) Then
MsgBox ("Error loading stylesheet document: " &
stylesheet.parseError.reason)
Else
' Do the transform.
'MsgBox "Transforming"
source.transformNodeToObject stylesheet, result
result.Save resultFile
End If
End If

End Sub

Private Function KMLaddr()
Close
MsgBox "Please select a location and then specify your desired *.kml
file name"
Dim dlgSaveAs
Dim output As String
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
dlgSaveAs.Show
output = dlgSaveAs.SelectedItems(1)
'Absolute paths are required!
Application.ExportXML acExportQuery, "XMLAddr", "C:\tempexp.xml", 1
Transform "C:\tempexp.xml", "C:\kmladdr.xsl", output
Kill "C:\tempexp.xml"

End Function

Is there a update I can install to get Office 2002 SP3 working with this
code or is the code simply not backwards compatible? If this code is not
backwards compatible, is there another solution for xsl xml transformations
that works with both Office 2003 SP3 and 2002 SP3? Thanks!

 

[John Musbach]

See if this link helps:
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Thanks, but the problem is not finding the broken references. We've found
the needed references and enabled them, the problem is that the reference
versions differ between Office 2002 SP3 and Office 2003 SP3 and this is
causing the code to fail. I'm wondering if there is a patch for this or if
the code as is just isn't backwards compatible? If the code isn't backwards
compatible, is there a alternative way of coding this xml xsl transform
functionality that is compatible with both Office 2002 SP3 and Office 2003
SP3? Thanks!

 

[boblarson]

If you are using this in both versions then you would need to use the
reference from the earliest version for BOTH versions. If the version 5 is
not available in 2003, or if the code takes advantage of anything that is in
version 6 that isn't in version 5 it won't work anyway.
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Michael Gramelspacher]

On Fri, 18 Jul 2008 14:59:00 -0700, John Musbach <John

Hi, I'm running Office 2002 SP3 and am having trouble with running VBA code
contained in a access database made in Office 2003 SP3. My references are:
http://img339.imageshack.us/img339/9433/referencesneededtk9.gif but the
version numbers do not match the known working references used in the
original Office 2003 SP3 environment:
http://img339.imageshack.us/img339/2307/therefsde1.jpg . A sample of the code
is:

Private Sub Transform(sourceFile, stylesheetFile, resultFile)

Dim source As New MSXML2.DOMDocument30
Dim stylesheet As New MSXML2.DOMDocument30
Dim result As New MSXML2.DOMDocument30

'Dim source As Object
'Dim stylesheet As Object
'Dim result As Object

'Set source = CreateObject("MSXML2.DOMDocument30")
'Set stylesheet = CreateObject("MSXML2.DOMDocument30")
'Set result = CreateObject("MSXML2.DOMDocument30")

' Load data.
source.async = False
source.Load sourceFile

' Load style sheet.
stylesheet.async = False
stylesheet.Load stylesheetFile

If (source.parseError.errorCode <> 0) Then
MsgBox ("Error loading source document: " & source.parseError.reason)
Else
If (stylesheet.parseError.errorCode <> 0) Then
MsgBox ("Error loading stylesheet document: " &
stylesheet.parseError.reason)
Else
' Do the transform.
'MsgBox "Transforming"
source.transformNodeToObject stylesheet, result
result.Save resultFile
End If
End If

End Sub

Private Function KMLaddr()
Close
MsgBox "Please select a location and then specify your desired *.kml
file name"
Dim dlgSaveAs
Dim output As String
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
dlgSaveAs.Show
output = dlgSaveAs.SelectedItems(1)
'Absolute paths are required!
Application.ExportXML acExportQuery, "XMLAddr", "C:\tempexp.xml", 1
Transform "C:\tempexp.xml", "C:\kmladdr.xsl", output
Kill "C:\tempexp.xml"

End Function

Is there a update I can install to get Office 2002 SP3 working with this
code or is the code simply not backwards compatible? If this code is not
backwards compatible, is there another solution for xsl xml transformations
that works with both Office 2003 SP3 and 2002 SP3? Thanks!

Your Transform Sub works for me using Access 2003. Maybe change it to

Dim source As New DOMDocument
Dim stylesheet As New DOMDocument
Dim result As New DOMDocument

I know little about XML, so there is not much more I can say.

 

[John Musbach]

Your Transform Sub works for me using Access 2003.  Maybe change it to

Dim source As New DOMDocument
Dim stylesheet As New DOMDocument
Dim result As New DOMDocument

I know little about XML, so there is not much more I can say.

I did some debugging and the troublesome line is:

Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)

...in particular: msoFileDialogSaveAs ...is there a alternative that
works with 2002? Thanks!

 

[Douglas J. Steele]

Try using Late Binding. Don't set any reference, and try:

Private Sub Transform(sourceFile, stylesheetFile, resultFile)

Dim source As Object
Dim stylesheet As Object
Dim result As Object

Set source = CreateObject("MSXML2.DOMDocument30")
Set stylesheet = CreateObject("MSXML2.DOMDocument30")
Set result = CreateObject("MSXML2.DOMDocument30")

' Load data.
source.async = False
source.Load sourceFile

' Load style sheet.
stylesheet.async = False
stylesheet.Load stylesheetFile

If (source.parseError.errorCode <> 0) Then
MsgBox ("Error loading source document: " & source.parseError.reason)
Else
If (stylesheet.parseError.errorCode <> 0) Then
MsgBox ("Error loading stylesheet document: " &
stylesheet.parseError.reason)
Else
' Do the transform.
'MsgBox "Transforming"
source.transformNodeToObject stylesheet, result
result.Save resultFile
End If
End If

End Sub

Private Function KMLaddr()
Close
MsgBox "Please select a location and then specify your desired *.kml
file name"
Dim dlgSaveAs
Dim output As String
Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)
dlgSaveAs.Show
output = dlgSaveAs.SelectedItems(1)
'Absolute paths are required!
Application.ExportXML acExportQuery, "XMLAddr", "C:\tempexp.xml", 1
Transform "C:\tempexp.xml", "C:\kmladdr.xsl", output
Kill "C:\tempexp.xml"

End Function

 

[Douglas J. Steele]

Scrap the File Dialog (it's troublesome at best), and use the all-API
approach shown in http://www.mvps.org/access/api/api0001.htm

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Your Transform Sub works for me using Access 2003. Maybe change it to

Dim source As New DOMDocument
Dim stylesheet As New DOMDocument
Dim result As New DOMDocument

I know little about XML, so there is not much more I can say.

I did some debugging and the troublesome line is:

Set dlgSaveAs = Application.FileDialog(msoFileDialogSaveAs)

....in particular: msoFileDialogSaveAs ...is there a alternative that
works with 2002? Thanks!

 

[John Musbach]

Try using Late Binding. Don't set any reference, and try:

Private Sub Transform(sourceFile, stylesheetFile, resultFile)

Dim source As Object

[Snip!]

    Application.ExportXML acExportQuery, "XMLAddr", "C:\tempexp.xml",1
    Transform "C:\tempexp.xml", "C:\kmladdr.xsl", output
    Kill "C:\tempexp.xml"

End Function

Thanks, I'll give this a try

 

[John Musbach]

Scrap the File Dialog (it's troublesome at best), and use the all-API
approach shown inhttp://www.mvps.org/access/api/api0001.htm

Thanks for the suggestion. I have actually tried that once before and
got as far as pasting that into its own module, but then couldn't
figure out how to refer to the code contained in the module? I had
tried pasting the test code but it said that items defined in the
module weren't defined. Not clear on how this works...

 

[Douglas J. Steele]

Create a new module (not a class module, nor a module associated with a form
or report).

Copy everything in the shaded area (between Code Start and Code End) and
paste it into the new module.

Save the module. (Do not give it the same name as any of the routines in the
module. Call it something like modFileDialog or basFileDialog)

To invoke it to select a file, you need code like the four lines of code at
the top:

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

That will let you select Excel spreadsheets. Play with the filter to get
other types.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Scrap the File Dialog (it's troublesome at best), and use the all-API
approach shown inhttp://www.mvps.org/access/api/api0001.htm

Thanks for the suggestion. I have actually tried that once before and
got as far as pasting that into its own module, but then couldn't
figure out how to refer to the code contained in the module? I had
tried pasting the test code but it said that items defined in the
module weren't defined. Not clear on how this works...