none
Searching certain keydata found in MSO Word in a MSO Excel spreadsheet RRS feed

  • Question

  • I have written a macro in word that builds me a certain key. I need to look in a small Excel database to learn the exact data that belongs to that key.

    My question is how can I transport this key form word to excel using only VBA. I don't have access to the registry so I can't use that option. I thought about using a .ini file but I don't get it to work.

    I hope somebady can help me.

    Greetz

    Wednesday, August 17, 2011 6:30 AM

Answers

  • Hi Peet

    This isn't something I can trouble-shoot for you, as I have no access to your machine and can't "watch over your shoulder".

    <<after a  bit research in the code I found that the object xmlDoc has no Intellisense.>>

    You have to do the detective work, starting at the declaraion of Dim xmlDoc AS. After you type the space you need to get find an entry for MSXML of some kind or other. If it's not there, then you haven't created the Reference to msxml correctly (Tools/References).


    Cindy Meister, VSTO/Word MVP
    • Proposed as answer by Bruce Song Thursday, September 1, 2011 7:52 AM
    • Marked as answer by Macy Dong Thursday, September 1, 2011 8:18 AM
    Monday, August 22, 2011 12:48 PM
    Moderator

All replies

  • Hi Peet

    I'm going to move your question to the Word for Developers forum, where you'll find many more application specialists who should be able to give you some tips. If you could please supplement the discussion with:

    1. Version of Office

    2. How using an INI file "doesn't work", possibly also with the code you tried


    Cindy Meister, VSTO/Word MVP
    Wednesday, August 17, 2011 8:34 AM
    Moderator
  • Hi Cindy,

    Thank you for answering.

    At home I werk with MSO 2007, but since i write it for my work it will have to work in MSO 2003

    I started in MS Word with the following code for writing the key to the .ini file:

    System.PrivateProfileString(strPathAndFileName, "Key", "Code") = "GeneratedKey"

    I tried to read in to excel with the code used in word tot get information out a .ini file, so the code look like this:

    strSearchstring = System.PrivateProfileString(strPathAndFileName, "Key", "Code")

    As an answer I got a message that an object was needed, but in the helpfiles I couldn't find how that should work.

    Greets

    Friday, August 19, 2011 7:15 AM
  • Hi Peet

    As you're using VBA, as long as you're careful to not use anything new in 2007, you should be able to write your code at home. But you'll of course need to test thoroughly at work.

    <<I started in MS Word with the following code for writing the key to the .ini file:

    System.PrivateProfileString(strPathAndFileName, "Key", "Code") = "GeneratedKey"

    I tried to read in to excel with the code used in word tot get information out a .ini file>>

    Ah. Excel doesn't have all the same facilities and Word, and vice-versa. I don't think Excel has an equivalent to PrivateProfileString, which is why you're running into problems. Would you like me to move your question to the Excel for Developers forum so that you can ask the application experts for Excel how they read/write to INI files?

    Another possibility would be to store settings in XML files, as a substitute for INI. That's the "modern" way. There's nothing built into any of the Office apps for that, but you can create a reference to the MSMXL library in order to use that in your code. I'm not recommending it, one way or the other, just offering an alternative...


    Cindy Meister, VSTO/Word MVP
    Friday, August 19, 2011 7:38 AM
    Moderator
  • Hi Cindy,

    Okay, I guessed already that reading .ini files would be the problem. So I chose will try your alternative. Do you have example code for me?

     

    I have no knowledge of working with xml, so starting to gather that now seems a good opportunity.

     

    Greetz

     

     

    Friday, August 19, 2011 10:26 AM
  • Hi Peet

    I'm afraid it's not something I can just copy/paste. Learning XML is about the same as learning VBA: you have to sit down and read to get the necessary background so that you can understand the kinds of answers you'll get in a forum.

    The first thing you'd need to do is sit down and work out the XML file structure you want to use. Think of it like creating an outline hierarchy in Word. Something along the lines of

    dataForExcel
      keyx
      keyz

    For as many items and levels as you need.

    Then you need to turn this into well-formed XML. Personally, I like working in the Visual Studio .NET editor as it does a lot of the "grunt work" for me. But you can also type in Notepad if it's something simple.

    Once you have the XML, copy/paste it in a message and we can tell you how to read/write data in VBA.


    Cindy Meister, VSTO/Word MVP
    Saturday, August 20, 2011 12:06 PM
    Moderator
  • Hi Cindy,

    Since I don't have your e-mailadress, I put the xml code in here.


    <?xml version="1.0" encoding="UTF-8"?>
    -<KeysToExcel>

               <key1>thisiskey01</key1>

               <key2>thisiskey02</key2>

               <key3>thisiskey03</key3>

    </KeysToExcel>

    I named the file keys.xml and typed it in notepad.

    The possibilities I need in VBA are writing keys to the file (Word), reading and deleting them(Word and Excel).

    Greetz

     


    Sunday, August 21, 2011 7:49 AM
  • Hi Peet

    Thank you for posting it here - I don't answer questions for free via email :-)

    First, you need to set a reference in your VBA project to MSXML type library. As this is very basic stuff, the version doesn't matter. I probably used MSXML 6 for this sample code from my book. That's the version which is installed with Office 2007, as I recall. If you use an earlier version it's possible that you might need to change a declaration as follows, as well as the "Set" line for xmlDoc:

    Dim xmlDoc As MSXML.DOMDocument - see what Intellisense offers you when you type "As MSX".

    Sub XmlKonfigSchreiben()
     Const cstrFileName As String = "C:\Folders\keys.xml"
     Const strFehlerDateiNichtGefunden As String = _
      "Could not find the XML keys file."
     Dim xmlDoc As MSXML2.DOMDocument 
     Dim strVorname As String, strName As String
    
     Set xmlDoc = New MSXML2.DOMDocument
     xmlDoc.async = False
     If Not xmlDoc.Load(cstrFileName) Then
      MsgBox strFehlerDateiNichtGefunden
     Else
      If xmlDoc.parseError <> 0 Then
       MsgBox "Parse error in XML-Datei: " & xmlDoc.parseError.reason
      Else
       xmlDoc.SelectSingleNode("thisiskey01").Text = "key1"
       xmlDoc.SelectSingleNode("thisiskey02").Text = "key2"
       xmlDoc.Save cstrFileName
      End If
     End If
     Set xmlDoc = Nothing
    End Sub
    
    
    Sub XmlKonfigLesen()
      Const cstrFileName As String = "C:\Folders\keys.xml"
     Const strFehlerDateiNichtGefunden As String = _
      "Could not find the XML keys file."
     Dim xmlDoc As MSXML2.DOMDocument
     Dim strKey01 As String, strKey02 As String
     
     Set xmlDoc = New MSXML2.DOMDocument
     xmlDoc.async = False
     If Not xmlDoc.Load(cstrFileName) Then
      MsgBox strFehlerDateiNichtGefunden
     Else
      If xmlDoc.parseError <> 0 Then
       MsgBox "Parse error in XML-Datei: " & xmlDoc.parseError.reason
      Else
       strKey01 = xmlDoc.SelectSingleNode("thisiskey01").Text
       strKey02 = xmlDoc.SelectSingleNode("thisiskey02").Text
       Debug.print strVorname, strName
      End If
     End If
     Set xmlDoc = Nothing
    End Sub
    
    


     


    Cindy Meister, VSTO/Word MVP
    Sunday, August 21, 2011 8:51 AM
    Moderator
  • Hi Cindy,

    Thanks for the code, I copied it and pasted it in to de form in which it should work. After running it i got a err.message with number 91, saying objectvariable or blockvariable is nor set.

     

    after a  bit research in the code I found that the object xmlDoc has no Intellisense. This, I think, means that the code:

    xmlDoc.SelectSingleNode("thisiskey01").Text = "key1"

    xmlDoc.SelectSingleNode("thisiskey02").Text = "key2"

    can't be executed. I've tried it with every available MSXM-Llibrary but it didn't run and gave me the exact same message every time.

    I even changed the object declaration but that was also not the solution, so I set it back again.

    I hope you have the solution to it

    Greetz Peet


    Sunday, August 21, 2011 6:56 PM
  • Hi Peet

    This isn't something I can trouble-shoot for you, as I have no access to your machine and can't "watch over your shoulder".

    <<after a  bit research in the code I found that the object xmlDoc has no Intellisense.>>

    You have to do the detective work, starting at the declaraion of Dim xmlDoc AS. After you type the space you need to get find an entry for MSXML of some kind or other. If it's not there, then you haven't created the Reference to msxml correctly (Tools/References).


    Cindy Meister, VSTO/Word MVP
    • Proposed as answer by Bruce Song Thursday, September 1, 2011 7:52 AM
    • Marked as answer by Macy Dong Thursday, September 1, 2011 8:18 AM
    Monday, August 22, 2011 12:48 PM
    Moderator