By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,943 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,943 IT Pros & Developers. It's quick & easy.

Access VBA to transfer combo box values into Access Table

100+
P: 126
Hello everyone, the following is the Access VBA code which opens an excel spreadsheet and creates combo boxes dynamically.

And whenever a user selects a value in a combo box, I am trying to pass the selected value into the corresponding cell in excel spreadsheet and then importing the excel spreadsheet into an Access Table.

But the following code is generating "Run-time error 91: Object variable or With block variable not set" error at line #18 in "Class1" class module.

1. Created the following Access VBA module:

Expand|Select|Wrap|Line Numbers
  1. Public eXL As New eventsXL
  2.  
  3. Function Create_ComboBoxes()
  4. Dim OLEObj As OLEObject, OLEObj1 As OLEObject
  5. Dim myRng As Range, myCell As Range, myRng1 As Range, myCell1 As Range
  6.  
  7. With eXL
  8. If .XL Is Nothing Then Set .XL = New Excel.Application
  9. .XL.Visible = True
  10. .XL.Interactive = True
  11. Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
  12. Set .WS = .WB.Worksheets("Example")
  13. .WS.Activate
  14.  
  15. .XL.CommandBars("Control Toolbox").Visible = False
  16. .WS.OLEObjects.Delete
  17.  
  18. Set myRng = .WS.Range("E2:E12")
  19. For Each myCell In myRng.Cells
  20. With myCell
  21.     .NumberFormat = ";;;" 'hide the value in the cell
  22.     Set OLEObj = .Parent.OLEObjects.Add _
  23.                 (ClassType:="Forms.ComboBox.1", Link:=False, _
  24.                 DisplayAsIcon:=False, _
  25.                 Top:=.Top, _
  26.                 Left:=.Left, _
  27.                 Width:=.Width, _
  28.                 Height:=.Height)
  29. End With
  30. .Abc OLEObj.Object
  31. Next myCell
  32. End With
  33. End Function
  34.  
  35. Function Export_ExtractedPolicyList()
  36. Dim excelFile As String, tableName As String
  37. excelFile = "C:\Book1.xls"
  38. tableName = "Example"
  39. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName, excelFile, True
  40. End Function
2. Created the following "eventsXL" class module:

Expand|Select|Wrap|Line Numbers
  1. Public WithEvents XL As Excel.Application
  2. Public WithEvents WB As Excel.Workbook
  3. Public WithEvents WS As Excel.Worksheet
  4. Private myObj As New VBA.Collection
  5. Private myObj1 As New VBA.Collection
  6.  
  7. Public Property Get myComboBoxes() As VBA.Collection
  8.     Set myComboBoxes = myObj
  9. End Property
  10.  
  11. Public Property Get myNewComboBoxes() As VBA.Collection
  12.     Set myNewComboBoxes = myObj1
  13. End Property
  14.  
  15. Public Function Abc(obj As Variant) As Class1
  16.     Set Abc = New Class1
  17.     Set Abc.myComboBox = obj
  18.     Me.myComboBoxes.Add Abc
  19. End Function
3. Created the following "Class1" class module:

Expand|Select|Wrap|Line Numbers
  1. Public WithEvents myOLE As MSForms.ComboBox
  2. Public objCell As eventsXL
  3.  
  4. Public Property Get myComboBox() As MSForms.ComboBox
  5. Set myComboBox = myOLE
  6. End Property
  7.  
  8. Public Property Set myComboBox(ByRef myCombo As MSForms.ComboBox)
  9. Set myOLE = myCombo
  10. myOLE.AddItem "A"
  11. myOLE.AddItem "B"
  12. myOLE.AddItem "C"
  13. myOLE.AddItem "D"
  14. End Property
  15.  
  16. Public Sub myOLE_Change()
  17. Dim Item As String
  18. objCell.XL.ActiveCell.Value = myOLE.Text
  19. End Sub
Sep 8 '08 #1
Share this Question
Share on Google+
6 Replies


Expert Mod 2.5K+
P: 2,545
Hi again JFKJr. In line 18 of Class1 you are referring to object objCell which has not been initialised, so it would look like the error message simply reflects that fact. It is public to the module, but the public declaration does not initialise the object variable at all. You will need to set objCell appropriately to resolve this.

I note that you do not use class initialise or terminate procedures, which are usually used to set and cleanup class object variables appropriately.

