Import Excel Workbook

[Question Boy]

I am needing to import a series of Excel workbooks with multiple worksheets
(each sheet has pertinent data).

I looked at the External Data -- Import

But it look for a header and my sheets are not 'flat'... Cell A11 has the
client name, A12,13,14 have the Client Address, A22 Modele... You get the
picture. What is the best technique for importing the data? Is VBA the way
to go in this case?

Thank you,

QB

 

[Jeanette Cunningham]

Question Boy,
here is some code that will import data from specific cells in excel.
I can't remember where I got this code.
----------------------------------
Public Function fAddRecordToAccess()

Dim ws As Workspace
Dim db As DAO.Database
Dim strSQL As String
Dim intBPID As Integer
Dim strAcct As String
Dim strCSR As String
Dim intQA As Integer
Dim intScore As Integer
Dim dtmDate As Date

intBPID = Sheet1.Cells(1, 4).Value
strAcct = Sheet1.Cells(4, 4).Value
strCSR = Sheet1.Cells(4, 6).Value
intQA = Sheet1.Cells(6, 12).Value
intScore = Sheet1.Cells(2, 12).Value
dtmDate = Sheet1.Cells(4, 11).Value

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("S:\Training Team\Training
Analyst\Testing\Import Test.mdb", , False, True)

strSQL = "INSERT INTO tblTest VALUES (" _
& intBPID & ",'" & strAcct & "','" _
& strCSR & "'," & intQA & "," & intScore _
& ",#" & Format(dtmDate, "mm/dd/yyyy") _
& "#);"

db.Execute strSQL, dbFailOnError

Set ws = Nothing
Set db = Nothing
Set rs = Nothing

End Function