Taking excel vba and using it in access 1

[camjon]

I have been givin the task of taking vba code done in excel, and converting it into workable code for access. Below is a sample of the excel vba. I know that I will not be activating cells and or a workbook, any suggestions will be very helpful. Thanks




PrivateSubcmdLoad_Click()
DimoDoc As MSXML.DOMDocument
DimfSuccess AsBoolean
DimoRoot As MSXML.IXMLDOMNode
DimoCountry As MSXML.IXMLDOMNode
DimoAttributes As MSXML.IXMLDOMNamedNodeMap
Dim oCountryName AsMSXML.IXMLDOMNode
DimoChildren As MSXML.IXMLDOMNodeList
DimoChild As MSXML.IXMLDOMNode
DimintI AsInteger
On ErrorGoTo HandleErr
SetoDoc = New MSXML.DOMDocument
' Load the XML from disk, without validating it. Wait
' for the load to finish before proceeding.
oDoc.async = False
oDoc.validateOnParse = False
fSuccess = oDoc.Load( _
ActiveWorkbook.Path & "\traffic.xml")
' If anything went wrong, quit now.
If NotfSuccess Then
GoToExitHere
EndIf

' Set up a row counter.
intI = 5
' Delete the previous information.
ActiveSheet.Cells(4, 1).CurrentRegion.ClearContents
ActiveSheet.Shapes(2).Delete
' Create column headers.
ActiveSheet.Cells(4, 1) = "Country"
ActiveSheet.Cells(4, 2) = "Total Visits"
ActiveSheet.Cells(4, 3) = "Latest Visit"
' Get the root of the XML tree.
Set oRoot = oDoc.documentElement
' Go through all children of the root.
ForEachoCountry InoRoot.childNodes
' Collect the attributes for this country/region.
SetoAttributes = oCountry.Attributes
' Extract the country/region name and
' place it on the worksheet.
SetoCountryName = _
oAttributes.getNamedItem("CountryName")
ActiveSheet.Cells(intI, 1).Value = oCountryName.Text
' Go through all the children of the country/region node.
SetoChildren = oCountry.childNodes
ForEachoChild InoChildren
' Get information from each child node to the sheet.
IfoChild.nodeName = "TotalVisits" Then
ActiveSheet.Cells(intI, 2) = oChild.nodeTypedValue
EndIf
IfoChild.nodeName = "LatestVisit" Then
ActiveSheet.Cells(intI, 3) = oChild.nodeTypedValue
EndIf
NextoChild
intI = intI + 1
Next oCountry

 

[Golom]

That's a bit like trying to convert plans for a skyscraper into plans for a submarine.

The Excel and Access object models are completely different creatures and have, mostly, no common or compatible elements. I suggest that you develop a specification document that describes the results you want to see and then start a grass-roots development in Access.

"Conversion" implies that you will not only do what Excel is doing but you will also do it the way that Excel does it and that's really not possible.

 

[SkipVought]

To tack on another suggestion, describe WHAT you want to accomplish, not HOW you percieve it can or should be accomplished in Access or Excel.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[camjon]

Thank you both for your responses, I have gotten it to work.