Adding Excel data to an existing tbl

[smithd14]

I am trying to choose an excel file with file picker.
Then insert a recordset into an already existing table in Access from the Excel file.

The first Column in Excel has the Field Names of the access table and the second row has the information.

The below code is what I have so far, I am pretty green at programming with Access VBA, but have given it a shot. If there are gross conceptual errors, I apologize in advance.

I am running into an error in the loop once it starts. Possibly due to an empty cell in excel??

Sub AddPIS()

Dim rstOPAL As DAO.Recordset
Dim dbs As DAO.Database
Dim xlapp As Object
Dim wbk As Object
Dim wks As Object
Set xlapp = CreateObject("Excel.Application")
Dim PISpathname As Variant
Set dbs = CurrentDb
Set PIS = Application.FileDialog(3)

With PIS
.Filters.Add ".xls", "*.xls"
.Title = "Select Personal Information Sheet"
.InitialFileName = ""
.Show
End With

PISpathname = PIS.SelectedItems(1)

MsgBox "The path is: " & PISpathname

Set wbk = xlapp.Workbooks.Open(PISpathname)
Set wks = xlapp.Worksheets("Personal Information")

strow = 4
mxrow = 61
stcol = 2

Set rstOPAL = dbs.OpenRecordset("tbl Officer Prospecting Applicant Log", dbOpenDynaset)
rstOPAL.AddNew

Do Until strow > mxrow

rstOPAL.Fields(wks.cells(strow, 1)) = wks.cells(strow, 2)
strcomment = strcomment & " " & wks.cells(strow, 3)

strow = strow + 1

Loop

wbk.Close
rstOPAL.Close

Set rstOPAL = Nothing
Set wks = Nothing
Set wbk = Nothing
Set xlapp = Nothing
Set dbs = Nothing
Set PIS = Nothing
End Sub

 

[DCrake]

Why don't you simply link the spreadsheet to access and use an append query?

David

 

[smithd14]

They pathname of the file and info in the .xls will change everytime, so I dont think I can link the spreadsheet without the file picker?

 

[DCrake]

Why will the path to the location of the spreadsheet change everytime? how is it getting to that location? Also the fact that the data changes is why you link it. It is so that every time you vie the linked spreadsheet the data is uptodate.

David

 

[smithd14]

Yes the spreadsheet is a tool used to see if sales leads are qualified, so the spreadsheets will be used by multiple people from different computers. I would like to upload all those individual spreadsheets without manual data entry into the master database.