Solved

How to read binary data from the registry?

Posted on 1997-05-25
6
496 Views
Last Modified: 2008-03-17
If I want to read the data from an Excel spreadsheet, the first row of the data will be skipped.  In order to read the first row of the data, I need to change the Registry setting of the Jet Engine. Is there any other way to accomplish this task?  If not, the registry setting is binary type, how can I retrieve/set it? I know how to deal with the string/word type, but not the binary.  Any idea?
0
Comment
Question by:ksleung
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:ChrisLewis
Comment Utility
Excerpts from the Microsoft KB Article ID: Q145679

...

Setting/Modifying a Value

Creating and setting a value of a specified key can be accomplished with the following short procedure. SetKeyValue takes the key that the value will be associated with, the name of the value, the setting of the value, and the type of the value (the SetValueEx function only supports REG_SZ and REG_DWORD, but this can be modified if necessary). Specifying a new value for an existing sValueName will modify the current setting of that value.

Private Sub SetKeyValue (sKeyName As String, sValueName As String, _
   vValueSetting As Variant, lValueType As Long)
       Dim lRetVal As Long         'result of the SetValueEx function
       Dim hKey As Long         'handle of open key

       'open the specified key
       lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, _
                              KEY_ALL_ACCESS, hKey)
       lRetVal = SetValueEx(hKey, sValueName, lValueType, vValueSetting)
       RegCloseKey (hKey)
End Sub


A call of:
SetKeyValue "TestKey\SubKey1", "StringValue", "Hello", REG_SZ

will create a value of type REG_SZ called "SubKey1" with the setting of "Hello". This value will be associated with the key SubKey1 of "TestKey".

In this case, "TestKey" is a subkey of HKEY_CURRENT_USER, but this can be modified by changing the call to RegOpenKeyEx. This call will fail if "TestKey\SubKey1" does not exist. To avoid this problem, use a call to RegCreateKeyEx instead of a call to RegOpenKeyEx. RegCreateKeyEx will open a specified key if it already exists.

Querying a Value

The next procedure can be used to ascertain the setting of an existing value. QueryValue takes the name of the key and the name of a value associated with that key and displays a message box with the corresponding value. It uses a call to the QueryValueEx wrapper function defined below, that only supports REG_SZ and REG_DWORD types.

Private Sub QueryValue (sKeyName As String, sValueName As String)
       Dim lRetVal As Long         'result of the API functions
       Dim hKey As Long         'handle of opened key
       Dim vValue As Variant      'setting of queried value

       lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, _
   KEY_ALL_ACCESS, hKey)
       lRetVal = QueryValueEx(hKey, sValueName, vValue)
       MsgBox vValue
       RegCloseKey (hKey)
   End Sub

With this procedure, a call of:

QueryValue "TestKey\SubKey1", "StringValue"

will display a message box with the current setting of the "StringValue" value, and assumes that "StringValue" exists in the "TestKey\SubKey1" key.

If the Value that you query does not exist then QueryValue will return an error code of 2 - 'ERROR_BADKEY'.

API Function and Constant Declarations

Option Explicit
Global Const REG_SZ As Long = 1
Global Const REG_DWORD As Long = 4

Global Const HKEY_CLASSES_ROOT = &H80000000
Global Const HKEY_CURRENT_USER = &H80000001
Global Const HKEY_LOCAL_MACHINE = &H80000002
Global Const HKEY_USERS = &H80000003

Global Const ERROR_NONE = 0
Global Const ERROR_BADDB = 1
Global Const ERROR_BADKEY = 2
Global Const ERROR_CANTOPEN = 3
Global Const ERROR_CANTREAD = 4
Global Const ERROR_CANTWRITE = 5
Global Const ERROR_OUTOFMEMORY = 6
Global Const ERROR_INVALID_PARAMETER = 7
Global Const ERROR_ACCESS_DENIED = 8
Global Const ERROR_INVALID_PARAMETERS = 87
Global Const ERROR_NO_MORE_ITEMS = 259

Global Const KEY_ALL_ACCESS = &H3F

Global Const REG_OPTION_NON_VOLATILE = 0

Declare Function RegCloseKey Lib "advapi32.dll" _ (ByVal hKey As Long) As Long

Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias _ "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _ ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions _ As Long, ByVal samDesired As Long, ByVal lpSecurityAttributes _ As Long, phkResult As Long, lpdwDisposition As Long) As Long

Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias _ "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _ ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As _ Long) As Long

