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

[Ehsan arman]

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.

[ADezii]

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:
   

   Dim rng As Excel.Range
   Dim rngDefine As Excel.Range
    
   'Define your own Range
   Set rngDefine = Worksheets("Sheet1").Range("A1:P1000")
    
   'DELETE every Row in which the String Total appears in any Column
   For Each rng In rngDefine
     If UCase$(rng.Value) = "TOTAL" Then
       Worksheets("Sheet1").Rows(rng.Row).Delete
     End If
   Next

[Ehsan arman]

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.

[ADezii]

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?

[Ehsan arman]

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?

[ADezii]

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:
   

   Total Quantity
   Sum Total for Year
   Sales - Total for Month
   etc...

2. If the word 'Total' exists either by itself or amongst other words, DELETE the Row that contains it? Is this correct?

[Ehsan arman]

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.

[ADezii]

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.

[Ehsan arman]

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.

[ADezii]

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.

[Ehsan arman]

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.

[ADezii]

Give me some time and I'll see what I can come up with.

[ADezii]

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.
   

   'First, set a Reference to the Microsoft Office XX.X Object Library
   'as well as to the Microsoft Excel XX.X Object Library
   Dim strButtonCaption As String
   Dim strDialogTitle As String
   Dim strExcelFile As String
    
   'Define your own Captions if necessary
   strButtonCaption = "Import"
   strDialogTitle = "Select Excel Spreadsheet"
    
   With Application.FileDialog(msoFileDialogFilePicker)
     With .Filters
       .Clear
       .Add "Excel Spreadsheets", "*.xls"     'Allow Spreadsheets Only
     End With
     'The Show Method returns True if 1 or more files are selected
       .AllowMultiSelect = False
       .ButtonName = strButtonCaption
       .InitialFileName = vbNullString
       .InitialView = msoFileDialogViewDetails     'Detailed View
       .Title = strDialogTitle
     If .Show Then
       strExcelFile = .SelectedItems(1)
     Else
       Exit Sub
     End If
   End With
   DoCmd.Hourglass True
    
   '************************** Start of Automation Code **************************
   Dim ExcelApp As New Excel.Application
   Dim ExcelBook As New Excel.Workbook
   Dim rng As Excel.Range
   Dim rngDefine As Excel.Range
    
   Set ExcelBook = ExcelApp.Workbooks.Open(strExcelFile)
   ExcelApp.Visible = True         'See what is going on?
    
   'Define your own Range
   Set rngDefine = ExcelBook.Worksheets("Sheet1").Range("A1:P100")
    
   'DELETE every Row in which the String Total appears in any Column
   For Each rng In rngDefine
     If UCase$(rng.Value) = "TOTAL" Then
       ExcelBook.Worksheets("Sheet1").Rows(rng.Row).Delete
     End If
   Next
    
   'Save as strExcelFile & "_2.xls"  (strip .xls from strExcelFile)
   'Example: C:\Test\Stock_Quotes.xls will be Saved As C:\Test\Stock_Quotes_2.xls
   ExcelBook.SaveAs Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls"
    
   ExcelApp.Quit
   '******************************************************************************
    
   'The Import Process can begin now, you'll be Importing the File named
   'Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls" which is exactly the Original
   'Spreadsheet striped of any Row that contains the word Total in any Cell within the
   'pre-defined Range
    
   DoCmd.Hourglass False

P.S. - Be sure to set References to both the Microsoft Office and Excel Object Libraries.

[Ehsan arman]

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?

[ADezii]

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.

[Ehsan arman]

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?

[ADezii]

Again, I would have to see the Spreadsheet.

[Ehsan arman]

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.

[ADezii]

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.

[Ehsan arman]

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?

[ADezii]

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.

[Ehsan arman]

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?

[ADezii]

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.

[Ehsan arman]

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?

