Delete Entire Rows

[Thompyt]

G'Day,


I am trying to figure out how to delete the first 4 rows on every sheet in an excel workbook. I found the below code and added to it. I was trying to modify:

.Range("A1:A4").EntireRow.Delete

or

For lcnt =1 to 3
.rows(lcnt).entireRow.Delete
Next lCnt

I believe this would have to happen during the collection import as each sheet is imported

For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Insert code here
Next lngCount

Private Sub OpenExcel_Click()
 
Dim objExcel As Object, objWorkbook As Object, f As Object
Dim strFile As String, strFolder As String, Filled As String, strPathFile As String, strTable As String
Dim varItem As Variant
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim colWorksheets As Collection
Dim strPassword As String
 
' Establish an EXCEL application object
On Error Resume Next
    Set objExcel = GetObject(strPathFile, "Excel.Application")
        If Err.Number <> 0 Then
    Set objExcel = CreateObject("Excel.Application")
          blnEXCEL = True
        End If
Err.Clear
 
On Error GoTo 0
 
 Set f = Application.FileDialog(1)
       f.AllowMultiSelect = False
   
    If f.Show Then
        For Each varItem In f.SelectedItems
            strFile = Dir(varItem)
            strFolder = Left(varItem, Len(varItem) - Len(strFile))
        Next
    End If
 
    blnHasFieldNames = True
 
    strTable = Left(strFile, InStr(strFile, ".") - 1)
    strPathFile = strpath & strFile
   
    strPassword = vbNullString
 
    blnReadOnly = True ' open EXCEL file in read-only mode
 
    Set colWorksheets = New Collection
    Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, strPassword)
               
        For lngCount = 1 To objWorkbook.Worksheets.Count
            colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
        Next lngCount
        
        objWorkbook.Close False
    Set objWorkbook = Nothing
        If blnEXCEL = True Then objExcel.Quit
 
    Set objExcel = Nothing
 
        For lngCount = colWorksheets.Count To 1 Step -1
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, colWorksheets(lngCount) & "$"
        Next lngCount
 
    Set colWorksheets = Nothing
   
End Sub

[Micron]

If multi select is set to No, then there's no need to loop over selections?
Is colWorksheets(lngCount) providing your range argument? If so, then modify that to exclude the first 4 rows? Perhaps use a named range, or a named dynamic range in Excel.

[Thompyt]

I see what you mean and there is a range
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strPathFile, blnHasFieldNames, colWorksheets(lngCount) & "$"

TransferType,SpreadsheetType,TableName, FileName, HasFieldNames, Range, UOA

Bold is where the range would go, but I am having issues with "Object Required.The inclusive range to import is Range would be (A5,E257) where Row A5 is the first row where HasFieldNames = True

[Micron]

Debug.Print colWorksheets(lngCount) & "$" just before TransferSpreadsheet line and see what you get. Or step through the code and see what it returns. Also make sure you're using the right spreadsheetType value for your version. It might have to be 12 (?), not 9.

[Thompyt]

Debug.Print colWorksheets(lngCount) & "$" gives you the last sheet name of Spare$ and runs backwards to the first sheet tab, basically Sheet.name$. This will run through all the tabs using Row 5 as the headers:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTable, strPathFile, blnHasFieldNames, "A1:I262"

Next step, deleting second column, adding a new column named Entity, and for each item on that sheet imported put sheet.name into Entity column.

Thanks Micron

[CJ_London]

range is a range - it should look like

sheet1$A5:E257

[CJ_London]

with that level of complexity, probably easier to use sql rather than transferspreadsheet

instead of all your excel manipulation use something like

dim sqlStr as string

'get the file details

sqlstr="INSERT INTO " & strTable & " (Entity, fld1, fld2......)" & _
           " SELECT '" & strFile & "', XL.*" & _
           " FROM (SELECT fld1, fld3, fl4...... FROM [" & sheetname & "$A5:E257] AS xlData IN '" & strPathFile & "'[Excel 12.0;HDR=yes;IMEX=2;ACCDB=Yes])   AS XL;

currentdb.execute sqlStr

replace fld1 etc with your field names and sheetname with the name of your sheet. Note I missed out fld2 from the excel file because you said you wanted to delete the second column

you already have the value for strTable, strPathFile and strFile from your earlier code

If you need to determine field names use

SELECT TOP 1 * FROM (SELECT * FROM [" & sheetname & $A5:E257] AS xlData IN 'D:\Dev\ltexttest.XLSX'[Excel 12.0;HDR=No;IMEX=2;ACCDB=Yes])