vendredi 24 février 2017

Trying to delete rows in Excel worksheet from MS Access

Brand new to the website and already addicted. I've read a number of posts and found a lot of helpful info. Thanks for that. I'm not a programmer and have a fairly basic understanding of VBA. I make ask a few more questions to understand the response, but am getting to the point where I can follow the dialog.

I'm stuck on a problem that I cannot resolve. I've found a few posts that address the topic of finding and deleting lines in an Excel worksheet using VBA from MS Access (2016 is what I'm using). I'm trying to delete the first few lines of an Excel document in order to move the headers up to the first row, so that I can import it into Access. The files I'm working with always have a blank row that precedes the header row.

I copied the same file I'm calling in Access, and entered the VBA directly into Excel. The code block works like a champ in Excel as a stand alone process. When I try to run that code in Access, it fails to step into the IF Statement that assigns a value to the variable I'm using to define the range of rows to be deleted. I've stepped through the loop using a Breakpoint and watched it execute the For Loop and then skip right over the If Statement that identifies the blank row. Not sure why this loop works in Excel but not in Access. If I hard code the variable in Access, the delete function works fine. It is just the If Statement that is the issue. Not sure what else to do in order to troubleshoot or solve this.

Here is the Access code block:

    'Open Excel file before import and delete the first (i) rows so that headings are on the first row, then save and close
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Dim lngBlankRow As Long

'Open Excel and the workbook
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject("C:\Users\eblagri\Downloads\MyFile.xlsx")
Xl.Visible = True

'There is only one worksheet in this workbook
Set XlSheet = XlBook.ActiveSheet
XlSheet.Visible = xlSheetVisible
lngBlankRow = 0

'need to find the blank row and delete everything above it because the column headers are below this line
Dim row As Range

'This loop should set lngBlankRow to 10 but keeps skipping over it and going to the end of the range
For i = 1 To XlSheet.UsedRange.Rows.Count
    Set row = XlSheet.Rows(i)
    If Xl.WorksheetFunction.CountA(row) = 0 Then
        lngBlankRow = i
    End If
Next i

Debug.Print i  'this shows 287
Debug.Print lngBlankRow  'this shows 0

'when I set the value to 10, the rows are deleted as needed
'lngBlankRow = 10

'Delete Rows in the excel sheet from 1 to specified row
XlSheet.Rows("1:" & lngBlankRow).EntireRow.Delete
XlBook.SaveAs "C:\Users\eblagri\Downloads\MyFile2.xlsx"
XlBook.Close

'Clean up and end with worksheet visible on the screen
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing

Here is the Excel code:

'same declarations as Access code
Dim row As Range
Dim XlSheet As Worksheet
Set XlSheet = ActiveSheet
Dim lngBlankRow As Long
lngBlankRow = 0

'This loop works in Excel but same code in Access does not step into the If statement
For i = 1 To XlSheet.UsedRange.Rows.Count
    Set row = XlSheet.Rows(i)
    If WorksheetFunction.CountA(row) = 0 Then
        lngBlankRow = i
    End If
Next i

Debug.Print i  'this shows 287
Debug.Print lngBlankRow  'this shows 10

Aucun commentaire:

Enregistrer un commentaire