Import Excel data to simple array in VB 5

Dewey · Apr 21, 2000

Elementary but i am trying to do the following: From an Excel 2000 file "c:\reports\march00.worksheet01.xls", I need to load into a simple array in VB6 the grid of "A53:G78" so that I can strip some data out. I am somewhat lost and confused. I can create a connection string using the ADODC control but can't get an SQL statement to work to access the worksheet ( this might be my fault and pure syntax). I think using simple ADO access would be easier.  I am ready for a primer in it.  Any takers, Thanks in advance....Dewey Dewey Davis <a href=mailto:ddavis@dfn.com>ddavis@dfn.com</a> <a href= > </a>

DougP · Apr 22, 2000

So let have a look at your SQL... DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a> Ask me how Bar-codes can help you be more productive.

Dewey · Apr 23, 2000

I have tried many like: SELECT [A53:G78] FROM "c:\Reports\March00.Worksheet01.xls" Dewey Davis <a href=mailto:ddavis@dfn.com>ddavis@dfn.com</a> <a href= > </a>

NoHandle · Apr 24, 2000

Create a reference to the Excel Object Library (use Project:References on the VB menu bar) and then in your code:     Dim i As Integer, j As Integer Dim aryExcel() Dim appExcel As Excel.Application Set appExcel = New Excel.Application ReDim aryExcel(7, 26) appExcel.Workbooks.Open "c:\reports\march00.worksheet01.xls" For i = 1 To 7     For j = 53 To 78         aryExcel(i, j - 52) = appExcel.Range(Chr(i + 64) &).Value     Next j Next i

Dewey · Apr 24, 2000

Nohandle: Thanks for the response.  I tried the following:Option Explicit Private Sub Command1_Click() Dim i As Integer, j As Integer Dim aryExcel() Dim appExcel As Excel.Application Set appExcel = New Excel.Application ReDim aryExcel(7, 26) appExcel.Workbooks.Open "c:\reports\march00.worksheet01.xls" For i = 1 To 7     For j = 53 To 78         aryExcel(i, j - 52) = appExcel.Range(Chr(i + 64)).Value     Next j Next i End Sub I keep gettting a workbook not found message.  I know its there and spelled correctly.  Also I don't understand the "&" on the 4th from bottom line of code. I keep getting a syntax error there also. Thanks again for the help this far..............Dewey Dewey Davis <a href=mailto:ddavis@dfn.com>ddavis@dfn.com</a> <a href= > </a>

NoHandle · Apr 25, 2000

Sorry - my typo.... It should read : aryExcel(i, j - 52) = appExcel.Range(Chr(i + 64) & j).Value The Chr converts the digit i to its equivalent letter (1 becomes a, 2 becomes b, etc) so the Range is acting on a Cell reference (a1, b2, etc). When i is 1 and j is 53 you have: appExcel.Range(a53).Value

Dewey · Apr 25, 2000

NoHandle(one more time) I edited the typo, but I get the VB error '1004' when I try to run. It says that the file "c:\reports\march00.worksheet01.xls" can not be found. I  Know it is there and spelled correctly.  I understand your code now, just can't figure out why it doesn't see the file.( I have set the References to Excel Object Library 9.0) Thanks again.... Dewey Dewey Davis <a href=mailto:ddavis@dfn.com>ddavis@dfn.com</a> <a href= > </a>

NoHandle · Apr 27, 2000

Sorry, I can't help you. If I correctly name my Excel 2000 spreadsheet file, the code works; if I spell it incorrectly, it doesn't (with Error 1004 as you got). I'm also using the Excel Object Library 9.0 I also should have said that you need to close the appexcel object and remove references to it once you have finished as follows:     appExcel.Workbooks.Close     Set appExcel = Nothing

Dewey · Apr 27, 2000

NoHandle, thanks again. I finally dropped the ".worksheet01" out of the workbook name, which then gave me: appExcel.Workbooks.Open "c:\reports\march00.xls" It now accesses the workbook and I need to figure out code to address each worksheet in the workbook,( there are 31 that I need to loop through).  I'm still working on it. Got a be a way.   Thought this might interest you. Any more ideas, don't hesitate to let me know. Thanks, Dewey Dewey Davis <a href=mailto:ddavis@dfn.com>ddavis@dfn.com</a> <a href= > </a>

lordhuh · Apr 27, 2000

Try appExcel.workbook(x) there may or may not be stuff after that to open it but i know it is something along that lines Karl Pietri <a href=mailto:lordhuh.pota.to>lordhuh.pota.to</a> <a href= > </a>

dsi · Apr 27, 2000

To cycle through the worksheets, you may want to set up an object reference to the sheet. Set ds = appExcel.Worksheets("Sheet1&quot

Then, the info will be read in much faster. aryExcel(i, j - 52) = ds.Range(Chr(i + 64) & j).Value You can cycle through the sheets: For i = 1 to 10      Set ds = appExcel.Worksheets("Sheet" & i)      'Do your stuff here Next i or you can do something like this: For Each w In Worksheets      aryExcel(i, j - 52) = w.Range(Chr(i + 64) & j).Value Next w It all depends on your setup. Hope this helps a bit!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Import Excel data to simple array in VB 5

Dewey

Programmer

DougP

MIS

Dewey

Programmer

NoHandle

MIS

Dewey

Programmer

NoHandle

MIS

Dewey

Programmer

NoHandle

MIS

Dewey

Programmer

lordhuh

Programmer

dsi

Programmer

Similar threads

Part and Inventory Search

Sponsor