469,281 Members | 2,484 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,281 developers. It's quick & easy.

How to delete particular row of an excel file before importing into an access table?

Hi guys,
I have a bunch of excel files stored in a folder and have a code to choose the appropriate excel sheet by opening up a dialog box. However, each excel sheet has a row which include the word "Total" in one or more columns.I dont want to see that row containing the word total in my access table so I want it to be deleted before the excel sheet is transferred into access.

Thanks for your help in advance.
Jan 12 '11 #1
43 14741
ADezii
8,800 Expert 8TB
  1. This is a simple matter if done within the context of the Excel Files, and involves:
    1. Defining a Range Object.
    2. Looping through every Cell in the Range to see if it contains the String Total.
    3. DELETE the corresponding Row containing that Value.
  2. The same process can be accomplished in Access, but would be more complex and involve Automation Code that performs this operation prior to the actual Import.
  3. The Code is posted below:
    Expand|Select|Wrap|Line Numbers
    1. Dim rng As Excel.Range
    2. Dim rngDefine As Excel.Range
    3.  
    4. 'Define your own Range
    5. Set rngDefine = Worksheets("Sheet1").Range("A1:P1000")
    6.  
    7. 'DELETE every Row in which the String Total appears in any Column
    8. For Each rng In rngDefine
    9.   If UCase$(rng.Value) = "TOTAL" Then
    10.     Worksheets("Sheet1").Rows(rng.Row).Delete
    11.   End If
    12. Next
Jan 13 '11 #2
Thanks ADezii, actually I should have phrased my question a little better. What I want is the row with the word Total to not get imported into access I dont want to have it deleted like permanently from my excel files. So maybe have a way of terminating my TransferSpreadsheet method and exiting the sub when it sees that row??
Thanks for the reply.
Jan 13 '11 #3
ADezii
8,800 Expert 8TB
Don't think that there is any way of doing this using the TransferSpreadsheet() Method. Why not DELETE the Rows in the Table after Import?
Jan 13 '11 #4
Ok so maybe I can save a temporary copy of the current excel file and then delete that particular row before importing into access?? This way I can overwrite that temp file by saving every new excel sheet that I open so I will end up with one temp file at the end and have the original file intact?
Jan 13 '11 #5
ADezii
8,800 Expert 8TB
The Code to DELETE any Row containing 'Total' (post Import) is actually quite simple, if you wish to go that Route. Just let me know. If you want to implement this, there are 2 critical items that I wish to know:
  1. Would the Whole Word 'Total' exist in a Field by itself, or can the word 'Total' exist anywhere within the Field, as in:
    Expand|Select|Wrap|Line Numbers
    1. Total Quantity
    2. Sum Total for Year
    3. Sales - Total for Month
    4. etc...
  2. If the word 'Total' exists either by itself or amongst other words, DELETE the Row that contains it? Is this correct?
Jan 13 '11 #6
Hi, The word 'Total' appears in the excel sheet at the very bottom row and it can be found under two columns and it represents the sum of the data in the corresponding two columns. I just dont want that row to show up in the access table after the import.
Jan 13 '11 #7
ADezii
8,800 Expert 8TB
Gotcha. An alternative would be to:
  1. Once you retrieve the Name of the Excel Spreadsheet via the File Dialog, Open the Sheet via Automation Code from Access.
  2. Run Code similar to what I showed you, only from within Access.
  3. Save the Spreadsheet after the Code has Run.
  4. Proceed with the Import as usual.
  5. Stating the solution, and implementing it are two different things. If you are interested in this approach, and not pressed for time, I'll see what I can come up with.
  6. Simply let me know one way or the other.
