import specific rows from an excel spreadsheet 
Author Message
 import specific rows from an excel spreadsheet

i'd like to import specific rows from an excel spreadsheet using VBA (visual
basic, actually).
can someone help?

Mon, 16 Aug 2004 02:35:29 GMT  
 import specific rows from an excel spreadsheet
i've gotten started with the code below.
looking for something more elegant.
maybe not?

Private Sub cmdImportSpreadSheet_Click()

Dim oXLApp As Excel.application
Dim oXLWBook As Excel.Workbook
Dim oXLWSheet As Excel.Worksheet

Set oXLWBook = GetObject("c:\develop\davidswan\NHANES90129_Column.xls")
Set oXLWSheet = oXLWBook.Worksheets(2)

MsgBox oXLWSheet.Cells(13, 2).Value

Set oXLWBook = Nothing
Set oXLWSheet = Nothing

End Sub

Mon, 16 Aug 2004 03:42:36 GMT  
 import specific rows from an excel spreadsheet

Depending on what you want to do with the Excel data once you've brought it
into your VB or VBA app, you may want to consider opening it as an ADO
Recordset instead. Depends whether you're more comfortable working with the
Automation techniques that you're already testing, or with ADO methods.

The articles below have lots of Excel-specific pointers...


Using ADO with Excel: Resources and Known Issues
February 18, 2002

Microsoft Knowledge Base articles can be found at:
Choose to search by "specific article ID number"
Enter the article number including the "Q"

Q306022 HOW TO: Transfer Data to an Excel Workbook Using Visual Basic .NET
Q306572 HOW TO: Query and Display Excel Data Using ASP.NET, ADO.NET
Q307021 HOW TO: Transfer XML Data to Microsoft Excel 2002 with VB .NET
Q311731 HOW TO: Query and Display Excel Data Using ADO.NET, VB .NET
Q316756 PRB: Error Using ADO.Net OleDbDataAdapter to Modify Excel Workbook
Q316831 PRB: Cannot Configure Data Connections to Excel Files (pending)
Q316934 HOW TO: Use ADO.Net to Retrieve and Modify Records in an Excel
Workbook (pending)
Q317759 HOW TO: Display Data in Excel Using ASP.NET WebForm (pending)
Q318373 HOW TO: Use GetOleDbSchemaTable with Excel in Visual Basic .Net

Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
Q278973 SAMPLE: ExcelADO Shows How to Read/Write Data in Excel Workbooks
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
Q306397 INFO: Use Excel with SQL Server Linked Servers and Distributed

Known Issues
Q189897 XL97: Data Truncated to 255 Characters with Excel ODBC Driver (Rows
To Scan)
Q211378 XL2000: 'Could Not Decrypt File' Error with Password-Protected File
Q246167 PRB: Collating Sequence Error Opening XLS as ADO Recordset
Q288343 BUG: Excel ODBC Driver Disregards FirstRowHasNames/HDR Setting
Q281517 PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error (DTS
& Excel)
Q293828 BUG: Excel File Size Grows When You Edit ADO Recordset
Q294410 ACC2002: Nulls Replaced w/ Next Field's Data Exporting to Excel
Q300948 BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets
Q314763 FIX: ADO Inserts Data into Wrong Excel Columns
Q316809 BUG: No ADO Connection Error When Excel Workbook Is Not Found

Transferring Data into Excel
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
Q246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation

Other How-To Subjects
Q244761 XL2000: How to Use ADO to Return Data to a ListBox or ComboBox
Q285891 HOWTO: Use VB or ASP to Create an XML Spreadsheet for Excel 2002
Q276488 HOWTO: Use ADODB.Stream to Read Binary Files to the Browser
Q199841 HOWTO: Display ASP Results Using Excel in IE with MIME Types
Q278976 HOWTO: Transform Excel XML Spreadsheet for Server-Side Use
Q302094 HOWTO: Fill/Retrieve Excel Data Using Arrays From VB .Net
Q307063 HOW TO: Write Binary Files to the Browser Using ASP.NET & VB

Known Issues Working within Excel
Q263498 BUG: Run-Time Error 5 Using Add Method of QueryTables Collection
Q215154 XL2000: Excel does not support OLE DB Data Links
Q228633 OFF2000: "Catastrophic Failure" Error Running Samples.xls Macro
Q291199 XL2002: "Invalid Use of New Keyword" Error Using ADODB Library
Q225059 XL2000: "Invalid Use of New Keyword" Error Using ADODB Library

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit

Wed, 18 Aug 2004 03:27:14 GMT  
 import specific rows from an excel spreadsheet

Here's a fairly clean example of "How To" dig around an get stuff from a

I was visiting a client the other day an wrote this {*filter*} for them to dump
an XLS into a Public array which they then placed in assorted product
tables. You have to think of the spreadsheet as a big ugly array and program
from that viewpoint. Here's the code (Access 97). I kept this on a floppy
which is why I happen to have it handy. Note that when you dump into the Fx
array only values are contained therein from the spreadsheet (no formula).
The interesting thing about a worksheet is that its presented to the VBA
without having to do any read/write logic. Its sort of "Open Presented"
which makes it a breeze to work with.


Public Fx(1000,20)                              'array stack wanted
elsewhere in app
Public CxStart, CxStop as Long           'row to start and number of columns
in row from elsewhere

Function WorkBook()                         'Reads Excel spreadsheet and
stack into array
Dim MyWb As WorkBook
Dim MyWs As Worksheet
Dim Cx, Cy As Long               'cx=row cy=column   ex: a1=1, a12=12, b2 =2
thus 1,2 would read row 1, column 2

Erase Fx                                             'public array someplace
in access

Set MyWb = Workbooks.Open(filename:="c:\March\catalog2.xls")    'Worksheet
dir you will have to change
Set MyWs = MyWb.Worksheets(1)
'first ws; could pass filename and ws number

For Cx = CxStart To CxStop                        'first row and last
For Cy = 1 To CxCount                                'columns wide
Fx(Cx, Cy) = MyWs.Cells(Cx, Cy).Value     'This array(ing) is sort of messy,
but memory is cheap
Next Cy                                                        'cycle on
Next Cx                                                        'cycle on row
MyWb.Close                                                'bye; not really
needed, but leave tidy
End Function                                                 'like I said,
big ugly array



Wed, 18 Aug 2004 11:56:51 GMT  
 [ 4 post ] 

 Relevant Pages 

1. referencing a specific Excel spreadsheet from Word

2. exporting contents of a textbox into a specific cell in an excel spreadsheet

3. Import and Excel Spreadsheet to 4 Tables

4. Import an Excel Spreadsheet and Update 4 related tables

5. Importing an Excel Spreadsheet in Access 2

6. importing Excel Spreadsheets using code?

7. Problem importing Excel spreadsheets

8. Importing Excel spreadsheet

9. Importing Excel SpreadSheet(w/TABS) into Access Table

10. Importing/Exporting data from a custom Outlook 97 Form into an Excel Spreadsheet

11. Import excel spreadsheet to claendar using a macro

12. Importing an Excel spreadsheet


Powered by phpBB® Forum Software