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
Code:
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