Jan 13 '11 #8
so are you suggesting to save the file with a different name after deleting that row prior to TransferSpreadsheet()? right now I am using the filedialog for opening my Excel files in the format outlined here : http://www.mvps.org/access/api/api0001.htm
and then calling the TransferSpreadsheet command.
Jan 13 '11 #9
ADezii
8,800 Expert 8TB
Absolutely no need to be that complex when using a File Dialog to Open an Excel File. You can use the Standard Office File Dialog.
Jan 13 '11 #10
Yeah I guess. I am still not sure as to how to delete that row after import. What do you mean by "Automation Code from Access"? The code that you provided runs only in Excel environment and for a particular worksheet. How can I incorporate an equivalent code in access. I am opening the File dialog from my form's On_Click_event button.
Jan 13 '11 #11
ADezii
8,800 Expert 8TB
Give me some time and I'll see what I can come up with.
Jan 13 '11 #12
ADezii
8,800 Expert 8TB
I created a Demo for you that will:
  1. Open a customized Office File Dialog whose Filter is set for Excel Spreadsheets only (*.xls).
  2. Once an Excel File has been selected:
    1. Open the File from within Access using Automation Code (simply stated, manipulating another Application from within Access).
    2. Search every Cell within a specified Range on Sheet1 for the word 'Total'. Both the Sheet Name and Range Parameters can be customized by you.
    3. In every Cell that 'Total' is found, DELETE the Row that the Cell is in.
    4. Save the Spreadsheet (Save As) as the Base File Name & "_2.xls". This is the File that you will not Import since it has been 'Cleaned'.
    5. Perform Clean-Up chores and continue with the Import.
  3. Download the Demo (Attachment), define a Range and Sheet Name, enter the word Total in as many Cells as you like within this Range, execute the Code which will Open the File Dialog, and select the modified File.
  4. I'll Post the Code, but definitely download the Attachment to really see what is going on.
  5. Any questions, feel free to ask.
    Expand|Select|Wrap|Line Numbers
    1. 'First, set a Reference to the Microsoft Office XX.X Object Library
    2. 'as well as to the Microsoft Excel XX.X Object Library
    3. Dim strButtonCaption As String
    4. Dim strDialogTitle As String
    5. Dim strExcelFile As String
    6.  
    7. 'Define your own Captions if necessary
    8. strButtonCaption = "Import"
    9. strDialogTitle = "Select Excel Spreadsheet"
    10.  
    11. With Application.FileDialog(msoFileDialogFilePicker)
    12.   With .Filters
    13.     .Clear
    14.     .Add "Excel Spreadsheets", "*.xls"     'Allow Spreadsheets Only
    15.   End With
    16.   'The Show Method returns True if 1 or more files are selected
    17.     .AllowMultiSelect = False
    18.     .ButtonName = strButtonCaption
    19.     .InitialFileName = vbNullString
    20.     .InitialView = msoFileDialogViewDetails     'Detailed View
    21.     .Title = strDialogTitle
    22.   If .Show Then
    23.     strExcelFile = .SelectedItems(1)
    24.   Else
    25.     Exit Sub
    26.   End If
    27. End With
    28. DoCmd.Hourglass True
    29.  
    30. '************************** Start of Automation Code **************************
    31. Dim ExcelApp As New Excel.Application
    32. Dim ExcelBook As New Excel.Workbook
    33. Dim rng As Excel.Range
    34. Dim rngDefine As Excel.Range
    35.  
    36. Set ExcelBook = ExcelApp.Workbooks.Open(strExcelFile)
    37. ExcelApp.Visible = True         'See what is going on?
    38.  
    39. 'Define your own Range
    40. Set rngDefine = ExcelBook.Worksheets("Sheet1").Range("A1:P100")
    41.  
    42. 'DELETE every Row in which the String Total appears in any Column
    43. For Each rng In rngDefine
    44.   If UCase$(rng.Value) = "TOTAL" Then
    45.     ExcelBook.Worksheets("Sheet1").Rows(rng.Row).Delete
    46.   End If
    47. Next
    48.  
    49. 'Save as strExcelFile & "_2.xls"  (strip .xls from strExcelFile)
    50. 'Example: C:\Test\Stock_Quotes.xls will be Saved As C:\Test\Stock_Quotes_2.xls
    51. ExcelBook.SaveAs Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls"
    52.  
    53. ExcelApp.Quit
    54. '******************************************************************************
    55.  
    56. 'The Import Process can begin now, you'll be Importing the File named
    57. 'Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls" which is exactly the Original
    58. 'Spreadsheet striped of any Row that contains the word Total in any Cell within the
    59. 'pre-defined Range
    60.  
    61. DoCmd.Hourglass False
P.S. - Be sure to set References to both the Microsoft Office and Excel Object Libraries.
Jan 14 '11 #13
Awesome, Thanks very much. It works fine for most of the Excel sheets, but for a few it generates an error: Type mismatch and it highlights the line:

