By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Delete Junk First Row When Importing Linked Table

P: 22
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.
Jan 3 '12 #1
Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 3 '12 #2

P: 22
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.
Jan 3 '12 #3

ADezii
Expert 5K+
P: 8,597
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.
    Expand|Select|Wrap|Line Numbers
    1. '*********************** USER DEFINED ***********************
    2. Const conPATH_TO_SPREADSHEET As String = "C:\Test\Test.xls"
    3. Const conTABLE_NAME As String = "Test Import"
    4. '************************************************************
    5.  
    6. Dim MyDB As DAO.Database
    7. Dim rst As DAO.Recordset
    8. Dim tdf As DAO.TableDef
    9.  
    10. 'See if the Imported Table already exists, if so DELETE it
    11. For Each tdf In CurrentDb.TableDefs
    12.   If tdf.Name = conTABLE_NAME Then
    13.     CurrentDb.TableDefs.Delete conTABLE_NAME
    14.       Exit For
    15.   End If
    16. Next
    17.  
    18. Set MyDB = CurrentDb
    19.  
    20. 'Import the Table
    21.   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
    22.                             conPATH_TO_SPREADSHEET, False
    23.  
    24.  
    25. Set rst = MyDB.OpenRecordset(conTABLE_NAME, dbOpenDynaset)
    26.  
    27. With rst
    28.   If Not .BOF And Not .EOF Then     'Any Records?
    29.     .MoveFirst
    30.       'If the 1st Field in the 1st Record = "Curriculum" then DELETE the Record
    31.       If .Fields(0) = "Curriculum" Then
    32.         .Delete
    33.       End If
    34.   End If
    35. End With
    36.  
    37. rst.Close
    38. Set rst = Nothing
Jan 3 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 3 '12 #5

P: 22
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:
Expand|Select|Wrap|Line Numbers
  1. Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
  2. "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
  3.  
  4. Private Type OPENFILENAME
  5.     lStructSize As Long
  6.     hwndOwner As Long
  7.     hInstance As Long
  8.     lpstrFilter As String
  9.     lpstrCustomFilter As String
  10.     nMaxCustFilter As Long
  11.     nFilterIndex As Long
  12.     lpstrFile As String
  13.     nMaxFile As Long
  14.     lpstrFileTitle As String
  15.     nMaxFileTitle As Long
  16.     lpstrInitialDir As String
  17.     lpstrTitle As String
  18.     flags As Long
  19.     nFileOffset As Integer
  20.     nFileExtension As Integer
  21.     lpstrDefExt As String
  22.     lCustData As Long
  23.     lpfnHook As Long
  24.     lpTemplateName As String
  25. End Type
  26.  
  27. Function LaunchCD(strform As Form) As String
  28.     Dim OpenFile As OPENFILENAME
  29.     Dim lReturn As Long
  30.     Dim sFilter As String
  31.     OpenFile.lStructSize = Len(OpenFile)
  32.     OpenFile.hwndOwner = strform.hwnd
  33.     sFilter = "Microsoft Excel Files (*.xls)" & Chr(0) & "*.XLS" & Chr(0) & _
  34.     "All Files (*.*)" & Chr(0) & "*.*" & Chr(0)
  35.     OpenFile.lpstrFilter = sFilter
  36.     OpenFile.nFilterIndex = 1
  37.     OpenFile.lpstrFile = String(257, 0)
  38.     OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
  39.     OpenFile.lpstrFileTitle = OpenFile.lpstrFile
  40.     OpenFile.nMaxFileTitle = OpenFile.nMaxFile
  41.     OpenFile.lpstrInitialDir = "R:\Curriculum_Verification\Curriculum Verification Reports"
  42.     OpenFile.lpstrTitle = "Select the most recent Curriculum Setup Verification Report"
  43.     OpenFile.flags = 0
  44.     lReturn = GetOpenFileName(OpenFile)
  45.         If lReturn = 0 Then
  46.             MsgBox "A file was not selected!", vbInformation, _
  47.               "Select the most recent Curriculum Setup Verification Report"
  48.          Else
  49.             LaunchCD = Trim(Left(OpenFile.lpstrFile, InStr(1, OpenFile.lpstrFile, vbNullChar) - 1))
  50.          End If
  51. End Function
  52.  
  53.  
