rrenis
10-18-2007, 02:20 AM
Hi all - I have the following code which populates a userform (which is in Outlook) from a the data stored in an excel spreadsheet. The sheet numbers for the lookup are assigned by an Integer based upon a user's selection elsewhere on the userform (ComboBox1)...
Dim objXLApp As Excel.Application
Dim objXLwb As Excel.Workbook
Set objXLApp = New Excel.Application
ClearTextBoxes ' This Sub clears Textboxes 1 to 7 using .text = ""
With objXLApp
Set objXLwb = .Workbooks.Open("C:\Support\Database.xls", ReadOnly:=True)
TextBox6.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("B1:B300"))
TextBox1.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("C1:C300"))
TextBox3.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("D1:D300"))
TextBox5.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("E1:E300"))
TextBox4.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("F1:F300"))
TextBox2.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("G1:G300"))
TextBox7.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("H1:H300"))
End With
objXLwb.Close False
objXLApp.Quit
Set objXLwb = Nothing
Set objXLApp = Nothing
The problem is that sometimes this works fine. Other times it errors on the first lookup (B1:B300). Not sure if this should make a difference or not but the rows aren't populated down to row 300 - this is just to save me altering the code each time a new row is added. The database.xls is sorted on exit so the data is always alphabetical on every sheets, so I don't think that's causing the problem.
Can anyone see anything that's wrong with the code (it's strange as sometimes it works fine :think: ) or possibly suggest a better way of doing this.
Cheers,
rrenis.
Dim objXLApp As Excel.Application
Dim objXLwb As Excel.Workbook
Set objXLApp = New Excel.Application
ClearTextBoxes ' This Sub clears Textboxes 1 to 7 using .text = ""
With objXLApp
Set objXLwb = .Workbooks.Open("C:\Support\Database.xls", ReadOnly:=True)
TextBox6.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("B1:B300"))
TextBox1.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("C1:C300"))
TextBox3.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("D1:D300"))
TextBox5.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("E1:E300"))
TextBox4.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("F1:F300"))
TextBox2.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("G1:G300"))
TextBox7.Value = objXLApp.Lookup(ComboBox1.Value, Worksheets(SheetNumber).Range("A1:A300"), Worksheets(SheetNumber).Range("H1:H300"))
End With
objXLwb.Close False
objXLApp.Quit
Set objXLwb = Nothing
Set objXLApp = Nothing
The problem is that sometimes this works fine. Other times it errors on the first lookup (B1:B300). Not sure if this should make a difference or not but the rows aren't populated down to row 300 - this is just to save me altering the code each time a new row is added. The database.xls is sorted on exit so the data is always alphabetical on every sheets, so I don't think that's causing the problem.
Can anyone see anything that's wrong with the code (it's strange as sometimes it works fine :think: ) or possibly suggest a better way of doing this.
Cheers,
rrenis.