If UCase$(rng.Value) = "TOTAL" Then

I also made the range large enough ("A1:1Z100")so that shouldnt be a problem. Do you know why?
Jan 14 '11 #14
ADezii
8,800 Expert 8TB
Do you know why?
My initial guess is that the Cell(s) are formatted as NUMERIC. If you can send me one of the Spreadsheets for which the Error is generated, I'll be happy to have a look at it. Other than this, I honestly cannot say why you are getting the Error.
Jan 14 '11 #15
thanks, I will try to fix that error if I cant I'll send you the spreadsheet. One other thing thats causing an error is when the field names in the Spreadsheet don't start from the first row A:1 I get the error " Field F1 does not exist in the destination Table". For some reason Access wants the field names to be on row 1 but in all my Spreadsheet the fields start from row 5 or 6( they have some comments on the first couple of rows).. Do you have any idea how I can fix that?
Jan 14 '11 #16
ADezii
8,800 Expert 8TB
Again, I would have to see the Spreadsheet.
Jan 14 '11 #17
Ok I attached it as a text file since it wouldnt let me to post a .xls file. So the Field names start from where it says Section Facility ID. The data starts from where it says AV-7. The top First few rows are just comments to identify the Spreadsheet.
Attached Files
File Type: txt CCTV.txt (1.5 KB, 413 views)
Jan 14 '11 #18
ADezii
8,800 Expert 8TB
Sorry Ehsan, but I Imported the Data (CCTV.txt) into a New Excel Spreadsheet, executed the Code after selecting CCTV.xls, and it ran perfectly without a hitch.

P.S. - If you place the *.xls File within a Compressed File (*.zip) then you can Upload it.
Jan 15 '11 #19
oh ok thanks. thats weird though because when I try to import that .xls file into the database table it produced the error "field F1 does not exist in the destination table" so my guess was that Access when reading the TransferSpreadsheet() assumes all your field names start on the 1st row of your Spreadsheets unless you tell it otherwise. Did you also create a table in Access having the same field names?
Jan 15 '11 #20
ADezii
8,800 Expert 8TB
We're talking about two different things. I was referring to Importing the Text File into Excel, then executing the Code to DELETE Rows which contained 'Total', not Importing the Excel File into Access.
Jan 15 '11 #21
oh yeah absolutely. That code works just fine and does what it's supposed to do. The thing I was having an error with after importing the excel files into Access was the name fields not appearing on the first row in the Excel sheets. so I was getting the error 'Field F1 does not exist in destination table'.When I deleted the first few rows(the following rows in CCTV.txt):
CCCT Inspection Daily Report
Contractor: IVIS Inc.
Neighbourhood: Alberta Avenue
Date: SEPT 18/08
Operator : SAM
I no longer got that error upon importing into Access.
I guess Access assumes that the first row in all Spreadsheets contains the name fields and wants to put that into the corresponding table. Do you know how I can fix that?
Jan 15 '11 #22
ADezii
8,800 Expert 8TB
You can always set the 5th Argument of the TransferSpreadsheet() Method (HasFieldNames) to False, then doctor the Table once the Data is in or DELETE the first couple of Rows in the SaveAs File prior to the actual Import.
Jan 15 '11 #23
I have tried setting the (HasFieldNames) to false, but the error just repeated itself,so that didn't help. So in the lines of code where deleting rows comes into play:
If UCase$(rng.Value) = "TOTAL" Then
ExcelBook.Worksheets("Sheet1").Rows(rng.Row).Delet e

