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...
Thank you so much for your help! I am using Access 2003.
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...
Code:
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.