-Stewart
Sep 8 '08 #2

100+
P: 126
Hi again JFKJr. In line 18 of Class1 you are referring to object objCell which has not been initialised, so it would look like the error message simply reflects that fact. It is public to the module, but the public declaration does not initialise the object variable at all. You will need to set objCell appropriately to resolve this.

I note that you do not use class initialise or terminate procedures, which are usually used to set and cleanup class object variables appropriately.

-Stewart
Hello Stewart, nice to see you again.

I changed the code a little bit. The following Access VBA code opens a new excel spreadsheet and creates combo boxes dynamically in a given range of cells using "Create_ComboBoxes" proc below.

And whenever user selects a value in the combo box, the value should be inserted into the corresponding cell in which the combo box is present.

And when I run "Export_ExtractedPolicyList" proc below, the excel spreadsheet should be imported into an Access Table which stores the combo box values that user has selected.

But the following code is unable to insert the user selected combo box value into the corresponding cell. The active cell address is displaying empty at line #18 in "Class1" class module.

Please kindly let me where I am doing wrong?

Thanks.

1. Access VBA module:

Expand|Select|Wrap|Line Numbers
  1. Public eXL As New eventsXL
  2.  
  3. Function Create_ComboBoxes()
  4. Dim OLEObj As OLEObject, OLEObj1 As OLEObject
  5. Dim myRng As Range, myCell As Range, myRng1 As Range, myCell1 As Range
  6. Dim cellAddr As String, curChar As String, actualAddr As String, X As Integer
  7.  
  8. With eXL
  9. If .XL Is Nothing Then Set .XL = New Excel.Application
  10. .XL.Visible = True
  11. .XL.Interactive = True
  12. Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
  13. Set .WS = .WB.Worksheets("Example")
  14. .WS.Activate
  15.  
  16. .XL.CommandBars("Control Toolbox").Visible = False
  17. .WS.OLEObjects.Delete
  18.  
  19. Set myRng = .WS.Range("E2:E12")
  20. For Each myCell In myRng.Cells
  21. With myCell
  22.     actualAddr = " "
  23.     .NumberFormat = ";;;" 'hide the value in the cell
  24.     Set OLEObj = .Parent.OLEObjects.Add _
  25.                 (ClassType:="Forms.ComboBox.1", Link:=False, _
  26.                 DisplayAsIcon:=False, _
  27.                 Top:=.Top, _
  28.                 Left:=.Left, _
  29.                 Width:=.Width, _
  30.                 Height:=.Height)
  31. End With
  32.  
  33. cellAddr = myCell.Address
  34.  
  35. For X = 1 To Len(cellAddr)
  36.     curChar = Mid(cellAddr, X, 1)
  37.     If InStr("$", curChar) = 0 Then actualAddr = _
  38.            actualAddr & curChar
  39. Next X
  40. .Abc OLEObj.Object, actualAddr
  41. Next myCell
  42. End With
  43. End Function
  44.  
  45. Function Export_ExtractedPolicyList()
  46. Dim excelFile As String, tableName As String
  47. excelFile = "C:\Book1.xls"
  48. tableName = "Example"
  49. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName, excelFile, True
  50. End Function
  51.  
  52. Public Function ImportExcelSheet(comboValue As String, ceAddr As String)
  53. eXL.XL.Range(ceAddr).Value = comboValue
  54. End Function
2. "eventsXL" class module:

Expand|Select|Wrap|Line Numbers
  1. Public WithEvents XL As Excel.Application
  2. Public WithEvents WB As Excel.Workbook
  3. Public WithEvents WS As Excel.Worksheet
  4. Private myObj As New VBA.Collection
  5. Private myObj1 As New VBA.Collection
  6. Public cellAddress As String
  7.  
  8. Public Property Get myComboBoxes() As VBA.Collection
  9.     Set myComboBoxes = myObj
  10. End Property
  11.  
  12. Public Property Get myNewComboBoxes() As VBA.Collection
  13.     Set myNewComboBoxes = myObj1
  14. End Property
  15.  
  16. Public Function Abc(obj As Variant, cAddr As String) As Class1
  17.     cellAddress = cAddr
  18.     Set Abc = New Class1
  19.     Set Abc.myComboBox = obj
  20.     Me.myComboBoxes.Add Abc
  21. End Function
  22.  
  23. Private Sub XL_WorkbookBeforeClose(ByVal WB1 As Excel.Workbook, Cancel As Boolean)
  24. With XL.Workbooks
  25. XL.Quit
  26. Set eXL.XL = Nothing
  27. Set eXL.WB = Nothing
  28. Set eXL.WS = Nothing
  29. Set eXL = Nothing
  30. End With
  31. Exit Sub
  32. End Sub