Instead of "TOTAL" I would have another few If statements just like above where it would have the first words in those rows like:
If UCase$(rng.Value) = "CONTRACTOR". Is that correct?
Jan 16 '11 #24
ADezii
8,800 Expert 8TB
How about?
  1. DELETING the first 4 Rows prior to the actual search for 'Total' in each Cell? (Code Lines 43 to 45).
  2. Making the Excel Window invisible? (Code Line 38).
  3. Releasing the Memory allocated to the Excel Application Object Variable? (Code Line 59).
    Expand|Select|Wrap|Line Numbers
    1. 'First, set a Reference to the Microsoft Office XX.X Object Library
    2. 'as well as to the Microsoft Excel XX.X Object Library
    3. Dim strButtonCaption As String
    4. Dim strDialogTitle As String
    5. Dim strExcelFile As String
    6. Dim intRowCtr As Integer
    7.  
    8. 'Define your own Captions if necessary
    9. strButtonCaption = "Import"
    10. strDialogTitle = "Select Excel Spreadsheet"
    11.  
    12. With Application.FileDialog(msoFileDialogFilePicker)
    13.   With .Filters
    14.     .Clear
    15.     .Add "Excel Spreadsheets", "*.xls"     'Allow Spreadsheets Only
    16.   End With
    17.   'The Show Method returns True if 1 or more files are selected
    18.     .AllowMultiSelect = False
    19.     .ButtonName = strButtonCaption
    20.     .InitialFileName = vbNullString
    21.     .InitialView = msoFileDialogViewDetails     'Detailed View
    22.     .Title = strDialogTitle
    23.   If .Show Then
    24.     strExcelFile = .SelectedItems(1)
    25.   Else
    26.     Exit Sub
    27.   End If
    28. End With
    29. DoCmd.Hourglass True
    30.  
    31. '************************** Start of Automation Code **************************
    32. Dim ExcelApp As New Excel.Application
    33. Dim ExcelBook As New Excel.Workbook
    34. Dim rng As Excel.Range
    35. Dim rngDefine As Excel.Range
    36.  
    37. Set ExcelBook = ExcelApp.Workbooks.Open(strExcelFile)
    38. ExcelApp.Visible = False        'See what is going on?
    39.  
    40. 'Define your own Range
    41. Set rngDefine = ExcelBook.Worksheets("Sheet1").Range("A1:P100")
    42.  
    43. 'DELETE the first 4 Rows, then Shift the remaining Cells Up
    44. ExcelBook.Worksheets("Sheet1").Rows("1:4").Select
    45. ExcelApp.Selection.Delete Shift:=xlUp
    46.  
    47. 'DELETE every Row in which the String Total appears in any Column
    48. For Each rng In rngDefine
    49.   If UCase$(rng.Value) = "TOTAL" Then
    50.     ExcelBook.Worksheets("Sheet1").Rows(rng.Row).Delete
    51.   End If
    52. Next
    53.  
    54. 'Save as strExcelFile & "_2.xls"  (strip .xls from strExcelFile)
    55. 'Example: C:\Test\Stock_Quotes.xls will be Saved As C:\Test\Stock_Quotes_2.xls
    56. ExcelBook.SaveAs Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls"
    57.  
    58. ExcelApp.Quit
    59. Set ExcelApp = Nothing
    60. '******************************************************************************
    61.  
    62. 'The Import Process can begin now, you'll be Importing the File named
    63. 'Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls" which is exactly the Original
    64. 'Spreadsheet striped of any Row that contains the word Total in any Cell within the
    65. 'pre-defined Range
    66.  
    67. DoCmd.Hourglass False
