Delete Junk First Row When Importing Linked Table

[Joel Marion]

I have a linked table that is being imported from Excel, with the data being exported from a source I cannot control. The incoming table contains a heading in row 1 that only has text in the first cell followed by blank cells, with the column names in row 2. I am currently manually deleting this first row before importing/linking the table, but I need to set it up so that others can use it without having to go through this manual step. I would like to set up some code (VBA) to do it automatically. This would entail:

1) Determine if the first row contains column names or junk. ie: Check if the first row, first cell (A1) starts with either "Curriculum " or "Title" (because if it starts with "Title" the junk row has already been deleted); or: check if cells B1, C1, D1, etc are blank (indicating a junk row).

2) If the first row is indeed junk, delete it.

Ideally, this code would be integrated with the linked table update, so that the table is 'cleaned' as it's imported. Whether this happens in the update process or immediately after doesn't matter to me, as long as it is only being done when the table is being linked/imported.

[NeoPa]

What do you have already Joel? It's important, as much of the code required would be to determine things which are not related directly to the problem, but would take a lot of time to develop (EG. The address of the Excel file being worked upon and possibly selecting it if that were necessary).

The actual question is easy enough to handle, but we shouldn't be wasting our time developing the whole project for you. You need to include such stuff in the question.

[Joel Marion]

Sadly, I have nothing yet. I'm a total VBA noob, and I'm hoping to be pointed in the right direction. I don't expect anyone to write the code for me, but even identifying specific functions that might help would be appreciated.

[ADezii]

My approach would be to Import the Excel Spreadsheet, then analyze the 1st Field in the 1st Record, taking appropriate action afterwards.

1. If the Table already exists, DELETE it.
2. Import the Spreadsheet.
3. If the Value in the 1st Field of the 1st Record = "Curriculum ", then DELETET the Row.
   

   '*********************** USER DEFINED ***********************
   Const conPATH_TO_SPREADSHEET As String = "C:\Test\Test.xls"
   Const conTABLE_NAME As String = "Test Import"
   '************************************************************
   
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim tdf As DAO.TableDef
   
   'See if the Imported Table already exists, if so DELETE it
   For Each tdf In CurrentDb.TableDefs
     If tdf.Name = conTABLE_NAME Then
       CurrentDb.TableDefs.Delete conTABLE_NAME
         Exit For
     End If
   Next
   
   Set MyDB = CurrentDb
   
   'Import the Table
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
                               conPATH_TO_SPREADSHEET, False
   
   
   Set rst = MyDB.OpenRecordset(conTABLE_NAME, dbOpenDynaset)
   
   With rst
     If Not .BOF And Not .EOF Then     'Any Records?
       .MoveFirst
         'If the 1st Field in the 1st Record = "Curriculum" then DELETE the Record
         If .Fields(0) = "Curriculum" Then
           .Delete
         End If
     End If
   End With
   
   rst.Close
   Set rst = Nothing

[NeoPa]

In that case, I would start with something that is more like walking than running. Explore how you can identify the file to start with (Select a File or Folder using the FileDialog Object). Next look into Application Automation. When you have some basics put together would be a good time to start piecing together bits of code that can form your overall picture.

[Joel Marion]

