473,883 Members | 1,712 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Delete Junk First Row When Importing Linked Table

22 New Member
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
12 12694
NeoPa
32,584 Recognized Expert Moderator MVP
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
Joel Marion
22 New Member
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:
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
32,584 Recognized Expert Moderator MVP
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.
Jan 20 '12 #13

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

Similar topics

1
2026
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? tod
0
2141
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 system. I actually import from two different named ranges from each workbook so for every import the following subroutine is used twice. The database is used to collate data collected from different surveys entered by remote users into an Excel...
5
4487
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 error: Deleting data in a linked table is not supported by this ISAM. From what I understand, indexed sequential access method (ISAM) drivers are used to update "non-Microsoft" file formats. So why doesn't Access
3
19505
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. However, whenever I try to edit data I get the error message "Updating data in a linked table is not supported by this ISAM." I can understand not being able to edit the linked data, but the field I'm trying to update is drawn from the stored...
4
18980
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 password. I am developing a process that will automatically run at night which will access those tables. I need to be able to give Access the password, as the user currently does, so that the process can run without a password prompt appearing....
1
3420
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 As String '*** You have to modify the path to where db1.mdb is located
2
2796
JAMBAI
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
2
3394
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 table today, it is linked as a text(255) field, ditto for the first table if I link it today if I link the 2nd table using access2003 (and the same odbc data source) it is linked as a memo field
4
1717
MattFitzgerald
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 I do not receive the password prompt? DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Documents and Settings\fitzgem4\Desktop\2008_2009 V5.mdb", acTable, "Tbl_VOL_References", "Tbl_VOL_References_StratfordData", False I have been...
0
1164
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 statement. I might think the syntax is wrong, but when opening the table in Access the delete option is disabled (but only on the linked table). He can delete records from that table from within SQL Server, so it's not a permissions issue on that...
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9781
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9567
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4606
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4211
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.