linking xls with access

[bob]

I am trying to figure out the best way to do this. I keep
sales reports in excel on a monthly basis (I have 4 years
worth of data). Eack month I create a new sheet. I want to
be able to have this data move automatically into an
access database so I can use it for future reports and
analysis. I want to be able to move the current dta in and
then have all the future data added automatically. I am
not an expert with either program however I can work in
them fairly well. Any suggestions?

 

[Joe Fallon]

1. Import the data each month to an Access table.
Then run reports off that table.

2. Build an app in Access and get rid of Excel for data entry. Just use
Access.

 

[Guest]

Thank you. I set up a table for importing but when I go to
import, the "import to an existing table" is greyed out.
???

 

[Joe Fallon]

Use TransferText.

Sample code:

Public Sub ImportSpreadsheet(strTable As String, strFileName As String, _
strWorkSheet As String, strRange As String)
'Purpose: to import a specific sheet and range of a workbook
'Usage: ImportSpreadsheet
"tblExcel","c:\temp\Book1.xls","Sheet1","C57"
'True in the options means the top row are field headers
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
strTable, strFileName, True, strWorkSheet & "!" & strRange
End Sub