Jan 16 '11 #25
Yea thank you it worked fine. I am now trying to delete the _2.xls file after import and running into some errors. I am using the Kill("pathname\Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls"") method but it cannot find the file to delete.
Jan 17 '11 #26
ADezii
8,800 Expert 8TB
Because of the nature of the Code Execution, you may not be able to DELETE the CCTV_2.xls File immediately after Importing it.
  1. Comment or Remove the Code Line Declaring the strExcelFile Variable (Code Line #3).
    Expand|Select|Wrap|Line Numbers
    1. Dim strButtonCaption As String
    2. Dim strDialogTitle As String
    3. 'Dim strExcelFile As String     'Comment or Remove
  2. Declare strExcelFile Privately in the Form's Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Private strExcelFile As String
  3. DELETE CCTV_2.xls in the Close() Event of your Form, if it exists:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Close()
    2.   If Len(strExcelFile) <> 0 Then
    3.     If Dir$(Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls") <> "" Then
    4.       Kill Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls"
    5.     End If
    6.   End If
    7. End Sub
Jan 17 '11 #27
Actually, I don't have on close() event in my form because the form is controlled by a command button. I put it in the On Exit() event of the button but it didn't delete the file.
Jan 17 '11 #28
ADezii
8,800 Expert 8TB
As this point, there is not much more that I can do, unless you are willing to Upload the Database (not the *.xls). The decision is yours, just let me know one way or the other.
Jan 17 '11 #29
Thanks, I used the Kill Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls" in the main subroutine and it worked.It turns out I didn't need to include it in the close() event of the form.
Jan 18 '11 #30
ADezii
8,800 Expert 8TB
Glad it all worked out for you.
Jan 18 '11 #31
Thank you. It's a long project I'm working on.Since I'm a beginner in VBA I will probably have many more things to ask.
I was also wondering if I could show after the import which records were imported into the database and which ones were lost? because as of now what I'm getting is a MsgBox saying some records were lost due to key violations after the import
Jan 19 '11 #32
ADezii
8,800 Expert 8TB
That would be a little difficult, since you would have to compare the Data in Excel prior to the Import with the Data in the Access Table after the Import. After the Import, do you see any kind of Import Errors Table?
Jan 19 '11 #33
Hi, I managed to display which records were imported into the table. I opened a recordset on the table and used the following loop:

Set recst = dbs.OpenRecordset("Test")
Do While Not recst.EOF
MsgBox "The Pipe IDs for the newly imported Pipes are the following:" & recst.Fields(1)
recst.MoveNext
Loop

However, I also wanted to display which pipes are a duplicate in the table and when I find the duplicates move them into another table, say table "TestDuplicates". I tried using two strings one containing the records in recst.fields(1)and comparing that to another string but I didnt get meaningful results.
Jan 20 '11 #34
ADezii
8,800 Expert 8TB
  1. What is the Name of your Table?
  2. List all the Table Fields in the Table as well as their Data Types.
  3. Which Field would have Duplicate information?
Jan 20 '11 #35
ok the name of the table is "Test" and it includes the following fields:
Section- FacilityID - Location- U/S MH#- U/S MH Depth - U/S MH Diameter- D/S MH# - D/S MH Depth- D/S MH Diameter-System- Diameter - Plan Length - Material

The duplicates could occur in any of the fields but I have only set a primary key for the field FacilityID in the table.
Jan 20 '11 #36
ADezii
8,800 Expert 8TB
You are only concerned about Duplication in the FacilityID Field, correct?
Jan 20 '11 #37
Yes, although the primary key prevents the duplication for that field I still want to be able to identify the duplicate records in that field and move the duplicates into a new table.
Jan 20 '11 #38
ADezii
8,800 Expert 8TB
The following Query will Copy all Fields from the Test Table to a Table named tblDups based on duplication in the FacilityID Field:
Expand|Select|Wrap|Line Numbers
  1. SELECT Test.FacilityID, Test.Section, Test.Location, Test.[U/S MH#], Test.[U/S MH Depth], Test.[U/S MH Diameter], 
  2. Test.[D/S MH#], Test.[D/S MH Depth], Test.[D/S MH Diameter], Test.System, Test.Diameter, Test.[Plan Length], 
  3. Test.Material INTO TblDups FROM Test
  4. WHERE Test.FacilityID In (SELECT [FacilityID] FROM [Test] As Tmp GROUP BY [FacilityID] HAVING Count(*)>1 
  5. ORDER BY Test.FacilityID);
Jan 21 '11 #39
Well,I made TblDups with the exact same fields as Test and I also made a few Duplicate FacilityIDs in the Spreadsheet before the import, but I can't see anything stored in the TblDups. I can see that only one of the duplicate FacilityIDs appearing in Test(because of the Primary Key), but I don't see anything moved into TblDups.
Jan 21 '11 #40
ADezii
8,800 Expert 8TB
How can you have Duplicates in Table Test on [FacilityID] if it is the Primary Key?
Jan 21 '11 #41
You're right I can't unless I remove the primary key or have a way of identifying which Facility ID in the Excel Sheet is gonna have a duplicate in the Test table before import. I guess for now I have no choice but to remove the primary key.
Jan 21 '11 #42
Despite removing the primary key I still got nothing moved into TblDups.
Jan 21 '11 #43
thanks ADezii.... very helpfull for me ;) thank u for sharing... i really appreciate...
Jan 2 '12 #44

Post your reply

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

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.