How to delete rows after importing from Excel

[mill97]

Hi I weekly import an excel spread sheet into access but i dont need the top 4 rows. So for this is what i have written

Public Sub Command1_Click()
DoCmd.TransferSpreadsheet acImport, 9, "Training", "...Adls Roster\Training.xlsx", True
Rows("1:4").Select
Selection.Delete Shift:=xlUp

But i keep getting "Complie error: Sub or functions not defined"

what am i doing wrong

 

[vbajock]

Function DelFirst4Rows()

Dim sSql As String

sSql = "DELETE theTableName.* FROM (SELECT TOP 4 * FROM theTableName) AS theTableName1"

CurrentProject.Connection.Execute sSql

End Function

 

[dhookom]

I would never run a delete query on the TOP 4 if there isn't an ORDER BY clause. Records in a table are like marbles in a bag. You never can rely on order.

I would define a named range in Excel and use it when importing. The other possibility is to import into a table that contains an autonumber. Then you might be able to delete the top 4 records when ordered by the autonumber.

Duane
Hook'D on Access
MS Access MVP

 

[mill97]

Ok here is what’s going on... before when I would download my spreadsheet off of the web page they left a few spaces blank in columns 1 and 2 starting at row 6, and my co-worker wrote the program based on that. Now he is gone and they changed their spreadsheet and I no longer need to fill in any blank spaces in column 1 or 2, however just deleting some of the code did not work. How can I change this so it works again?

Private Sub cmdImport_Click()
Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.Workbooks.Open (CurrentProject.Path & "\training.xlsx")
convertXls (excelApp.Workbooks.Item(1).Worksheets.Item(1))
excelApp.ActiveWorkbook.Close True
Set excelApp = Nothing

Dim dbsTemp As Database
Dim tdfLinked As TableDef

Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateTableDef("tblSheet1")

tdfLinked.Connect = _
"Excel 12.0;HDR=YES;IMEX=2;DATABASE=" & CurrentProject.Path & "\training.xlsx"

tdfLinked.SourceTableName = "Sheet1$"
DoCmd.RunSQL "SELECT * INTO tblData FROM tblSheet1"
End Sub

Private Sub convertXls(xls)
Dim curName, curEmp
Dim row, col
Dim finished
Dim xlsDate
finished = False
row = 6
col = 1
Do While finished = False
curName = xls.Cells(row, col)
curEmp = xls.Cells(row, col + 1)
xls.Cells(row, col + 1).Clear
xls.Cells(row, col + 1).NumberFormat = "00000"
xls.Cells(row, col + 1) = curEmp
row = row + 1
Do While IsEmpty(xls.Cells(row, col)) = True
If IsEmpty(xls.Cells(row, col + 2)) = True Then
finished = True
Exit Do
End If
xls.Cells(row, col) = curName
xls.Cells(row, col + 1) = curEmp
xls.Cells(row, col + 1).NumberFormat = "00000"
row = row + 1
Loop
Loop
xlsDate = Format(Date, "d-mmm-yyyy")
DoCmd.Close acTable, "tblDate"
MsgBox xlsDate
MsgBox "UPDATE tblDate SET date='" & xlsDate & "';"
DoCmd.RunSQL "UPDATE [tblDate] SET [date]='" & xlsDate & "';"
xls.Range("1:4").Delete
End Sub