Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > DSN Listing
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default DSN Listing
Hi there

Does anybody have code that will bring in a list of external data
sources i.e. the list that appears when you click Data, Get external
Data, New Database Query.

Thanks

Michael
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default DSN Listing
One way (the only one?) is to read it from the registry:
HKEY_USERS\bla-bla-bla\Software\ODBC\ODBC.INI

RBS

"Michael Rekas" wrote in message
...
Hi there

Does anybody have code that will bring in a list of external data
sources i.e. the list that appears when you click Data, Get external
Data, New Database Query.

Thanks

Michael

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default DSN Listing
This code will dump the external datasources in the sheet on my machine.
All you will have to figure out is how to get the string:
"S-1-5-21-45121442-3062903995-4062188013-1005"
As I take it that this will be different on every PC.
Maybe somebody could explain how to do this.


Option Explicit
Const ERROR_NO_MORE_ITEMS = 259&
Const HKEY_USERS = &H80000003
Private Declare Function RegCloseKey _
Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
Private Declare Function RegOpenKey _
Lib "advapi32.dll" _
Alias "RegOpenKeyA" (ByVal hKey As Long, _
ByVal lpSubKey As String, _
phkResult As Long) As Long
Private Declare Function RegEnumKeyEx _
Lib "advapi32.dll" Alias _
"RegEnumKeyExA" (ByVal hKey As Long, _
ByVal dwIndex As Long, _
ByVal lpName As String, _
lpcbName As Long, _
ByVal lpReserved As Long, _
ByVal lpClass As String, _
lpcbClass As Long, _
lpftLastWriteTime As Any) As Long

Sub ShowExternalDataSources()

Dim hKey As Long
Dim Cnt As Long
Dim sName As String
Dim sData As String
Dim Ret As Long
Dim RetData As Long
Const BUFFER_SIZE As Long = 255

Ret = BUFFER_SIZE

If RegOpenKey(HKEY_USERS, _
"S-1-5-21-45121442-3062903995-4062188013-1005" & _
"\Software\ODBC\ODBC.INI", _
hKey) = 0 Then
sName = Space(BUFFER_SIZE)
While RegEnumKeyEx(hKey, _
Cnt, _
sName, _
Ret, _
ByVal 0&, _
vbNullString, _
ByVal 0&, _
ByVal 0&) < ERROR_NO_MORE_ITEMS
Cnt = Cnt + 1
Cells(Cnt, 1) = Left$(sName, Ret)
sName = Space(BUFFER_SIZE)
Ret = BUFFER_SIZE
Wend
'close the registry key
RegCloseKey hKey
Else
MsgBox "Error while calling RegOpenKey", , ""
End If

End Sub


RBS


"Michael Rekas" wrote in message
...
Hi there

Does anybody have code that will bring in a list of external data
sources i.e. the list that appears when you click Data, Get external
Data, New Database Query.

Thanks

Michael

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default DSN Listing
OK, this code will do it:


Option Explicit
Const ERROR_NO_MORE_ITEMS = 259&
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_USERS = &H80000003
Private Declare Function RegCloseKey _
Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
Private Declare Function RegOpenKey _
Lib "advapi32.dll" _
Alias "RegOpenKeyA" (ByVal hKey As Long, _
ByVal lpSubKey As String, _
phkResult As Long) As Long
Private Declare Function RegEnumKeyEx _
Lib "advapi32.dll" Alias _
"RegEnumKeyExA" (ByVal hKey As Long, _
ByVal dwIndex As Long, _
ByVal lpName As String, _
lpcbName As Long, _
ByVal lpReserved As Long, _
ByVal lpClass As String, _
lpcbClass As Long, _
lpftLastWriteTime As Any) As Long

Sub ShowExternalDataSources()

Dim hKey As Long
Dim Cnt As Long
Dim sName As String
Dim sData As String
Dim Ret As Long
Dim RetData As Long
Const BUFFER_SIZE As Long = 255

Ret = BUFFER_SIZE

If RegOpenKey(HKEY_CURRENT_USER, _
"Software\ODBC\ODBC.INI", _
hKey) = 0 Then
sName = Space(BUFFER_SIZE)
While RegEnumKeyEx(hKey, _
Cnt, _
sName, _
Ret, _
ByVal 0&, _
vbNullString, _
ByVal 0&, _
ByVal 0&) < ERROR_NO_MORE_ITEMS
Cnt = Cnt + 1
Cells(Cnt, 1) = Left$(sName, Ret)
sName = Space(BUFFER_SIZE)
Ret = BUFFER_SIZE
Wend
'close the registry key
RegCloseKey hKey
Else
MsgBox "Error while calling RegOpenKey", , ""
End If

