Transferspreadsheet regardless of Tab Name

bthomas

Board Regular
Joined
Mar 4, 2008
Messages
139
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...

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.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Is the sheet name always the current month?

If it is you could use MonthName and Date or just Format to get the name.
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top