Declare Function RegQueryValueExString Lib "advapi32.dll" Alias _ "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _ String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _ As String, lpcbData As Long) As Long
Declare Function RegQueryValueExLong Lib "advapi32.dll" Alias _ "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _ String, ByVal lpReserved As Long, lpType As Long, lpData As _ Long, lpcbData As Long) As Long
Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias _ "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _ String, ByVal lpReserved As Long, lpType As Long, ByVal lpData _ As Long, lpcbData As Long) As Long
Declare Function RegSetValueExString Lib "advapi32.dll" Alias _ "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _ ByVal Reserved As Long, ByVal dwType As Long, ByVal lpValue As _ String, ByVal cbData As Long) As Long
Declare Function RegSetValueExLong Lib "advapi32.dll" Alias _ "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _ ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, _ ByVal cbData As Long) As Long


SetValueEx and QueryValueEx Wrapper Functions

Public Function SetValueEx(ByVal hKey As Long, sValueName As String, _ lType As Long, vValue As Variant) As Long
    Dim lValue As Long
    Dim sValue As String
    Select Case lType
        Case REG_SZ
            sValue = vValue & Chr$(0)
            SetValueEx = RegSetValueExString(hKey, sValueName, 0&, lType, sValue, Len(sValue))
        Case REG_DWORD
            lValue = vValue
            SetValueEx = RegSetValueExLong(hKey, sValueName, 0&, lType, lValue, 4)
        End Select
End Function

Function QueryValueEx(ByVal lhKey As Long, ByVal szValueName As _ String, vValue As Variant) As Long

    Dim cch As Long
    Dim lrc As Long
    Dim lType As Long
    Dim lValue As Long
    Dim sValue As String

    On Error GoTo QueryValueExError

    ' Determine the size and type of data to be read
    lrc = RegQueryValueExNULL(lhKey, szValueName, 0&, lType, 0&, cch)
    If lrc <> ERROR_NONE Then Error 5

    Select Case lType
        ' For strings
        Case REG_SZ:
            sValue = String(cch, 0)
           lrc = RegQueryValueExString(lhKey, szValueName, 0&, lType,  sValue, cch)

            If lrc = ERROR_NONE Then
                vValue = Left$(sValue, cch)
            Else
                vValue = Empty
            End If
        ' For DWORDS
        Case REG_DWORD:
lrc = RegQueryValueExLong(lhKey, szValueName, 0&, lType, lValue, cch)
            If lrc = ERROR_NONE Then vValue = lValue
        Case Else
            'all other data types not supported
            lrc = -1
    End Select

QueryValueExExit:
    QueryValueEx = lrc
    Exit Function

QueryValueExError:
    Resume QueryValueExExit
End Function


+-------+-----+
+-------+-----+

Note that these are all the 32 bit calls.

I have the 16 bit calls somewhere.

Hope this helps

Chris
0
 
LVL 1

Author Comment

by:ksleung
Comment Utility
I have all these functions already. However, they cannot retrieve the binary values for me.  I also tried to modify it, but it does not work for me.  Thanks anyway.
0
 
LVL 7

Expert Comment

by:tward
Comment Utility
I don't understand how it skips over the first row as I use OLE to read data from an Excel Spreadsheet and it does not skip over the first row.  

It might help if you post some of the code that you are using.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:ksleung
Comment Utility
Tward,

First, you can check your registry setting first. Under HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\JET\3.0\ENGINES\EXCEL, if the value for FirstRowHasNames is 00, then it will not skip the first row of data, however, if it 01, then the first row cannot be read. My problem is to change the 01 to 00. However, the functions mentioned by ChrisLewis cannot do the job since the entry is binary. Any idea
0
 
LVL 3

Accepted Solution

by:
ChrisLewis earned 100 total points
Comment Utility
Sorry about that, I just posted the previous with out checking for binarys.  Try this.  It should be a little cleaner, and it does work!

Option Explicit
Global Const REG_SZ As Long = 1
Global Const REG_BINARY As Long = 3
Global Const REG_DWORD As Long = 4

Global Const HKEY_CLASSES_ROOT = &H80000000
Global Const HKEY_CURRENT_USER = &H80000001
Global Const HKEY_LOCAL_MACHINE = &H80000002
Global Const HKEY_USERS = &H80000003
Global Const ERROR_NONE = 0
Global Const KEY_ALL_ACCESS = &H3F


Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias "RegCreateKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal Reserved As Long, ByVal lpClass As String, ByVal dwOptions As Long, ByVal samDesired As Long, ByVal lpSecurityAttributes As Long, phkResult As Long, lpdwDisposition As Long) As Long
Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias "RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long
Declare Function RegQueryValueExString Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As String, lpcbData As Long) As Long
Declare Function RegQueryValueExLong Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, lpData As Long, lpcbData As Long) As Long
Declare Function RegQueryValueExNULL Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, ByVal lpData As Long, lpcbData As Long) As Long
Declare Function RegSetValueExString Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, ByVal lpValue As String, ByVal cbData As Long) As Long
Declare Function RegSetValueExLong Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, lpValue As Long, ByVal cbData As Long) As Long

