5

I have a Word VBA function that I'm trying to build in Excel VBA (the reason behind this choice comes from this question) and I'm stuck at the following problem:

The Word VBA function uses System.PrivateProfileString extensively, which throws the Compile Error: invalid qualifier error when applied to Excel VBA. What's the equivalent statement in Excel? Alternatively, how do I work my way around this?

Example of use: 

strHomeSharePath = System.PrivateProfileString("", "HKEY_CURRENT_USER\Volatile Environment", "HOMESHARE")
Community
  • 1
  • 1
Holene
  • 563
  • 1
  • 7
  • 26

2 Answers2

4

If you want to read the registry in Excel you can do it this way.

Sub test()
Dim strPath As String
    strPath = RegKeyRead("HKEY_CURRENT_USER\Volatile Environment\HOMESHARE")
End Sub

Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object

  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function
Roly
  • 2,126
  • 2
  • 20
  • 34
Moosli
  • 3,140
  • 2
  • 19
  • 45
  • Hell yeah! This is nice. Quick question: this function sends me to a `.ini` file, which has a header such as `[Header Text]` and several entries/keys below, such as "Company=company name". Trying to access the entries returns the error "Invalid root in registry key". Any idea how I can specifically return the value of a key? – Holene Jan 27 '16 at 11:37
  • @Holene Can you Post some Code? Because i don't understand your Question. – Moosli Jan 27 '16 at 11:45
  • Ah, yeah, I did some more searching, and what I'm basically asking is if there is an easy way to read `.ini` files in Excel VBA? – Holene Jan 27 '16 at 12:03
  • @Holene [VBA Read Text File](http://stackoverflow.com/questions/27185865/vba-read-all-text-in-a-text-file) – Moosli Jan 27 '16 at 12:27
1

Assuming (as per your other question) your Excel code has a reference to a word Object, let's say "objWord", it should be...

strHomeSharePath = objWord.System.PrivateProfileString("", "HKEY_CURRENT_USER\Volatile Environment", "HOMESHARE")

IMO the word "System" is a bit misleading, suggesting that it isn't to do with Word. But it's a class in the Word library.