Importing Excel Spreadsheet to Access

[whatalulu]

I need to import 4 spreadsheets into access. The data area in each spreadsheet begins on a different row.

I have found the VBA code to import a spreadsheet but it starts with row one.

Can I open an external table and examine the record before importing it? Can I read on record at a time? I cannot find the answer.

 

[nickjar2]

I got this from the help file. I have used it before and it works pretty well.

Dim MyChar
Open "TESTFILE" For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
MyChar = Input(1, #1) ' Get one character.
Debug.Print MyChar ' Print to Debug window.
Loop
Close #1 ' Close file.

Just tweak it to match your requirements.

Nick

 

[whatalulu]

This is how I finally solved the probelm
1. open excel
2. Find the first field name in the block of data (database)
3. Select the database range
4. Name the database range
5. Transfer the spreadsheet using the database range name.

Function OpenExcel()
Dim objExcel2 As Excel.Application
Set objExcel2 = New Excel.Application
objExcel2.Visible = True
Workbooks.Open Filename:="C:\My Documents\test.xls"
Cells.Find(What:="first field name", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Selection.CurrentRegion.Select
Selection.Name = "database"
DoCmd.transferspreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="tbltest", _
Filename:="C:\My Documents\test.xls", _
HasFieldNames:=True, Range:="database"
ActiveWorkbook.Close savechanges:=False
End Function