ADezii, yes, I'm able to import the file that way, but then it's too late to accept the second row as headers. I've been doing a lot of digging around, but still can't seem to get things working. My problem is with step 2 in the following process, where I can't figure out the proper code to open the named Excel workbook, evaluate the first row for "junk" (ie: B1 is empty), and delete the junk if necessary. It seems to me like I need to:
1) set the file name:

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Function LaunchCD(strform As Form) As String
    Dim OpenFile As OPENFILENAME
    Dim lReturn As Long
    Dim sFilter As String
    OpenFile.lStructSize = Len(OpenFile)
    OpenFile.hwndOwner = strform.hwnd
    sFilter = "Microsoft Excel Files (*.xls)" & Chr(0) & "*.XLS" & Chr(0) & _
    "All Files (*.*)" & Chr(0) & "*.*" & Chr(0)
    OpenFile.lpstrFilter = sFilter
    OpenFile.nFilterIndex = 1
    OpenFile.lpstrFile = String(257, 0)
    OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
    OpenFile.lpstrFileTitle = OpenFile.lpstrFile
    OpenFile.nMaxFileTitle = OpenFile.nMaxFile
    OpenFile.lpstrInitialDir = "R:\Curriculum_Verification\Curriculum Verification Reports"
    OpenFile.lpstrTitle = "Select the most recent Curriculum Setup Verification Report"
    OpenFile.flags = 0
    lReturn = GetOpenFileName(OpenFile)
        If lReturn = 0 Then
            MsgBox "A file was not selected!", vbInformation, _
              "Select the most recent Curriculum Setup Verification Report"
         Else
            LaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
         End If
End Function

2) Clean up the Excel file (this is where I'm having problems):

Private Sub cleanExcel_Click()
On Error GoTo Err_cleanExcel_Click

Hourglass = True
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim Message
Set xlApp = New Excel.Application
        
' double-check that PATH_TO_SPREADSHEET has been set
    If PATH_TO_SPREADSHEET = "" Then
        'identify the spreadsheet to be imported
        Me!fileName = LaunchCD(Me)
        'Set the path to the spreadsheet
        PATH_TO_SPREADSHEET = Me!fileName
     ' otherwise, go on and use the already set path
    End If
' End double-check PATH_TO_SPREADSHEET

' ### THIS IS WHERE MY PROBLEMS START 

Excel.Application.Visible = False
Set xlWB = Excel.Application.Workbooks.Open(PATH_TO_SPREADSHEET, , False)

If xlWB.Range("B1").value = "" Then

' ### INSERT CODE TO DELETE ROW 1

Else
' ### DO NOTHING, THE HEADERS ARE IN ROW 1
End If
    
    xlApp.Quit
    Set xlApp = Nothing
    Set xlWB = Nothing

Exit_cleanExcel_Click:
    Hourglass = False
    Exit Sub

Err_cleanExcel_Click:
    MsgBox Err.Description
    Resume Exit_cleanExcel_Click
    
End Sub

3) Then import it:

Private Sub importTable_Click()
On Error GoTo Err_importTable_Click
Hourglass = True
'*********************** USER DEFINED ***********************
Dim PATH_TO_SPREADSHEET As String
Const conTABLE_NAME As String = "CVReport"
'************************************************************
 
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim cleanExcel As Long
Dim objExcel As Object

' identify the spreadsheet to be imported and
' put the filename and path in the fileName field
Me!fileName = LaunchCD(Me)

'Set the path to the spreadsheet
PATH_TO_SPREADSHEET = Me!fileName

' check for bad header, fix if necessary
' ### Am I calling the function properly?
cleanExcel() = PATH_TO_SPREADSHEET

'See if the Imported Table already exists, if so DELETE it
For Each tdf In CurrentDb.TableDefs
  If tdf.Name = conTABLE_NAME Then
    CurrentDb.TableDefs.Delete conTABLE_NAME
      Exit For
  End If
Next
 
Set MyDB = CurrentDb
 
'Import the Table
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
                            PATH_TO_SPREADSHEET, False

Exit_importTable_Click:
Hourglass = False
    Exit Sub

Err_importTable_Click:
    MsgBox Err.Description
    Resume Exit_importTable_Click

End Sub

I've marked up where I'm having problems with "###" comments. Like I said, my biggest problem is working with the Excel file from within Access.

Thanks in advance for any feedback!

Joel

[NeoPa]

If you check out the Application Automation link I posted earlier you'll see the recommended ways to open an Excel application depending on your needs. These do not include simply creating a new object with the New keyword (Line #8 of your second block of code).

