Transferspreadsheet regardless of Tab Name

[bthomas]

Hi!

I am creating an access db that users with limited Access/Excel knowledge will be using. My goal is to make everything as automated as possible.

There is an excel workbook the user has to analyze each month. Details of the data are in tab 2 (different name each month), Also the header row is not at the top of the sheet.

Is there a way to import the second tab without specifying the name and removing all lines until the header rows are at the top of the sheet?

The code I have I tried to piece meal from other places, so I know it is probably out of order. The main two goals are: importing the 2nd sheet regardless of name and removing all rows until the headers are at the top of the excel sheet before import.

Here is the code I have...

Public Function OutofAPR1()

Dim strSFAccounts As String
'Dim SFdb As Object
Dim SFdb As New Excel.Application
Dim strsql As String
Set SFdb = CreateObject("excel.application")
Dim strtble As String
Dim strpath As String
strSFAccounts = SFdb.GetOpenFilename(FileFilter:="Microsoft Excel, *.xl*", Title:="Click on Out of APR Report")
'On Error Resume Next
   
 '   strsql = "drop table [02ImportDistrictAccountList];"
 '   CurrentDb.Execute (strsql)
'On Error GoTo 0
' delete rows until the headers are in the first row
 

If strSFAccounts <> "False" Then

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Out of APR Detail", strSFAccounts, True, "[B]Excel Tab 2[/B]"
[B]For i = 1 To 5
    xlws.Rows(1).EntireRow.Delete
Next[/B]
[B]End If[/B]
CurrentDb.Execute ("OutofAPR-UpdateSoundexCodes")
On Error Resume Next
    CurrentDb.Execute (strsql)
On Error GoTo 0
End Function

Thank you so much for your help! I am using Access 2003.

 

[Norie]

Is the sheet name always the current month?

If it is you could use MonthName and Date or just Format to get the name.

strWSImportName = MonthName(Month(Date())
 
strWSImportName = Format(Date(), "mmmm")

Both would need adjusted to get the right format for the name, eg Jan/January/01 etc.

As for the rows thing, you can't do it without a little extra work.

For example you can import the data to a temporary file then delete the unneeded rows and append them to the 'real' table.