How to list my databases in EXCEL with VBA

Question

How to list my databases in EXCEL with VBA

Navigation

#1 by (1 votes)

0

I'm trying to dig deeper into the connection from VBA using Excel versus MS SQL.

All right with the forced connection putting all the parameters in the data of the macro (name of the server, database, etc.) But I would like to do something more complex.

Would it be possible at the beginning of the excel sheet, in a combo, to list the server's databases? I only get errors and I do not know if I'm wasting my time.

Thank you.

Deputy code

Sub CommandButton1_Click()

Dim CMDStoredProc As ADODB.Command
Dim CnnConexion As ADODB.Connection
Dim RcsDatos As ADODB.Recordset

Dim CadConexion As String 'Cadena de conexión
Dim Row As Integer
Dim RecordsAffected As Long

'Cadena de conexión
Dim Servidor As String
Dim Usuario As String
Dim Contrasena As String
Dim BaseDatos As String

Servidor = "SOPORTE-SW1\A3ERP"
Usuario = "sa"
Contrasena = "1234"
BaseDatos = ""

' CadConexion = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & Usuario & ";Pwd=" & Contrasena & ";Initial Catalog=" & BaseDatos & ";Data Source=" & Servidor & ""
CadConexion = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & Usuario & ";Pwd=" & Contrasena & ";Data Source=" & Servidor & ""

Set CnnConexion = New ADODB.Connection
Set RcsDatos = New ADODB.Recordset
Set CMDStoredProc = New ADODB.Command

'Establecemos comunicación con nuestro servidor SQL Server
Call CnnConexion.Open(CadConexion)

'Enlazamos nuestros objetos y definimos el procedimiento almacenado a ejecutar
CMDStoredProc.CommandType = adCmdText
Set CMDStoredProc.ActiveConnection = CnnConexion
CMDStoredProc.CommandText = "EXEC sp_helpdb"

'Creamos el parámetro del procedimiento almacenado
Call CMDStoredProc.Parameters.Append(CMDStoredProc.CreateParameter("PV_OPCION", DataTypeEnum.adChar, ParameterDirectionEnum.adParamInput, 10))

'Ejecutamos de Script
Set RcsDatos = CMDStoredProc.Execute(RecordsAffected, , ExecuteOptionEnum.adAsyncFetch)

'Recorremos el Recordset resultante para asignarlo a la celda en Excel
Row = 1
Do While Not RcsDatos.EOF
    Cells(Row, 2).Value = RcsDatos.Fields(0).Value
    Row = Row + 1
    RcsDatos.MoveNext
Loop

End Sub

The error that appears to me is "The user-defined type has not been defined"

sql-server vba excel

asked by lordmiaux 26.12.2017 в 13:08

source

1 answer

problems configuring apache tomcat 8.5.24 for java enter abstract parameter in interfaces

score 1 · Answer 1

The specific error you receive indicates that you are missing references to the necessary libraries to be able to manipulate certain classes.

According to your code, it is most likely that you did not include a reference to the ADO library.

To add it, in the VBA editor, go to the menu Tools --> References ( Herramientas --> Referencias in Spanish?), and add the library Microsoft ActiveX Data Objects X.X Library . You may find several versions of this library in the list. You can usually choose the one with the most recent version.