3. "Class1" class module:

Expand|Select|Wrap|Line Numbers
  1. Public WithEvents myOLE As MSForms.ComboBox
  2. Public cXL As New eventsXL
  3.  
  4. Public Property Get myComboBox() As MSForms.ComboBox
  5. Set myComboBox = myOLE
  6. End Property
  7.  
  8. Public Property Set myComboBox(ByRef myCombo As MSForms.ComboBox)
  9. Set myOLE = myCombo
  10. myOLE.AddItem "A"
  11. myOLE.AddItem "B"
  12. myOLE.AddItem "C"
  13. myOLE.AddItem "D"
  14. End Property
  15.  
  16. Public Sub myOLE_Change()
  17. Dim Item As String
  18. MsgBox "" & cXL.cellAddress
  19. ImportExcelSheet myOLE.Text, cXL.cellAddress
  20. End Sub
Sep 9 '08 #3

Expert Mod 2.5K+
P: 2,545
Well, you have at least one circular relationship between your class modules.

In line 18 of Class1 you are referring to object cXL, which has been initialised this time in the public declaration. Its type is EventsXL. But, if you look at class module EventsXL you will find a function header at line 16 which returns a value of type Class1. Class1 cannot depend on EventsXL if EventsXL also depends on Class1.

I have not skimmed for other errors - this one is big enough I'm sorry to say. Redesign 2 is inevitable.

-Stewart
Sep 9 '08 #4

FishVal
Expert 2.5K+
P: 2,653
Well, you have at least one circular relationship between your class modules.

In line 18 of Class1 you are referring to object cXL, which has been initialised this time in the public declaration. Its type is EventsXL. But, if you look at class module EventsXL you will find a function header at line 16 which returns a value of type Class1. Class1 cannot depend on EventsXL if EventsXL also depends on Class1.

I have not skimmed for other errors - this one is big enough I'm sorry to say. Redesign 2 is inevitable.

-Stewart
Hello, Stewart.

As soon as I've taken part in code creation , I'd like to clarify situation.
  • Class eventsXL contains VBA.Collection of Class1 objects. Method eventsXL.Abc() adds an object to the collection.
  • At the same time Class1 contains reference to parent eventsXL object to ensure two-way referencing.
  • cXL was supposed to be instantiated in eventsXL.Abc() immediately after creation of Class1 object.

Unfortunately OP has done nothing to improve code styling. It is still that messed, kludgy and with bad naming as it was last time I've seen it, maybe even more. All that makes it difficult to understand.

Regards.
Fish
Sep 9 '08 #5

FishVal
Expert 2.5K+
P: 2,653
@JFKJr

Sorry, comrade.

Last time I just didn't want to spoil your triumph with those silly thoughts about code style and naming conventions. Now you have a not that long peace of code that has already messed you to say nothing about somebody seeing this first time. ;)

You code is so messed and bad styled that personally I would let it RIP and recode from scratch. Keeping in mind straight and flexible logic, helpful comments and meaningful naming.

Regards,
Fish
Sep 9 '08 #6

100+
P: 126
@JFKJr

Sorry, comrade.

Last time I just didn't want to spoil your triumph with those silly thoughts about code style and naming conventions. Now you have a not that long peace of code that has already messed you to say nothing about somebody seeing this first time. ;)

You code is so messed and bad styled that personally I would let it RIP and recode from scratch. Keeping in mind straight and flexible logic, helpful comments and meaningful naming.

Regards,
Fish
Thanks Fish for your suggestions.

I changed the following Access VBA code a little bit and it is working as I expected.

I agree that the following code is bad styled but haven't bothered much since it is working.

And regarding the naming conventions I wantedly changed the names because I don't want to reveal the actual names that I am using and I only took a part of the code which is not working and showed it to you to make it simple.

But as you said I should have come up with meaningful names and helpful comments to give you a clear idea.

Next time I will definitely come up with meaningful names.

Thank you once again for all your help.