Public Function SetValueEx(ByVal hKey As Long, sValueName As String, lType As Long, vValue As Variant) As Long
  Dim lValue As Long
  Dim sValue As String
 
  Select Case lType
    Case REG_SZ
      sValue = vValue & Chr$(0)
      SetValueEx = RegSetValueExString(hKey, sValueName, 0&, lType, sValue, Len(sValue))
    Case REG_BINARY, REG_DWORD
      lValue = vValue
      SetValueEx = RegSetValueExLong(hKey, sValueName, 0&, lType, lValue, 4)
  End Select
End Function

Function QueryValueEx(ByVal lhKey As Long, ByVal szValueName As String, vValue As Variant) As Long
  Dim cch As Long
  Dim lrc As Long
  Dim lType As Long
  Dim lValue As Long
  Dim sValue As String

  On Error GoTo QueryValueExError

  ' Determine the size and type of data to be read
  lrc = RegQueryValueExNULL(lhKey, szValueName, 0&, lType, 0&, cch)
  If lrc <> ERROR_NONE Then Error 5

  Select Case lType
    ' For strings
    Case REG_SZ
      sValue = String(cch, 0)
      lrc = RegQueryValueExString(lhKey, szValueName, 0&, lType, sValue, cch)
      If lrc = ERROR_NONE Then
        vValue = Left$(sValue, cch)
      Else
        vValue = Empty
      End If
   
    ' For DWORDS, Binary
    Case REG_BINARY, REG_DWORD
      lrc = RegQueryValueExLong(lhKey, szValueName, 0&, lType, lValue, cch)
      If lrc = ERROR_NONE Then vValue = lValue
    Case Else
      'all other data types not supported
      lrc = -1
  End Select

QueryValueExExit:
  QueryValueEx = lrc
  Exit Function

QueryValueExError:
  Resume QueryValueExExit
End Function


 Sub SetKeyValue(sKeyName As String, sValueName As String,   vValueSetting As Variant, lValueType As Long)
  Dim lRetVal As Long 'result of the SetValueEx function
  Dim hKey As Long 'handle of open key

  'open the specified key
  lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, KEY_ALL_ACCESS, hKey)
  lRetVal = SetValueEx(hKey, sValueName, lValueType, vValueSetting)
  RegCloseKey (hKey)
End Sub

 Function QueryValue(sKeyName As String, sValueName As String)
  Dim lRetVal As Long         'result of the API functions
  Dim hKey As Long         'handle of opened key
  Dim vValue As Variant      'setting of queried value

  lRetVal = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, 0, KEY_ALL_ACCESS, hKey)
  lRetVal = QueryValueEx(hKey, sValueName, vValue)
  RegCloseKey (hKey)
  QueryValue = vValue
End Function

Make a form with 3 text boxes, two command buttons, and an option button control array with 3 items.  Label Command one as Get, Command 2 as Set, Option1(0) DWord, Option1(1) String, and Option1(2) binary.

I made an ODBC Source called "Color Test", and used this Key (in Text1):
Software\ODBC\ODBC.INI\Color Test\Engines\Excel
With this item (in Text2): FirstRowHasNames

Private Sub Command1_Click()
  Text3 = QueryValue(Text1, Text2)
End Sub

Private Sub Command2_Click()
  Dim lngType As Long
  Select Case True
    Case Option1(0).Value
      lngType = REG_DWORD
      SetKeyValue Text1, Text2, Text3, lngType
    Case Option1(1).Value
      lngType = REG_SZ
      SetKeyValue Text1, Text2, Text3, lngType
    Case Option1(2).Value
      lngType = REG_BINARY
      SetKeyValue Text1, Text2, CByte(Text3), lngType
  End Select

End Sub


This will retrieve the values and set strings, dwords, and binaries.

It's interesting that when I set, I end up with 01 00 00 00, which is one byte, rather than 01, one bit. I don't know if VB can actually send just a bit.  Still seems to work, however.



Hope this helps,
Chris

E-mail me if you want the form and module sent to you.

Chris@TAYLORMGMT.COM
0
 
LVL 1

Author Comment

by:ksleung
Comment Utility
It works fine, but I need to make it a 00 or 01 instead of 01 00 00 or 00 00 00. Otherwise, it will not work for me.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now