2) Clean up the Excel file (this is where I'm having problems):
Expand|Select|Wrap|Line Numbers
  1. Private Sub cleanExcel_Click()
  2. On Error GoTo Err_cleanExcel_Click
  3.  
  4. Hourglass = True
  5. Dim xlApp As Excel.Application
  6. Dim xlWB As Excel.Workbook
  7. Dim Message
  8. Set xlApp = New Excel.Application
  9.  
  10. ' double-check that PATH_TO_SPREADSHEET has been set
  11.     If PATH_TO_SPREADSHEET = "" Then
  12.         'identify the spreadsheet to be imported
  13.         Me!fileName = LaunchCD(Me)
  14.         'Set the path to the spreadsheet
  15.         PATH_TO_SPREADSHEET = Me!fileName
  16.      ' otherwise, go on and use the already set path
  17.     End If
  18. ' End double-check PATH_TO_SPREADSHEET
  19.  
  20. ' ### THIS IS WHERE MY PROBLEMS START 
  21.  
  22. Excel.Application.Visible = False
  23. Set xlWB = Excel.Application.Workbooks.Open(PATH_TO_SPREADSHEET, , False)
  24.  
  25. If xlWB.Range("B1").value = "" Then
  26.  
  27. ' ### INSERT CODE TO DELETE ROW 1
  28.  
  29. Else
  30. ' ### DO NOTHING, THE HEADERS ARE IN ROW 1
  31. End If
  32.  
  33.     xlApp.Quit
  34.     Set xlApp = Nothing
  35.     Set xlWB = Nothing
  36.  
  37. Exit_cleanExcel_Click:
  38.     Hourglass = False
  39.     Exit Sub
  40.  
  41. Err_cleanExcel_Click:
  42.     MsgBox Err.Description
  43.     Resume Exit_cleanExcel_Click
  44.  
  45. End Sub
3) Then import it:
Expand|Select|Wrap|Line Numbers
  1. Private Sub importTable_Click()
  2. On Error GoTo Err_importTable_Click
  3. Hourglass = True
  4. '*********************** USER DEFINED ***********************
  5. Dim PATH_TO_SPREADSHEET As String
  6. Const conTABLE_NAME As String = "CVReport"
  7. '************************************************************
  8.  
  9. Dim MyDB As DAO.Database
  10. Dim rst As DAO.Recordset
  11. Dim tdf As DAO.TableDef
  12. Dim cleanExcel As Long
  13. Dim objExcel As Object
  14.  
  15. ' identify the spreadsheet to be imported and
  16. ' put the filename and path in the fileName field
  17. Me!fileName = LaunchCD(Me)
  18.  
  19. 'Set the path to the spreadsheet
  20. PATH_TO_SPREADSHEET = Me!fileName
  21.  
  22. ' check for bad header, fix if necessary
  23. ' ### Am I calling the function properly?
  24. cleanExcel() = PATH_TO_SPREADSHEET
  25.  
  26. 'See if the Imported Table already exists, if so DELETE it
  27. For Each tdf In CurrentDb.TableDefs
  28.   If tdf.Name = conTABLE_NAME Then
  29.     CurrentDb.TableDefs.Delete conTABLE_NAME
  30.       Exit For
  31.   End If
  32. Next
  33.  
  34. Set MyDB = CurrentDb
  35.  
  36. 'Import the Table
  37.   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
  38.                             PATH_TO_SPREADSHEET, False
  39.  
  40. Exit_importTable_Click:
  41. Hourglass = False
  42.     Exit Sub
  43.  
  44. Err_importTable_Click:
  45.     MsgBox Err.Description
  46.     Resume Exit_importTable_Click
  47.  
  48. End Sub
  49.  
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
Jan 13 '12 #6

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 13 '12 #7

