472,357 Members | 1,937 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,357 software developers and data experts.

Delete Junk First Row When Importing Linked Table

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
12 12433
32,511 Expert Mod 16PB
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
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
8,832 Expert 8TB
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. '************************************************************
    6. Dim MyDB As DAO.Database
    7. Dim rst As DAO.Recordset
    8. Dim tdf As DAO.TableDef
    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
    18. Set MyDB = CurrentDb
    20. 'Import the Table
    21.   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
    22.                             conPATH_TO_SPREADSHEET, False
    25. Set rst = MyDB.OpenRecordset(conTABLE_NAME, dbOpenDynaset)
    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
    37. rst.Close
    38. Set rst = Nothing
Jan 3 '12 #4
32,511 Expert Mod 16PB
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
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
  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
  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
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
  4. Hourglass = True
  5. Dim xlApp As Excel.Application
  6. Dim xlWB As Excel.Workbook
  7. Dim Message
  8. Set xlApp = New Excel.Application
  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
  22. Excel.Application.Visible = False
  23. Set xlWB = Excel.Application.Workbooks.Open(PATH_TO_SPREADSHEET, , False)
  25. If xlWB.Range("B1").value = "" Then
  29. Else
  31. End If
  33.     xlApp.Quit
  34.     Set xlApp = Nothing
  35.     Set xlWB = Nothing
  37. Exit_cleanExcel_Click:
  38.     Hourglass = False
  39.     Exit Sub
  41. Err_cleanExcel_Click:
  42.     MsgBox Err.Description
  43.     Resume Exit_cleanExcel_Click
  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 ***********************
  6. Const conTABLE_NAME As String = "CVReport"
  7. '************************************************************
  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
  15. ' identify the spreadsheet to be imported and
  16. ' put the filename and path in the fileName field
  17. Me!fileName = LaunchCD(Me)
  19. 'Set the path to the spreadsheet
  20. PATH_TO_SPREADSHEET = Me!fileName
  22. ' check for bad header, fix if necessary
  23. ' ### Am I calling the function properly?
  24. cleanExcel() = PATH_TO_SPREADSHEET
  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
  34. Set MyDB = CurrentDb
  36. 'Import the Table
  37.   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
  38.                             PATH_TO_SPREADSHEET, False
  40. Exit_importTable_Click:
  41. Hourglass = False
  42.     Exit Sub
  44. Err_importTable_Click:
  45.     MsgBox Err.Description
  46.     Resume Exit_importTable_Click
  48. 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!

Jan 13 '12 #6
32,511 Expert Mod 16PB
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
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
  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
  13. ' identify the spreadsheet to be imported and
  14. ' put the filename and path in the fileName field
  15. Me!fileName = LaunchCD(Me)
  17. 'Set the path to the spreadsheet
  18. PATH_TO_SPREADSHEET = Me!fileName
  20. '************************************************************
  21. ' check for bad header in Excel, fix if necessary
  23. Dim xlApp As Excel.Application
  24. Dim xlWB As Excel.Workbook
  25. Dim Message
  26. Set xlApp = Excel.Application
  28. xlApp.Visible = False
  29. Set xlWB = Excel.Application.Workbooks.Open(PATH_TO_SPREADSHEET, , False)
  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
  37.     xlApp.Quit
  38.     Set xlApp = Nothing
  39.     Set xlWB = Nothing
  40. '************************************************************
  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
  50. Set MyDB = CurrentDb
  52. 'Import the Table
  53.   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
  54.                             PATH_TO_SPREADSHEET, True
  56. Exit_importTable_Click:
  57. Hourglass = False
  58.     Exit Sub
  60. Err_importTable_Click:
  61.     MsgBox Err.Description
  62.     Resume Exit_importTable_Click
  64. End Sub
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
393 256MB
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
8,832 Expert 8TB
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
  5. Set xlWB = xlApp.Workbooks.Open(PATH_TO_SPREADSHEET, , False)
  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
32,511 Expert Mod 16PB
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
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
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)
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 ***********************
  6. Const conTABLE_NAME As String = "CVReport"
  7. '************************************************************
  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
  16. ' identify the spreadsheet to be imported and
  17. ' put the filename and path in the fileName field
  18. Me!fileName = LaunchCD(Me)
  20. 'Set the path to the spreadsheet
  21. PATH_TO_SPREADSHEET = Me!fileName
  23. '************************************************************
  24. ' check for bad header in Excel, fix if necessary
  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)
  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
  39. '  Close Excel and reset variables to nothing
  40.     xlApp.Quit
  41.     Set xlWB = Nothing
  42.     Set xlApp = Nothing
  44. '************************************************************
  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
  54. Set MyDB = CurrentDb
  56. 'Import the Table, note "true" to read row 1 headers
  57.   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, _
  58.                             PATH_TO_SPREADSHEET, True
  60. Exit_importTable_Click:
  61. Hourglass = False
  62.     Exit Sub
  64. Err_importTable_Click:
  65.     MsgBox Err.Description
  66.     Resume Exit_importTable_Click
  68. End Sub
Thank you NeoPa and ADezii for your great help on this!
Jan 20 '12 #12
32,511 Expert Mod 16PB
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

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

Similar topics

by: tod | last post by:
My Access database has a link to a View in another database, using ODBC. That view is updated by its database each day. I'd like to know what time that happened. Is there a way I can find that? ...
by: me here | last post by:
I have a VBA subroutine that links an MS Excel spreadsheet and copies the data into a local table. This process is controlled by a form that allows users to select the spreadsheet from the file...
by: deko | last post by:
How to run action query against linked table? I have an Access 2003 mdb with an Excel 2003 Workbook as a linked table. When I attempt to run an action query against the linked table I get this...
by: Michael Plant | last post by:
Hello one and all. I have a stored table in my database and the form I'm using is based on a query that draws data from my stored table and a linked table. The linked table is a *.txt file. ...
by: Neil Ginsberg | last post by:
I have ODBC linked tables to a SQL 7 database in an A2K database. The linked tables do not have the password stored in them, so the first time the user accesses them, they need to enter the SQL...
by: jdorp | last post by:
I read KP article at support.microsoft.com (Q177594) that stated that with the following code: Sub Command1_Click() Dim db1 As Database Dim db2 As Database Dim rs As Recordset Dim strConnect...
by: JAMBAI | last post by:
Hi, How to delete large numbers (100,000 - 1,000,000) records from linked table. I am trying to delete from MS Access Forms. Thanks Jambai
by: Roger | last post by:
I've got two tables in sql2005 which have an 'ntext' field when I linked the first table in access97 last week using an odbc data source the access-field type was 'memo' when I link the 2nd...
by: MattFitzgerald | last post by:
I am using the below code to import a table when the code run I get a pop up box asking me for the password for the database I am importing from. Is it possible to put the password into my code so...
by: Duke Slater | last post by:
I have a user who has created an Access 2007 database with one linked table to SQL Server 2005. He wants to purge the linked table and repopulate it, but gets the 3086 error when running a delete...
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. header("Location:".$urlback); Is this the right layout the...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.