0

I am trying to prep an Excel file for import.
I used the macro recorder to get the code to remove the header and shift the rows up for my column headers for my temp table in Access.

I am trying to find a specific value in a column, then delete everything below it.

Public Function FormatSPExcel()

    Dim xl As Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim strfile As String
    Dim r As Range ' range variable
    Dim d As String ' rows to delete variable

    Set fd = Application.FileDialog(3)
    
    With fd
        .allowmultiselect = False
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls*"
        If .Show Then
            strfile = .selecteditems(1)
        End If
    End With
        
    Set xl = New Excel.Application
    Set wb = xl.Workbooks.Open(strfile)
    Set ws = wb.Sheets("Sheet0")
    
    'deletes and cleans up header
    ws.Shapes.Range(Array("Picture 1")).Select
    Selection.Delete
    Rows("1:21").Select
    Selection.Delete Shift:=xlUp
                
    'delete footer up to and including total
    Set r = ws.Range("A:K").Find("Total", lookin:=xlValues)
    
    Do While Not r Is Nothing
        d = "A:K" & r.Row + 30
        ws.Range(d).Delete xlShiftUp
        Set r = ws.Range("A:K").Find("Total", lookin:=xlValues)
    Loop
                
    wb.Save
    xl.Quit
    Set xl = Nothing
            
End Function

This fails at ActiveSheet.Range(d).Delete xlShiftUp and says

Application-defined or object-defined error

I cobbled this together from various things across StackOverflow and other sites. It is finding the row but I think I did the delete part wrong.

I want to find the row with a specific value, in this case "Total" then store as string to delete that row and a large chunk of rows below it.

Community
  • 1
  • 1
Tmyers
  • 93
  • 5
  • You need a number after "A" in the range. – Warcupine Nov 09 '20 at 16:20
  • Don't use global Excel objects like Selection or ActiveSheet. Only fully qualified local variables like ws. Otherwise your code will only work once. -- Avoid using .Select at all. – Andre Nov 09 '20 at 17:02

1 Answers1

0

When you are programming in Excel and write ActiveSheet, the VBA compile will silently translate this into Application.ActiveSheet. Similar, Workbooks is translated to Application.Workbooks , and this is done for a lot of other members of the Application-object.

When you are programming in Access, there is also an Application-object, but this time it's an Access Application that has different members, the CurrentDb is one of them.

If you are in Access and want to use the members of the Excel.Application object, simply use your Excel application object (you named it xl in your function), so write for example xl.ActiveSheet.

However, is your case it's even not neccessary to use ActiveSheet at all as you have the worksheet assigned to a variable (ws). It's much better to write ws.Range(d).Delete xlShiftUp.

FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • Maybe include the fact that `d = "A:K" & r.Row + 30` doesn't produce a valid range reference too? – BigBen Nov 09 '20 at 17:27
  • I just ran it again making the change to ws, but you are correct. in that its not producing a valid range. Now I just need to figure out what I did incorrectly in that. – Tmyers Nov 09 '20 at 17:29
  • 1
    Yes, and there are other issues, eg. using `rows` instead of `ws.rows`. @Timyers: Read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1 – FunThomas Nov 09 '20 at 17:30
  • The delete header part I just used the macro recorder. I should probably clean it up though. – Tmyers Nov 09 '20 at 17:41