End Sub


RBS


"Michael Rekas" wrote in message
...
Hi there

Does anybody have code that will bring in a list of external data
sources i.e. the list that appears when you click Data, Get external
Data, New Database Query.

Thanks

Michael

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 12
Default DSN Listing
Thanks. I appreciate your persistence.

Your code returns all default and Microsoft SQL databases.

I searched the registry and found that the Pervasive.SQL DSNs (and for
that matter the Microsoft SQl DSNs) are stored in the same key under
HKEY_LOCAL_MACHINE.

I added "Const HKEY_LOCAL_MACHINE = &H80000002" to the declarations (a
good guess as I really do not understand your code!).

I also changed HKEY_CIRRENT_USER to HKEY_LOCAL_MACHINE in the Sub and
it gives me exactly what I want.

Absolutely thrilled. Thanks for the help.

Regards

Michael.



On Fri, 25 Feb 2005 14:45:34 -0000, "RB Smissaert"
wrote:

OK, this code will do it:


Option Explicit
Const ERROR_NO_MORE_ITEMS = 259&
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_USERS = &H80000003
Private Declare Function RegCloseKey _
Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
Private Declare Function RegOpenKey _
Lib "advapi32.dll" _
Alias "RegOpenKeyA" (ByVal hKey As Long, _
ByVal lpSubKey As String, _
phkResult As Long) As Long
Private Declare Function RegEnumKeyEx _
Lib "advapi32.dll" Alias _
"RegEnumKeyExA" (ByVal hKey As Long, _
ByVal dwIndex As Long, _
ByVal lpName As String, _
lpcbName As Long, _
ByVal lpReserved As Long, _
ByVal lpClass As String, _
lpcbClass As Long, _
lpftLastWriteTime As Any) As Long

Sub ShowExternalDataSources()

Dim hKey As Long
Dim Cnt As Long
Dim sName As String
Dim sData As String
Dim Ret As Long
Dim RetData As Long
Const BUFFER_SIZE As Long = 255

Ret = BUFFER_SIZE

If RegOpenKey(HKEY_CURRENT_USER, _
"Software\ODBC\ODBC.INI", _
hKey) = 0 Then
sName = Space(BUFFER_SIZE)
While RegEnumKeyEx(hKey, _
Cnt, _
sName, _
Ret, _
ByVal 0&, _
vbNullString, _
ByVal 0&, _
ByVal 0&) < ERROR_NO_MORE_ITEMS
Cnt = Cnt + 1
Cells(Cnt, 1) = Left$(sName, Ret)
sName = Space(BUFFER_SIZE)
Ret = BUFFER_SIZE
Wend
'close the registry key
RegCloseKey hKey
Else
MsgBox "Error while calling RegOpenKey", , ""
End If

End Sub


RBS


"Michael Rekas" wrote in message
.. .
Hi there

Does anybody have code that will bring in a list of external data
sources i.e. the list that appears when you click Data, Get external
Data, New Database Query.

Thanks

Michael

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default DSN Listing
Nice it works now.
I have adapted the code a bit.
The collection will take care of doubles.


Option Explicit
Const ERROR_NO_MORE_ITEMS = 259&
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003
Private Declare Function RegCloseKey _
Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
Private Declare Function RegOpenKey _
Lib "advapi32.dll" _
Alias "RegOpenKeyA" (ByVal hKey As Long, _
ByVal lpSubKey As String, _
phkResult As Long) As Long
Private Declare Function RegEnumKeyEx _
Lib "advapi32.dll" Alias _
"RegEnumKeyExA" (ByVal hKey As Long, _
ByVal dwIndex As Long, _
ByVal lpName As String, _
lpcbName As Long, _
ByVal lpReserved As Long, _
ByVal lpClass As String, _
lpcbClass As Long, _
lpftLastWriteTime As Any) As Long

Sub ShowExternalDataSources()

Dim hKey As Long
Dim Cnt As Long
Dim sName As String
Dim Ret As Long
Const BUFFER_SIZE As Long = 255
Dim coExternalDataSources As Collection
Dim arrRegKeys
Dim i As Long

Set coExternalDataSources = New Collection

arrRegKeys = Array(HKEY_CURRENT_USER, HKEY_LOCAL_MACHINE)

Ret = BUFFER_SIZE

