PDA

View Full Version : Userform Populated with range from another excel file



jsabo
05-27-2014, 02:21 AM
Hello,

Trying to automate the generation of templates in Word. I am creating a userform with combo boxes and I would like the combo boxes to be populated from ranges in a separate excel file, which will be closed once the variables are assigned to the ranges (without the user ever seeing the excel file).

First, the below runs from a button on a ribbon:


Sub GenerateLetter()
'
' GenerateLetter Macro
'
'

UserInput.Show


End Sub

Then, the UserInput form is shown and the code for its initialization looks like:


Private Sub UserForm_Initialize()

Dim oExcel As Object
Dim oWB As Object
Dim SCNumberRange As Range
Dim LastRow As Long

Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.Workbooks.Open("C:\Users\jsabo\Documents\Letter Generator\Admin\Subcontract_List.xlsx")
oExcel.Visible = True

With oWB

LastRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
SCNumberRange = oWB.Sheets("Data").Range("A2:A" & LastRow)

End With

End Sub

I get a runtime 424 'Object Required' error. Currently, both the userform and the module are both under the "Normal" project. Any ideas?

Bob Phillips
05-27-2014, 02:34 AM
Set SCNumberRange = oWB.Sheets("Data").Range("A2:A" & LastRow)

jsabo
05-27-2014, 04:12 AM
Hm, doesn't seem to have worked. another forum suggested "LastRow = .ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row" but that failed to work as well.

jsabo
05-27-2014, 07:40 AM
Have been working on this for hours and have refined it to:


Private Sub UserForm_Initialize()

Dim oExcel As Object
Dim oWB As Object
Dim SCNumberRange As Range
Dim LastRow As Long

Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.Workbooks.Open("C:\Users\jsabo\Documents\Letter Generator\Admin\Subcontract_List.xlsx")
oExcel.Visible = True

With oWB.Sheets("Data")

LastRow = .UsedRange.Rows.Count
Set SCNumberRange = .Range("A2:A" & LastRow)

End With

End Sub

But still have an error- this time a "Type Mismatch" error??

Bob Phillips
05-27-2014, 09:34 AM
Where is the error occurring?