Excel help

[lhorton]

Can anytone give me source code on how to...
Open an excel application (c:\inventory.xls)
Define the columns, in hopes of moving the correct cells to the corresponding fields in access.
Move cells to access, row by row
Do this until the EOF is reached
Close excel

Thank you!

[Iouri]

OPen Excel

Dim ApExcel As Object 'To open Excel

Set ApExcel = CreateObject("Excel.application") 'Creates an object
ApExcel.Visible = True ' So you can see Excel
ApExcel.Workbooks.Add 'Adds a new book.
ApExcel.cells(1, 1).Formula = "HELLO" 'Add Text to a Cell
'You can use the line above, changing coordenates to go to any
'cell and you can also add Formulas
ApExcel.Range("A1:Z1").BORDERS.Color = RGB(0, 0, 0) 'Use it to
'change the borders.
ApExcel.Columns("A:AY").EntireColumn.AutoFit 'To adjust the
'column's width.
ApExcel.Range("A:Z").Select 'To establish a selection
ApExcel.Selection.NumberFormat = "0" 'Adding different formats


Enter to Access
Set a reference to the Excel Object Library in Project-References.

Dim oApp As Excel.Application
Dim oSheet As Excel.Worksheet
Dim iRow As Integer
Dim iCol As Integer
Dim first_row as integer
Dim first_col as integer
Dim num_rows as integer
Dim num_cols as integer

Set oApp = GetObject(, "Excel.Application")

Set oSheet = oApp.ActiveSheet


'to find how many rows and cols
' Get and display the bounds.
first_row = oSheet.UsedRange.Row
first_col = oSheet.UsedRange.Column
num_rows = oSheet.UsedRange.Rows.Count
num_cols = oSheet.UsedRange.Columns.Count



For iRow = first_row To num_rows -1
For iCol = first_col To num_cols -1
'here is yor code to enter the cell value to the Access table
Debug.Print oSheet.Cells(iRow, iCol)
Next iCol
Next iRow

Set oSheet = Nothing
Set oApp = Nothing

This will loop through all cells of the active worksheet.



Iouri Boutchkine
[email protected]