Extract/get data from Excel

[enigmaos]

I'm trying to get some data from an Excel spreadsheet. For example, I need to get all the data listed in the second column and use those data at a later time. Does anybody know how to do that?

Thanks!!!!!!

[email protected]

[Cakkie]

This code will loop through the second column of a specific worksheet. When it comes across 3 empty cells on a row, it will stop processing. This requires you to set a refference to Excel, via the Project>Refferences menu.

Dim AppXL as new Excel.Application
Dim WBXL as Excel.Workbook
Dim WSXL as Excel.Worksheet

set WBXL = AppXL.Workbooks.Open("c:\myworkbook.xls")
set WSXL = WBXL.Sheets(1)

Dim EmptyCount as Integer
Dim RowCount as Long

EmptyCount = 0
RowCount = 0

Do Until EmptyCount = 3
RowCount = RowCount + 1
myvalue = WSXL.Range("B" & RowCount)
If myvalue = "" then
' empty row
EmptyCount = EmptyCount + 1
else
EmptyCount = 0
' do processing of data here
End If
Loop




Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Cimperiali]

'Another way (but I think Cakkie solution is better) is the following:
'put a reference to Microsoft Excel 8.0 (or 9.0) object library
'two commandbutton on a form
private xlapp as Excel.Application
private xlWorkbooks as Excel.Workbooks
private xlWbook as Excel.Workbook
private xlSheet as Excel.Worksheet
Dim myXlsData as Variant
private Sub Command1_Click()
Dim intI as Integer

set xlapp = new Excel.Application
xlapp.Visible = true
xlapp.DisplayAlerts = false 'no message from excel
set xlWorkbooks = xlapp.Workbooks
xlWorkbooks.Open "c:\myxls.xls" 'here put path and name to your excel file
set xlWbook = xlWorkbooks(1)
set xlSheet = xlWbook.Worksheets(1)
Debug.print xlSheet.Name
xlSheet.Columns("B:B").Select
xlapp.Selection.Copy
myXlsData = Split(Clipboard.GetText, vbCrLf)
Clipboard.Clear
set xlSheet = nothing

set xlWbook = nothing
xlWorkbooks.Close
set xlWorkbooks = nothing
xlapp.Quit
set xlapp = nothing

End Sub
'Firstname Surname TelephoneNo
'Shane Sharrock 555 1929 1922
'Micky Mouse 152 2222 2111
private Sub Command2_Click()
Dim lngX as Long
for lngX = 0 to UBound(myXlsData)
me.print myXlsData(lngX)
next lngX
End Sub

private Sub Form_Load()
Command1.Caption = "Retrieve data"
Command2.Caption = "print Data"
End Sub





Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[Cakkie]

Nice, using the split function and stuff. My code works in VB5 and VB6, yours only in VB6, but I think yours will run faster, expecially with large amounts of data.

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Cimperiali]

Not sure of this:
I copy/paste to/from clipboard in variant, ence it may be faster for small data, but it may kill your machine on large amount...
;-)
Have a nice day, you RoadRunner


Special thanks to Lothar "the Great" Haensler, Tom Archer, Chris Eastwood, TCartwright, Bruno Paris, Dr_Micahel
and all the other wonderful people who made and make Codeguru a great place.
Come back soon, you Gurus.

The Rater

[enigmaos]

Thanks! I will try it!

[email protected]