Line #22 should probably be refering to your Excel object variable rather than the class itself, although this is the default when opened correctly :

xlApp.Visible = False

Line #23 suffers from the same problem. Entirely unnecessary if the file is opened as illustrated in the linked article. I'll leave it there for now as most of the problems seem to be related to not having read the linked article. If you can show code without these easily resolved problems then I'll be happy to look again for you.

PS. Nevertheless this is much better and something we can work with when you post what you have. Well done.

[Joel Marion]

NeoPa: I'll readily admit to not fully understanding everything in your application automation tutorial. Nevertheless, I did find it quite useful.

Here is what I've come up with, that seems to do what I want. As you'll see, I've folded together two of the functions that had previously been posted as separate. The file open dialogue has not changed, and so is not repeated below.

Private Sub importTable_Click()
On Error GoTo Err_importTable_Click
Hourglass = True

Dim PATH_TO_SPREADSHEET As String
Const conTABLE_NAME As String = "CVReport"
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim cleanExcel As Long
Dim objExcel As Object

' identify the spreadsheet to be imported and
' put the filename and path in the fileName field
Me!fileName = LaunchCD(Me)

'Set the path to the spreadsheet
PATH_TO_SPREADSHEET = Me!fileName

'************************************************************
' check for bad header in Excel, fix if necessary

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim Message
Set xlApp = Excel.Application
        
xlApp.Visible = False
Set xlWB = Excel.Application.Workbooks.Open(PATH_TO_SPREADSHEET, , False)

'  If the first row is not headers (B2 is blank), delete it
If xlApp.Workbooks(1).Worksheets(1).Cells(1, 2).Value = "" Then
    xlApp.Workbooks(1).Worksheets(1).Rows(1).EntireRow.Delete
    xlApp.ActiveWorkbook.Close SaveChanges:=True
End If
    
    xlApp.Quit
    Set xlApp = Nothing
    Set xlWB = Nothing
'************************************************************

'See if the Imported Table already exists, if so DELETE it
For Each tdf In CurrentDb.TableDefs
  If tdf.Name = conTABLE_NAME Then
    CurrentDb.TableDefs.Delete conTABLE_NAME
      Exit For
  End If
Next
 
Set MyDB = CurrentDb
 
'Import the Table
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
                            PATH_TO_SPREADSHEET, True

Exit_importTable_Click:
Hourglass = False
    Exit Sub

Err_importTable_Click:
    MsgBox Err.Description
    Resume Exit_importTable_Click

End Sub

Note that I also had to change the last value of DoCmd.TransferS preadsheet to "True" in order to accept the first row as headers:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
                            PATH_TO_SPREADSHEET, True

The only lingering question is why "xlApp.Quit " doesn't actually quit Excel. The application doesn't appear open, but it appears in the task manager, still running, even after I close Access. I tried it with "Call" as well (as suggested in the tutorial), to no avail.

[dsatino]

Just a guess, if you change line 29 to use the xlApp variable it might solve the 'quit' issue.

Or alternatively you could just use Excel.Applicati on.Quit

[ADezii]

I'm with dsatino on this one, namely:

'************************ Code Intentionally Omitted ************************
Set xlApp = New Excel.Application
xlApp.Visible = False

Set xlWB = xlApp.Workbooks.Open(PATH_TO_SPREADSHEET, , False)
  
'If the first row is not headers (B2 is blank), delete it
With xlApp
  If .Workbooks(1).Worksheets(1).Cells(1, 2).Value = "" Then
    .Workbooks(1).Worksheets(1).Rows(1).EntireRow.Delete
    .ActiveWorkbook.Close SaveChanges:=True
  End If
End With
'************************ Code Intentionally Omitted ************************

[NeoPa]

Two ideas spring to mind Joel :

