Albrecht
02-16-2010, 06:38 AM
Hi guys
I'm making a Excel ADD-In application for a knowlede database at my work. All the data is located in an Access database and handled by a frontend in Excel (managed by ADO)
Until now I have put the data from Acces into a sheet and refered to it by the listbox rowsource property. And It worked fine in the .xls but when I create the ADD-In I can't get any data in the listbox. It is empty
Is there a simpler better way to do it?
Private Sub cbIdeData_Enter()
On Error GoTo FejlBehandler
'Åben forbindelse til Acces
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String
sql = "Select * from IdeDB"
filenm = DBsti
Call GlobaleProcedurer.GetCn(adoconn, adors, sql, filenm, "", "")
Dim xlsht As Excel.Worksheet
Set xlsht = Workbooks("AluCoreBobleTank.xla").Sheets("Sheet3")
xlsht.Range("A1").CopyFromRecordset adors
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Dim ListRange As Range
Set ListRange = Workbooks("AluCoreBobleTank.xla").Sheets("sheet3") .Range("A1", Sheet3.Range("C65000").End(xlUp))
'Initialiser Listbox
With cbIdeData
.ColumnCount = 3
.ColumnWidths = 30
.RowSource = ListRange.Address
.ListIndex = 0
End With
Set xlsht = Nothing
Exit Sub
FejlBehandler:
Select Case Err
Case -2147352571 'Der er NULL værdi i Access tabellen
Resume Next
Case -2147217913 'Der skrives NULL værdi til Access tabellen
Resume Next
Case -2147217900 'Der er ikke angivet et id nr
Exit Sub
Case Else
MsgBox ("Programmet er afsluttet pga udefineret fejl: " & Err.Number & " - " & Err.Description)
Exit Sub
End
I'm making a Excel ADD-In application for a knowlede database at my work. All the data is located in an Access database and handled by a frontend in Excel (managed by ADO)
Until now I have put the data from Acces into a sheet and refered to it by the listbox rowsource property. And It worked fine in the .xls but when I create the ADD-In I can't get any data in the listbox. It is empty
Is there a simpler better way to do it?
Private Sub cbIdeData_Enter()
On Error GoTo FejlBehandler
'Åben forbindelse til Acces
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim sql As String
Dim filenm As String
sql = "Select * from IdeDB"
filenm = DBsti
Call GlobaleProcedurer.GetCn(adoconn, adors, sql, filenm, "", "")
Dim xlsht As Excel.Worksheet
Set xlsht = Workbooks("AluCoreBobleTank.xla").Sheets("Sheet3")
xlsht.Range("A1").CopyFromRecordset adors
adors.Close
adoconn.Close
Set adors = Nothing
Set adoconn = Nothing
Dim ListRange As Range
Set ListRange = Workbooks("AluCoreBobleTank.xla").Sheets("sheet3") .Range("A1", Sheet3.Range("C65000").End(xlUp))
'Initialiser Listbox
With cbIdeData
.ColumnCount = 3
.ColumnWidths = 30
.RowSource = ListRange.Address
.ListIndex = 0
End With
Set xlsht = Nothing
Exit Sub
FejlBehandler:
Select Case Err
Case -2147352571 'Der er NULL værdi i Access tabellen
Resume Next
Case -2147217913 'Der skrives NULL værdi til Access tabellen
Resume Next
Case -2147217900 'Der er ikke angivet et id nr
Exit Sub
Case Else
MsgBox ("Programmet er afsluttet pga udefineret fejl: " & Err.Number & " - " & Err.Description)
Exit Sub
End