Delete all rows until header line and import 2nd Sheet into Access

[bthomas]

Hi!

I am trying to import the second sheet from an excel spreadsheet. The sheet may not have the same name and there is "junk" in the top rows of the sheet.

I tried to write my own code, but nothing seems to be happening. Can someone help me?

Thanks so much!

This is my current code:

Public Function OutofAPR3()
[COLOR=yellowgreen]'object variables that will be used to get open dailogue box and get the path to the worksheet for input
[/COLOR]Dim xl As Excel.Application
Dim xlWB As Workbook
Dim xlWS As Worksheet

[COLOR=yellowgreen]' sets the transfer spreadsheet to import
[/COLOR]Const acimport = 0
[COLOR=yellowgreen]'on file import this tells access what type of sheet is being imported
[/COLOR]Const acspreadsheettypeexcel9 = 8
[COLOR=yellowgreen]'variable to be used for name and filepath of file to imported
[/COLOR]Dim strinput As String
[COLOR=yellowgreen]'create instance of excel
[/COLOR]Set xl = New Excel.Application
[COLOR=yellowgreen]'use the instance of excel to create open dialogue box and get filepath for import file
[/COLOR]strinput = xl.GetOpenFilename(FileFilter:="Excel Workbook, *.xls", Title:="Select Out of APR File")
[COLOR=yellowgreen]'if the user pushes the cancel button on the open dialogue exit the 'program
[/COLOR]If strinput = "False" Then
    Exit Function
End If
[COLOR=yellowgreen]' open the file with the name = return from open file dialogue box.  The file 'will open very slowly and then it will be resaved as
' [filename]_2 and as a standard excel file as the original is some strange 'webview file.
[/COLOR][COLOR=yellowgreen]' Changes need to be made to the file to get the headers right, these 'include deleting rows to bring the headers to the first row[/COLOR]
'open source file
Set xlWB = xl.Workbooks.Open(strinput)
Set xlws = xlWB.Sheets(2)
xlws.Activate
xlws.Select
    
  [COLOR=yellowgreen] ' delete rows until the headers are in the first row
[/COLOR]    On Error Resume Next     [COLOR=yellowgreen]' In case there are no blanks
[/COLOR]    Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

[COLOR=yellowgreen]' save and close the workbook and clean up the applications running
[/COLOR]xlWB.Save
xlWB.Close
Set xlWB = Nothing

Set xl = Nothing

[COLOR=yellowgreen]' import the new file into the input data table. Need to pull in sheet 2
[/COLOR]DoCmd.TransferSpreadsheet acimport, acspreadsheettypeexcel9, "Out of APR Detail", strnewname, True

End Function

 

[John Big Booty]

Welcome to the forum.

You might find it easier to import the Excel file into a temporary table and then clean the data from there.

Have a look at the import process in the attached DB (Copy both the Access and Excel files to the same location). Have a look at the code behind Command2 on the Form FRM_Import.