Import/Export to Excel

[Guest]

I'm trying to create a table that I can have my users export and import from
an existing excel spreadsheet. I have a spreadsheet already formatted that
the users use to manipulate data but I want to be able to import this into a
table so I can run queries and reports from it. What would be the easiest way
to go about this?

 

[Danny J. Lesandrini]

I recently posted some articles on Import and Export to Excel at DBJ ...

http://www.databasejournal.com/features/msaccess/article.php/3563671
http://www.databasejournal.com/features/msaccess/article.php/3557541

 

[Guest]

Hi Dan,
Thanks for the links. I do have one question though. How would this work if
I wanted to append this data to an existing workbook and also to certain rows
in this workbook?

 

[Danny J. Lesandrini]

If you know the rows, it's easy. I think my code uses an iRow variable
which tells the method where to insert the data. I'm not sure if the code
I published includes a function to return the LAST USED ROW of the
spreadsheet, but it's a simple matter to determine. You would then set
the iRow variable to that value. Something like this ...

Dim sData As String
sData = wks.UsedRange.Address
iLastRow = CInt(Mid(sData, InStrRev(sData,"$")))

For iRow = iLastRow To iTotalRecords
wks.Cells(iRow, iCol) = sValue
Next