P: 22
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub importTable_Click()
  2. On Error GoTo Err_importTable_Click
  3. Hourglass = True
  4.  
  5. Dim PATH_TO_SPREADSHEET As String
  6. Const conTABLE_NAME As String = "CVReport"
  7. Dim MyDB As DAO.Database
  8. Dim rst As DAO.Recordset
  9. Dim tdf As DAO.TableDef
  10. Dim cleanExcel As Long
  11. Dim objExcel As Object
  12.  
  13. ' identify the spreadsheet to be imported and
  14. ' put the filename and path in the fileName field
  15. Me!fileName = LaunchCD(Me)
  16.  
  17. 'Set the path to the spreadsheet
  18. PATH_TO_SPREADSHEET = Me!fileName
  19.  
  20. '************************************************************
  21. ' check for bad header in Excel, fix if necessary
  22.  
  23. Dim xlApp As Excel.Application
  24. Dim xlWB As Excel.Workbook
  25. Dim Message
  26. Set xlApp = Excel.Application
  27.  
  28. xlApp.Visible = False
  29. Set xlWB = Excel.Application.Workbooks.Open(PATH_TO_SPREADSHEET, , False)
  30.  
  31. '  If the first row is not headers (B2 is blank), delete it
  32. If xlApp.Workbooks(1).Worksheets(1).Cells(1, 2).Value = "" Then
  33.     xlApp.Workbooks(1).Worksheets(1).Rows(1).EntireRow.Delete
  34.     xlApp.ActiveWorkbook.Close SaveChanges:=True
  35. End If
  36.  
  37.     xlApp.Quit
  38.     Set xlApp = Nothing
  39.     Set xlWB = Nothing
  40. '************************************************************
  41.  
  42. 'See if the Imported Table already exists, if so DELETE it
  43. For Each tdf In CurrentDb.TableDefs
  44.   If tdf.Name = conTABLE_NAME Then
  45.     CurrentDb.TableDefs.Delete conTABLE_NAME
  46.       Exit For
  47.   End If
  48. Next
  49.  
  50. Set MyDB = CurrentDb
  51.  
  52. 'Import the Table
  53.   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
  54.                             PATH_TO_SPREADSHEET, True
  55.  
  56. Exit_importTable_Click:
  57. Hourglass = False
  58.     Exit Sub
  59.  
  60. Err_importTable_Click:
  61.     MsgBox Err.Description
  62.     Resume Exit_importTable_Click
  63.  
  64. End Sub
  65.  
Note that I also had to change the last value of DoCmd.TransferSpreadsheet to "True" in order to accept the first row as headers:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
  2.                             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.
Jan 19 '12 #8

dsatino
100+
P: 393
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.Application.Quit
Jan 19 '12 #9

ADezii
Expert 5K+
P: 8,597
I'm with dsatino on this one, namely:
Expand|Select|Wrap|Line Numbers
  1. '************************ Code Intentionally Omitted ************************
  2. Set xlApp = New Excel.Application
  3. xlApp.Visible = False
  4.  
  5. Set xlWB = xlApp.Workbooks.Open(PATH_TO_SPREADSHEET, , False)
  6.  
  7. 'If the first row is not headers (B2 is blank), delete it
  8. With xlApp
  9.   If .Workbooks(1).Worksheets(1).Cells(1, 2).Value = "" Then
  10.     .Workbooks(1).Worksheets(1).Rows(1).EntireRow.Delete
  11.     .ActiveWorkbook.Close SaveChanges:=True
  12.   End If
  13. End With
  14. '************************ Code Intentionally Omitted ************************
Jan 19 '12 #10

NeoPa
Expert Mod 15k+
P: 31,186
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 :
    Expand|Select|Wrap|Line Numbers
    1.     xlApp.Quit
    2.     Set xlWB = Nothing
    3.     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 :