For i = 0 To UBound(arrRegKeys)
Cnt = 0
If RegOpenKey(arrRegKeys(i), _
"Software\ODBC\ODBC.INI", _
hKey) = 0 Then
sName = Space(BUFFER_SIZE)
While RegEnumKeyEx(hKey, _
Cnt, _
sName, _
Ret, _
ByVal 0&, _
vbNullString, _
ByVal 0&, _
ByVal 0&) < ERROR_NO_MORE_ITEMS
Cnt = Cnt + 1
On Error Resume Next
coExternalDataSources.Add Left$(sName, Ret), _
Left$(sName, Ret)
On Error GoTo 0
sName = Space(BUFFER_SIZE)
Ret = BUFFER_SIZE
Wend
'close the registry key
RegCloseKey hKey
Else
MsgBox "Error while calling RegOpenKey", , ""
End If
Next

For i = 1 To coExternalDataSources.Count
Cells(i, 1) = coExternalDataSources(i)
Next

End Sub


RBS


"Michael Rekas" wrote in message
...
Thanks. I appreciate your persistence.

Your code returns all default and Microsoft SQL databases.

I searched the registry and found that the Pervasive.SQL DSNs (and for
that matter the Microsoft SQl DSNs) are stored in the same key under
HKEY_LOCAL_MACHINE.

I added "Const HKEY_LOCAL_MACHINE = &H80000002" to the declarations (a
good guess as I really do not understand your code!).

I also changed HKEY_CIRRENT_USER to HKEY_LOCAL_MACHINE in the Sub and
it gives me exactly what I want.

Absolutely thrilled. Thanks for the help.

Regards

Michael.



On Fri, 25 Feb 2005 14:45:34 -0000, "RB Smissaert"
wrote:

OK, this code will do it:


Option Explicit
Const ERROR_NO_MORE_ITEMS = 259&
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_USERS = &H80000003
Private Declare Function RegCloseKey _
Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
Private Declare Function RegOpenKey _
Lib "advapi32.dll" _
Alias "RegOpenKeyA" (ByVal hKey As Long, _
ByVal lpSubKey As String, _
phkResult As Long) As Long
Private Declare Function RegEnumKeyEx _
Lib "advapi32.dll" Alias _
"RegEnumKeyExA" (ByVal hKey As Long, _
ByVal dwIndex As Long, _
ByVal lpName As String, _
lpcbName As Long, _
ByVal lpReserved As Long, _
ByVal lpClass As String, _
lpcbClass As Long, _
lpftLastWriteTime As Any) As Long

Sub ShowExternalDataSources()

Dim hKey As Long
Dim Cnt As Long
Dim sName As String
Dim sData As String
Dim Ret As Long
Dim RetData As Long
Const BUFFER_SIZE As Long = 255

Ret = BUFFER_SIZE

If RegOpenKey(HKEY_CURRENT_USER, _
"Software\ODBC\ODBC.INI", _
hKey) = 0 Then
sName = Space(BUFFER_SIZE)
While RegEnumKeyEx(hKey, _
Cnt, _
sName, _
Ret, _
ByVal 0&, _
vbNullString, _
ByVal 0&, _
ByVal 0&) < ERROR_NO_MORE_ITEMS
Cnt = Cnt + 1
Cells(Cnt, 1) = Left$(sName, Ret)
sName = Space(BUFFER_SIZE)
Ret = BUFFER_SIZE
Wend
'close the registry key
RegCloseKey hKey
Else
MsgBox "Error while calling RegOpenKey", , ""
End If

End Sub


RBS


"Michael Rekas" wrote in message
. ..
Hi there

Does anybody have code that will bring in a list of external data
sources i.e. the list that appears when you click Data, Get external
Data, New Database Query.

Thanks

Michael


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default DSN Listing
For my particular purpose I needed to see from what registry root key the
datasource came from
so I changed the code a bit. Declarations are the same.

Sub ShowExternalDataSources()

Dim hKey As Long
Dim Cnt As Long
Dim sName As String
Dim Ret As Long
Const BUFFER_SIZE As Long = 255
Dim coExternalDataSources As Collection
Dim arrRegKeys
Dim arrRegKeysNames
Dim i As Long
Dim lDataSources As Long

Set coExternalDataSources = New Collection

arrRegKeys = Array(HKEY_CURRENT_USER, _
HKEY_LOCAL_MACHINE)
arrRegKeysNames = Array("HKEY_CURRENT_USER", _
"HKEY_LOCAL_MACHINE")

Ret = BUFFER_SIZE

