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 TransferSpreads heet 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 TransferSpreads heet() 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 TransferSpreads heet()? 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 TransferSpreads heet 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.Valu e) = "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.