[ADezii]

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).
   

   'First, set a Reference to the Microsoft Office XX.X Object Library
   'as well as to the Microsoft Excel XX.X Object Library
   Dim strButtonCaption As String
   Dim strDialogTitle As String
   Dim strExcelFile As String
   Dim intRowCtr As Integer
    
   'Define your own Captions if necessary
   strButtonCaption = "Import"
   strDialogTitle = "Select Excel Spreadsheet"
    
   With Application.FileDialog(msoFileDialogFilePicker)
     With .Filters
       .Clear
       .Add "Excel Spreadsheets", "*.xls"     'Allow Spreadsheets Only
     End With
     'The Show Method returns True if 1 or more files are selected
       .AllowMultiSelect = False
       .ButtonName = strButtonCaption
       .InitialFileName = vbNullString
       .InitialView = msoFileDialogViewDetails     'Detailed View
       .Title = strDialogTitle
     If .Show Then
       strExcelFile = .SelectedItems(1)
     Else
       Exit Sub
     End If
   End With
   DoCmd.Hourglass True
    
   '************************** Start of Automation Code **************************
   Dim ExcelApp As New Excel.Application
   Dim ExcelBook As New Excel.Workbook
   Dim rng As Excel.Range
   Dim rngDefine As Excel.Range
    
   Set ExcelBook = ExcelApp.Workbooks.Open(strExcelFile)
   ExcelApp.Visible = False        'See what is going on?
    
   'Define your own Range
   Set rngDefine = ExcelBook.Worksheets("Sheet1").Range("A1:P100")
    
   'DELETE the first 4 Rows, then Shift the remaining Cells Up
   ExcelBook.Worksheets("Sheet1").Rows("1:4").Select
   ExcelApp.Selection.Delete Shift:=xlUp
    
   'DELETE every Row in which the String Total appears in any Column
   For Each rng In rngDefine
     If UCase$(rng.Value) = "TOTAL" Then
       ExcelBook.Worksheets("Sheet1").Rows(rng.Row).Delete
     End If
   Next
    
   'Save as strExcelFile & "_2.xls"  (strip .xls from strExcelFile)
   'Example: C:\Test\Stock_Quotes.xls will be Saved As C:\Test\Stock_Quotes_2.xls
   ExcelBook.SaveAs Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls"
    
   ExcelApp.Quit
   Set ExcelApp = Nothing
   '******************************************************************************
    
   'The Import Process can begin now, you'll be Importing the File named
   'Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls" which is exactly the Original
   'Spreadsheet striped of any Row that contains the word Total in any Cell within the
   'pre-defined Range
    
   DoCmd.Hourglass False

[Ehsan arman]

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.

[ADezii]

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).
   

   Dim strButtonCaption As String
   Dim strDialogTitle As String
   'Dim strExcelFile As String     'Comment or Remove

2. Declare strExcelFile Privately in the Form's Code Module:
   

   Private strExcelFile As String

3. DELETE CCTV_2.xls in the Close() Event of your Form, if it exists:
   

   Private Sub Form_Close()
     If Len(strExcelFile) <> 0 Then
       If Dir$(Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls") <> "" Then
         Kill Left$(strExcelFile, Len(strExcelFile) - 4) & "_2.xls"
       End If
     End If
   End Sub

[Ehsan arman]

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.

[ADezii]

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.

[Ehsan arman]

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.

[ADezii]

Glad it all worked out for you.

[Ehsan arman]

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

[ADezii]

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?

[Ehsan arman]

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.

[ADezii]

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?

[Ehsan arman]

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.

[ADezii]

You are only concerned about Duplication in the FacilityID Field, correct?

[Ehsan arman]

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.

[ADezii]

The following Query will Copy all Fields from the Test Table to a Table named tblDups based on duplication in the FacilityID Field:

SELECT Test.FacilityID, Test.Section, Test.Location, Test.[U/S MH#], Test.[U/S MH Depth], Test.[U/S MH Diameter], 
Test.[D/S MH#], Test.[D/S MH Depth], Test.[D/S MH Diameter], Test.System, Test.Diameter, Test.[Plan Length], 
Test.Material INTO TblDups FROM Test
WHERE Test.FacilityID In (SELECT [FacilityID] FROM [Test] As Tmp GROUP BY [FacilityID] HAVING Count(*)>1 
ORDER BY Test.FacilityID);

[Ehsan arman]

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.

[ADezii]

How can you have Duplicates in Table Test on [FacilityID] if it is the Primary Key?

[Ehsan arman]

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.

[Ehsan arman]

Despite removing the primary key I still got nothing moved into TblDups.

[lindafitriani]

thanks ADezii.... very helpfull for me ;) thank u for sharing... i really appreciate...