1. Your line #26 is still not getting hold of the Excel application in the way suggested.
2. It seems you do set the xlApp object to nothing (A point I would otherwise have made as this can keep the application alive), but the order of the lines that clear down the objects should be reversed from that of assignment generally. I would expect to see lines #37 to #39 as :
   

       xlApp.Quit
       Set xlWB = Nothing
       Set xlApp = Nothing

   I doubt this will make much of a difference but it may be confusing things.

As for the opening code, I would expect something like :

Set xlApp = GetObject(PATH_TO_SPREADSHEET, "Excel.Application")
Set xlWB = xlApp.ActiveWorkbook

This would replace lines #26 through #29 (An automated application object is returned in a hidden state anyway by default).

[Joel Marion]

Solved!

Reversing the order of lines #37 to 39 didn't seem to change anything (excel still stayed open in the background), but properly addressing my variable on line #29 ("Set xlWB = xlApp.Workbooks."... instead of "Excel.Applicati on.Workbooks."...) successfully resulted in closing excel. I tested the line #37 to 39 order again (both ways) after changing line #29, and there appeared to be no difference.

NeoPa: When I tried this:

    Set xlApp = GetObject(PATH_TO_SPREADSHEET, "Excel.Application")
    Set xlWB = xlApp.ActiveWorkbook

I received a "File name or class name not found during automation operation" error, and the excel automation did not successfully execute (the 'bad' row was not deleted).

Reverting back to the following seemed to fix it:

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(PATH_TO_SPREADSHEET, , False)

I think the biggest issue was that I was not consistently addressing my variable ("xlApp").

Here is what I've ended up with:

Private Sub importTable_Click()
On Error GoTo Err_importTable_Click
Hourglass = True
'*********************** USER DEFINED ***********************
Dim PATH_TO_SPREADSHEET As String
Const conTABLE_NAME As String = "CVReport"
'************************************************************
 
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim cleanExcel As Long
Dim objExcel As Object
Dim Message

' identify the spreadsheet to be imported and
' put the filename and path in the fileName field
Me!fileName = LaunchCD(Me)

'Set the path to the spreadsheet
PATH_TO_SPREADSHEET = Me!fileName

'************************************************************
' check for bad header in Excel, fix if necessary

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(PATH_TO_SPREADSHEET, , False)

'  If the first row is not headers (B2 is blank), delete it
With xlApp
If xlApp.Workbooks(1).Worksheets(1).Cells(1, 2).Value = "" Then
    xlApp.Workbooks(1).Worksheets(1).Rows(1).EntireRow.Delete
    xlApp.ActiveWorkbook.Close SaveChanges:=True
End If
End With
    
'  Close Excel and reset variables to nothing
    xlApp.Quit
    Set xlWB = Nothing
    Set xlApp = Nothing
    
'************************************************************

'See if the Imported Table already exists, if so DELETE it
For Each tdf In CurrentDb.TableDefs
  If tdf.Name = conTABLE_NAME Then
    CurrentDb.TableDefs.Delete conTABLE_NAME
      Exit For
  End If
Next
 
Set MyDB = CurrentDb
 
'Import the Table, note "true" to read row 1 headers
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
                            PATH_TO_SPREADSHEET, True

Exit_importTable_Click:
Hourglass = False
    Exit Sub

Err_importTable_Click:
    MsgBox Err.Description
    Resume Exit_importTable_Click

End Sub

Thank you NeoPa and ADezii for your great help on this!

[NeoPa]

Joel:
I think the biggest issue was that I was not consistently addressing my variable ("xlApp").

I'd certainly agree on that one. I might do some testing on the other code that didn't work for you. I find the idea of simply creating an Excel.Applicati on object using the New keyword somewhat suspicious. If it's as simple as that I'm curious as to why the recommended approach is to use the CreateObject() call (or in other circumstances the GetObject() call). These recommendations come from MS documentation originally. I didn't create the article completely from my own experience. Anyway, I'll do some exploring and see what I find.