Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to read binary data from the registry?

Posted on 1997-05-25
6
Medium Priority
?
545 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:ChrisLewis
ID: 1426565
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
ID: 1426566
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
ID: 1426567
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:ksleung
ID: 1426568
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 300 total points
ID: 1426569
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
ID: 1426570
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

722 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