Expand|Select|Wrap|Line Numbers
  1. Set xlApp = GetObject(PATH_TO_SPREADSHEET, "Excel.Application")
  2. Set xlWB = xlApp.ActiveWorkbook
This would replace lines #26 through #29 (An automated application object is returned in a hidden state anyway by default).
Jan 19 '12 #11

P: 22
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.Application.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:
Expand|Select|Wrap|Line Numbers
  1.     Set xlApp = GetObject(PATH_TO_SPREADSHEET, "Excel.Application")
  2.     Set xlWB = xlApp.ActiveWorkbook
  3.  
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:
Expand|Select|Wrap|Line Numbers
  1. Set xlApp = New Excel.Application
  2. Set xlWB = xlApp.Workbooks.Open(PATH_TO_SPREADSHEET, , False)
  3.  
I think the biggest issue was that I was not consistently addressing my variable ("xlApp").

Here is what I've ended up with:
Expand|Select|Wrap|Line Numbers
  1. Private Sub importTable_Click()
  2. On Error GoTo Err_importTable_Click
  3. Hourglass = True
  4. '*********************** USER DEFINED ***********************
  5. Dim PATH_TO_SPREADSHEET As String
  6. Const conTABLE_NAME As String = "CVReport"
  7. '************************************************************
  8.  
  9. Dim MyDB As DAO.Database
  10. Dim rst As DAO.Recordset
  11. Dim tdf As DAO.TableDef
  12. Dim cleanExcel As Long
  13. Dim objExcel As Object
  14. Dim Message
  15.  
  16. ' identify the spreadsheet to be imported and
  17. ' put the filename and path in the fileName field
  18. Me!fileName = LaunchCD(Me)
  19.  
  20. 'Set the path to the spreadsheet
  21. PATH_TO_SPREADSHEET = Me!fileName
  22.  
  23. '************************************************************
  24. ' check for bad header in Excel, fix if necessary
  25.  
  26. Dim xlApp As Excel.Application
  27. Dim xlWB As Excel.Workbook
  28. Set xlApp = New Excel.Application
  29. Set xlWB = xlApp.Workbooks.Open(PATH_TO_SPREADSHEET, , False)
  30.  
  31. '  If the first row is not headers (B2 is blank), delete it
  32. With xlApp
  33. If xlApp.Workbooks(1).Worksheets(1).Cells(1, 2).Value = "" Then
  34.     xlApp.Workbooks(1).Worksheets(1).Rows(1).EntireRow.Delete
  35.     xlApp.ActiveWorkbook.Close SaveChanges:=True
  36. End If
  37. End With
  38.  
  39. '  Close Excel and reset variables to nothing
  40.     xlApp.Quit
  41.     Set xlWB = Nothing
  42.     Set xlApp = Nothing
  43.  
  44. '************************************************************
  45.  
  46. 'See if the Imported Table already exists, if so DELETE it
  47. For Each tdf In CurrentDb.TableDefs
  48.   If tdf.Name = conTABLE_NAME Then
  49.     CurrentDb.TableDefs.Delete conTABLE_NAME
  50.       Exit For
  51.   End If
  52. Next
  53.  
  54. Set MyDB = CurrentDb
  55.  
  56. 'Import the Table, note "true" to read row 1 headers
  57.   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
  58.                             PATH_TO_SPREADSHEET, True
  59.  
  60. Exit_importTable_Click:
  61. Hourglass = False
  62.     Exit Sub
  63.  
  64. Err_importTable_Click:
  65.     MsgBox Err.Description
  66.     Resume Exit_importTable_Click
  67.  
  68. End Sub
Thank you NeoPa and ADezii for your great help on this!
Jan 20 '12 #12

NeoPa
Expert Mod 15k+
P: 31,186
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.Application 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.
Jan 20 '12 #13

Post your reply

Sign in to post your reply or Sign up for a free account.