load list box from range in excel

[Joe Patrick]

I found this thread but, for the life of me, I cannot figure out what they mean by "then go Insert | Name | Define and type a name for the range". None of the insert functions I can find have anything to do with this.



Would someone please help me out?

http://word.mvps.org/FAQs/InterDev/F...xFromXLDAO.htm

thank you so much!

[macropod]

Hi Joe,

In Excel 2007, the equivalent function is found under Formulas|Define Name

[Joe Patrick]

Thank you so much!

Unfortunately, I can't get the rest of it to work either. I suppose it may be outdated but a couple of things don't seem right to me.

-I changed 'listbox1' to 'combobox1' since that's what i'm using
-If i'm not supposed to be referring to DAO when in Excel 2007 then is this correct?
-I get runtime error 3170 - 'could not find installable ISAM

Private Sub UserForm_Initialize()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

Set db = OpenDatabase("U:\TPCentral_Too\PE_GC_list.xls", False, False, "Excel 12.0")
Set rs = db.OpenRecordset("SELECT * FROM 'PEdatabase'")

With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

ComboBox1.ColumnCount = rs.Fields.Count
ComboBox1.Column = rs.GetRows(NoOfRecords)

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

[macropod]

Hi Joe,

In what format is the file you're retrieving from - Excel 2007-10 or Excel 97-2003?
If it's Excel 2007-10 , is Access 2007 is installed on the system?
If it's Excel 2007-10 and Access 2007 is not installed on the system have you downloaded & installed the object library?
Do you have a reference set to:
Microsoft DAO 3.51 (or 3.6) Object Library?
or is to:
Microsoft Office 12.0 Access database engine Object Library?

[Joe Patrick]

I'm in Excel 2007, Access 2007 is installed. I set reference to both DAO and Access just to be safe but no go.

[macropod]

Hi Joe,

If it's an xls file, you should use the "Excel 8.0" variant.

[Joe Patrick]

I changed "Excel 12.0" to "Excel 8.0" was that all?

now i get run-time error '3450': syntax error in query. incomplete queyr clause.

seems like we're on the right track though!

[macropod]

Hi Joe,

The code from the MVP site is demonstration code only - it's not intended to be thought of as a tailor-made solution.

Presumably the error you referred to occurs on the line:
Set rs = db.OpenRecordset("SELECT * FROM 'PEdatabase'")
You need to use an SQL query that matches your workbook structure and what you're trying to extract.

[Joe Patrick]

ok, thank you!