Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-25-2011, 09:37 AM
Joe Patrick Joe Patrick is offline load list box from range in excel Windows 7 64bit load list box from range in excel Office 2007
Advanced Beginner
load list box from range in excel
 
Join Date: May 2011
Posts: 33
Joe Patrick is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 05-26-2011, 12:25 AM
macropod's Avatar
macropod macropod is online now load list box from range in excel Windows 7 32bit load list box from range in excel Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,862
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

Hi Joe,

In Excel 2007, the equivalent function is found under Formulas|Define Name
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 05-26-2011, 07:54 AM
Joe Patrick Joe Patrick is offline load list box from range in excel Windows 7 64bit load list box from range in excel Office 2007
Advanced Beginner
load list box from range in excel
 
Join Date: May 2011
Posts: 33
Joe Patrick is on a distinguished road
Default 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
Reply With Quote
  #4  
Old 05-26-2011, 04:05 PM
macropod's Avatar
macropod macropod is online now load list box from range in excel Windows 7 32bit load list box from range in excel Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,862
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

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
[MS MVP - Word]
Reply With Quote
  #5  
Old 05-27-2011, 05:35 AM
Joe Patrick Joe Patrick is offline load list box from range in excel Windows 7 64bit load list box from range in excel Office 2007
Advanced Beginner
load list box from range in excel
 
Join Date: May 2011
Posts: 33
Joe Patrick is on a distinguished road
Default

I'm in Excel 2007, Access 2007 is installed. I set reference to both DAO and Access just to be safe but no go.
Reply With Quote
  #6  
Old 05-27-2011, 02:54 PM
macropod's Avatar
macropod macropod is online now load list box from range in excel Windows 7 32bit load list box from range in excel Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,862
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

Hi Joe,

If it's an xls file, you should use the "Excel 8.0" variant.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 06-02-2011, 04:21 PM
Joe Patrick Joe Patrick is offline load list box from range in excel Windows 7 64bit load list box from range in excel Office 2007
Advanced Beginner
load list box from range in excel
 
Join Date: May 2011
Posts: 33
Joe Patrick is on a distinguished road
Default

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!
Reply With Quote
  #8  
Old 06-02-2011, 04:52 PM
macropod's Avatar
macropod macropod is online now load list box from range in excel Windows 7 32bit load list box from range in excel Office 2007
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,862
macropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud ofmacropod has much to be proud of
Default

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
[MS MVP - Word]
Reply With Quote
  #9  
Old 06-07-2011, 09:31 AM
Joe Patrick Joe Patrick is offline load list box from range in excel Windows 7 64bit load list box from range in excel Office 2007
Advanced Beginner
load list box from range in excel
 
Join Date: May 2011
Posts: 33
Joe Patrick is on a distinguished road
Default

ok, thank you!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
load list box from range in excel 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


All times are GMT -7. The time now is 04:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft