#1
|
|||
|
|||
load list box from range in excel
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! |
#2
|
||||
|
||||
Hi Joe,
In Excel 2007, the equivalent function is found under Formulas|Define Name
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
thank you, macropod!
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 |
#4
|
||||
|
||||
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?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
I'm in Excel 2007, Access 2007 is installed. I set reference to both DAO and Access just to be safe but no go.
|
#6
|
||||
|
||||
Hi Joe,
If it's an xls file, you should use the "Excel 8.0" variant.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
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! |
#8
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
ok, thank you!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
list of Opened excel files | SDondeti | Word VBA | 10 | 05-10-2018 03:52 PM |
Excel - move with tab through named range | mjlaw | Excel | 4 | 03-26-2012 10:40 AM |
Searchable list in excel? | leahca | Excel | 7 | 11-24-2011 04:44 AM |
PowerPoint Add-in fails to load in start up but loads successfully if load manually f | shahbazlibra | PowerPoint | 0 | 11-30-2010 08:36 AM |
Through VBA, export range from Excel to Word | duugg | Word VBA | 0 | 08-24-2009 07:50 PM |