1. Access VBA module:

Expand|Select|Wrap|Line Numbers
  1. Public eXL As New eventsXL
  2.  
  3. Function Create_ComboBoxes()
  4. Dim OLEObj As OLEObject, OLEObj1 As OLEObject
  5. Dim myRng As Range, myCell As Range, myRng1 As Range, myCell1 As Range
  6. Dim cellAddr As String, curChar As String, actualAddr As String, X As Integer
  7.  
  8. With eXL
  9. If .XL Is Nothing Then Set .XL = New Excel.Application
  10. .XL.Visible = True
  11. .XL.Interactive = True
  12. Set .WB = .XL.Workbooks.Open("C:\Book1.xls", , False)
  13. Set .WS = .WB.Worksheets("Example")
  14. .WS.Activate
  15.  
  16. .XL.CommandBars("Control Toolbox").Visible = False
  17. .WS.OLEObjects.Delete
  18.  
  19. Set myRng = .WS.Range("E2:E4")
  20. For Each myCell In myRng.Cells
  21. With myCell
  22.     actualAddr = " "
  23.     .NumberFormat = ";;;" 'hide the value in the cell
  24.     Set OLEObj = .Parent.OLEObjects.Add _
  25.                 (ClassType:="Forms.ComboBox.1", Link:=False, _
  26.                 DisplayAsIcon:=False, _
  27.                 Top:=.Top, _
  28.                 Left:=.Left, _
  29.                 Width:=.Width, _
  30.                 Height:=.Height)
  31. End With
  32.  
  33. cellAddr = myCell.Address
  34.  
  35. For X = 1 To Len(cellAddr)
  36.     curChar = Mid(cellAddr, X, 1)
  37.     If InStr("$", curChar) = 0 Then actualAddr = _
  38.            actualAddr & curChar
  39. Next X
  40. .Abc OLEObj.Object, actualAddr
  41. Next myCell
  42. End With
  43. End Function
  44.  
  45. Function Export_ExtractedPolicyList()
  46. Dim excelFile As String, tableName As String
  47. excelFile = "C:\Book1.xls"
  48. tableName = "Example"
  49. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName, excelFile, True
  50. End Function
  51.  
  52. Public Function ImportExcelSheet(comboValue As String, ceAddr As String)
  53. eXL.XL.Range(ceAddr).Value = comboValue
  54. End Function
2. "eventsXL" class module:

Expand|Select|Wrap|Line Numbers
  1. Public WithEvents XL As Excel.Application
  2. Public WithEvents WB As Excel.Workbook
  3. Public WithEvents WS As Excel.Worksheet
  4. Private myObj As New VBA.Collection
  5.  
  6. Public Property Get myComboBoxes() As VBA.Collection
  7.     Set myComboBoxes = myObj
  8. End Property
  9.  
  10. Public Function Abc(obj As Variant, cAddr As String) As Class1
  11.     Set Abc = New Class1
  12.     Set Abc.myComboBox = obj
  13.     Abc.cellAddress = cAddr
  14.     Me.myComboBoxes.Add Abc
  15. End Function
  16.  
  17. Private Sub XL_WorkbookBeforeClose(ByVal WB1 As Excel.Workbook, Cancel As Boolean)
  18. With XL.Workbooks
  19. XL.Quit
  20. Set eXL.XL = Nothing
  21. Set eXL.WB = Nothing
  22. Set eXL.WS = Nothing
  23. Set eXL = Nothing
  24. End With
  25. Exit Sub
  26. End Sub
3. "Class1" class module:

Expand|Select|Wrap|Line Numbers
  1. Public WithEvents myOLE As MSForms.ComboBox
  2. Public cellAddress As String
  3.  
  4. Public Property Get myComboBox() As MSForms.ComboBox
  5. Set myComboBox = myOLE
  6. End Property
  7.  
  8. Public Property Set myComboBox(ByRef myCombo As MSForms.ComboBox)
  9. Set myOLE = myCombo
  10. myOLE.AddItem "A"
  11. myOLE.AddItem "B"
  12. myOLE.AddItem "C"
  13. myOLE.AddItem "D"
  14. End Property
  15.  
  16. Public Sub myOLE_Change()
  17. Dim Item As String
  18. ImportExcelSheet myOLE.Text, cellAddress
  19. End Sub
Sep 10 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.