Need help - Excel Automation - Find Non Date Cell & Delete Entire Row 2

[Lokoono]

Hi all,

I can get the code below to work as an Excel Macro, but I'm at a loss with trying to do the same from Access VBA by using Excel automation (it's part of an import procedure I'm working on). Can anyone help?

I'd like to just use Access VBA and don't want to use any external files (such as a .bas file that I could call from Access) if possible.

Here's the code in Excel:

' Code modified slightly from Hiker95's (forum member on MrExcel.com) code
Dim LR As Long, a As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = LR To 2 Step -1
  Cells(a, 1).Select
  If IsDate(Cells(a, 1)) = False Then
    Cells(a, 1).EntireRow.Delete
  End If
Next a
Application.ScreenUpdating = True

Is there an Access VBA equivalent? I have the following code to start off, but am stuck as to how to type up the For...Next part.

Private Sub cmdImportExcel_Click()
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
On Error Resume Next
'If  Excel  is  already  open,  get  a  handle  to
'the  existing  instance.
Set xlApp = GetObject(, "Excel.Application")
'Test  for  an  error  condition.
If Err <> 0 Then
'Excel  is  not  currently  open,  create  an
'instance.
Set xlApp = CreateObject("Excel.Application")
End If
Set xlWorkbook = xlApp.Workbooks.Open("c:\DatesToImport.xls", 0, False)
Set xlSheet = xlApp.Worksheets("Sheet1")
xlApp.DisplayAlerts = False
xlSheet.Range("A2:A" & xlSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).Select

'This space is where I need to type in the lines of
'code to loop through Column A to find non date fields
'and delete them when found.

End Sub

 

[kjv1611]

The For-Next part of your loop should be the same, as it doesn't depend on any specific model data.

The inside part would be the same, but just using the xlApp object to get the actions to work on the correct workbook/worksheet.

So, basically, possibly something like this:

xlApp.ScreenUpdating = False

LR = xlSheet.Cells(Rows.Count, 1).End(xlUp).Row
For a = LR To 2 Step -1
  xlSheet.Cells(a, 1).Select
  If IsDate(xlSheet.Cells(a, 1)) = False Then
    xlSheet.Cells(a, 1).EntireRow.Delete
  End If
Next a

xlApp.ScreenUpdating = True

But I'm not sure whether you need xlApp.Application.ScreenUpdating, or whether just the way I typed above.

Also, if Step doesn't work as is, then try xlApp.Step

I would think that will work. You may have to clean up the code from an error or two, but basically, I would think that's it.

--

"If to err is human, then I must be some kind of human!" -Me

 

[lameid]

Honestly I would write a query to do it. Something like the below...

Delete From [Table Name]
Where Isdate([Table Name].[Field Name]) = False

You could also run it via vba as a literal...

docmd.setwarnings False 'Supress warning messages ...
     'like "Do you really want to delete x records?"
Docmd.runSQL "Delete From [Table Name] " & _
     "Where Isdate([Table Name].[Field Name]) = False"
docmd.setwarnings True 'Turn warnings back on

 

[Lokoono]

Addition to my question above:


I meant to also say that I can't save the spreadsheet with the macro inside it. It's not my spreadsheet to change. I could save it as a new sheet and use a .bas file to run the code, but there's no guarantee that the date column will always be Column A, so I was wanting to just do all this from Access where I can control any variables.

Thanks in advance for any help )

 

[lameid]

If you want to do it in Access, import the spreadsheet as in and then fix it... My post was related to deleting the blanks.

On the otherhand, I have written code that alters an existing spreadsheet before import and then saves the sheet.

Either approaches are fine. To Automate Excel, set a reference to the "Microsoft Excel X.X Object Library" where X.X is the numeric version of Excel you are using (i.e. Excel 2003 is 11.0). And then create an excel Application object in Access. Do everything that is Excel code off the Excel Applicatioon Object... Here is a sample to start.

Dim XLAPP As Excel.Application
Set XLAPP = CreateObject("Excel.Application")
     With XLAPP
            '.Visible = True  'When you debug, sometimes it helps to see what is going on
            .Workbooks.Open FileName:= _
                strFilePath
         .ActiveWorkbook.Close (True) 'Close and save workbook
         .Quit
     End With
Set XLAPP = Nothing

 

[Lokoono]

kjv1611 - Your code worked perfectly. I don't know why I didn't try something similar (my brain shuts down on Friday apparently). The ScreenUpdating piece isn't required in normal function (where Excel wouldn't be visible when this code runs), but it does come in handy for testing purposes (the Excel window moves around a lot if it's left out).

lameid - Your code works great if I was going to import the date field as a text field when uploading to Access. Normally I don't have a problem doing this if I was only using the field for reporting purposes. However, the powers that be may want me to do some calculations with the field, which means it'd need to be a date field in this circumstance. Still, it may still be useful with this project and I know I can use your examples in other projects I'm doing where I don't need to do calculations.

Thanks again to you both!

 

[Lokoono]

lameid - In regards to your last post, the timing of my followup to my own question was typed up before I read your original answer. Your first answer did help in some aspects of what I need to do.

I'm sorry if I implied that I was confused about how to automate Excel through Access. I do know how to do that (my OP gives an example of what I had so far). It's just the looping part that was confusing me. I do appreciate you answering again though.

I prefer kjv1611's answer in this case since I'm already doing a lot of Excel automation (I'm trying to convince the owners of the spreadsheet to accept my changes - they have been reluctant because they think it would be too much work to fix them).

You did remind me that I could always just leave the field intended for dates formatted as a text field in Access (and just change it later if need be). It's always nice to have a workaround ready when possible.

 

[kjv1611]

Glad it worked out.

You could also create an additional loop with conditional statements to be sure that the field header(s) are in the correct locations at all times, and THEN import/query the data. The query would run faster than looping through the fields that way.

Also, if you need to edit the sheet on each ocasion, prior to doing anything, you can just code that in as well, using the Excel object... or even the FileSystem object, I believe - I've used it before to delete files as needed from within VBA.

--

"If to err is human, then I must be some kind of human!" -Me

 

[lameid]

You could import to a table, remove the data and then append that data into a table with correct data types and finally delete the imported table. If I use a 'temp table' I put it either in the Front end or create another file as a template and copy it to a fixed location and link to it.

Ideally you don't need them to change their spreadsheets. You need them to put the data in Access and if they insist on using Excel for reason x, dump or link to the Access data in Excel.

In my adventures, data is routinely dumped to Excel from an application as a report so I have to do all sorts of things just to get it in first normal form to import it. I hate Excel. It has a narrow scope of being useful for some analysis but it has no place to store reusable data or a valid method to transmit it... Ideally I could query the application database correctly... Large companies can be difficult. No one even knows who is in charge of things to ask the appropriate questions.

As for automation... I didn't go back and reread your entire OP. I missed the second code segment and hence confused.

Hmmm... I'm seriously contemplating kjv1611's suggestion of validating the column headings before import myself. Just more data to store