For i = 0 To UBound(arrRegKeys)
Cnt = 0
If RegOpenKey(arrRegKeys(i), _
"Software\ODBC\ODBC.INI", _
hKey) = 0 Then
sName = Space(BUFFER_SIZE)
While RegEnumKeyEx(hKey, _
Cnt, _
sName, _
Ret, _
ByVal 0&, _
vbNullString, _
ByVal 0&, _
ByVal 0&) < ERROR_NO_MORE_ITEMS
Cnt = Cnt + 1
On Error Resume Next
'keep both the datasource and the registry root key
'--------------------------------------------------
coExternalDataSources.Add Array(Left$(sName, Ret), _
arrRegKeysNames(i)), _
Left$(sName, Ret)
On Error GoTo 0
sName = Space(BUFFER_SIZE)
Ret = BUFFER_SIZE
Wend
'close the registry key
RegCloseKey hKey
Else
MsgBox "Error while calling RegOpenKey", , ""
End If
Next

lDataSources = coExternalDataSources.Count

For i = 1 To lDataSources
Cells(i, 1) = coExternalDataSources(i)(0)
Cells(i, 2) = coExternalDataSources(i)(1)
Next

Range(Cells(1), Cells(lDataSources, 2)).Columns.AutoFit

End Sub


RBS


"Michael Rekas" wrote in message
...
Thanks. I appreciate your persistence.

Your code returns all default and Microsoft SQL databases.

I searched the registry and found that the Pervasive.SQL DSNs (and for
that matter the Microsoft SQl DSNs) are stored in the same key under
HKEY_LOCAL_MACHINE.

I added "Const HKEY_LOCAL_MACHINE = &H80000002" to the declarations (a
good guess as I really do not understand your code!).

I also changed HKEY_CIRRENT_USER to HKEY_LOCAL_MACHINE in the Sub and
it gives me exactly what I want.

Absolutely thrilled. Thanks for the help.

Regards

Michael.



On Fri, 25 Feb 2005 14:45:34 -0000, "RB Smissaert"
wrote:

OK, this code will do it:


Option Explicit
Const ERROR_NO_MORE_ITEMS = 259&
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_USERS = &H80000003
Private Declare Function RegCloseKey _
Lib "advapi32.dll" _
(ByVal hKey As Long) As Long
Private Declare Function RegOpenKey _
Lib "advapi32.dll" _
Alias "RegOpenKeyA" (ByVal hKey As Long, _
ByVal lpSubKey As String, _
phkResult As Long) As Long
Private Declare Function RegEnumKeyEx _
Lib "advapi32.dll" Alias _
"RegEnumKeyExA" (ByVal hKey As Long, _
ByVal dwIndex As Long, _
ByVal lpName As String, _
lpcbName As Long, _
ByVal lpReserved As Long, _
ByVal lpClass As String, _
lpcbClass As Long, _
lpftLastWriteTime As Any) As Long

Sub ShowExternalDataSources()

Dim hKey As Long
Dim Cnt As Long
Dim sName As String
Dim sData As String
Dim Ret As Long
Dim RetData As Long
Const BUFFER_SIZE As Long = 255

Ret = BUFFER_SIZE

If RegOpenKey(HKEY_CURRENT_USER, _
"Software\ODBC\ODBC.INI", _
hKey) = 0 Then
sName = Space(BUFFER_SIZE)
While RegEnumKeyEx(hKey, _
Cnt, _
sName, _
Ret, _
ByVal 0&, _
vbNullString, _
ByVal 0&, _
ByVal 0&) < ERROR_NO_MORE_ITEMS
Cnt = Cnt + 1
Cells(Cnt, 1) = Left$(sName, Ret)
sName = Space(BUFFER_SIZE)
Ret = BUFFER_SIZE
Wend
'close the registry key
RegCloseKey hKey
Else
MsgBox "Error while calling RegOpenKey", , ""
End If

End Sub


RBS


"Michael Rekas" wrote in message
. ..
Hi there

Does anybody have code that will bring in a list of external data
sources i.e. the list that appears when you click Data, Get external
Data, New Database Query.

Thanks

Michael


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
listing permutations scott Excel Worksheet Functions 2 September 10th 08 02:10 PM
Listing all items only once guttyguppy Excel Discussion (Misc queries) 1 June 16th 06 03:07 PM
Duplicate listing huntin_Xcel_answers Excel Worksheet Functions 2 April 15th 06 03:59 PM
sum and listing Andreas5516 Excel Discussion (Misc queries) 2 February 4th 05 10:44 PM
Listing of one cell controlling the listing of another cell. peijuan Excel Programming 0 August 8th 04 03:17 AM


All times are GMT +1. The time now is 01:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement