Manipulating Excel (A2K SR1)

Topic

New Reply

Users have a query in access and want the results of that query sent to excel for processing. In a nutshell, data from Access query “Query Imp final Data For Report1” is written to C:Imp.xls. The raw data is then copied and pasted to four tabs for processing.

In the first tab, “RDT&E Lvl 4 & 5 by FY”, I need to loop through the data and delete all rows where the data in column 2 , Level, > 5. Simple enough. I was able to do this while coding in excel but cant get it to work when controlling excel from access. Code is as below: I am getting an error message runtime 424, object requried at line shown. I have tried this various different ways but am stuck. Funny thing is this part was working yesterday as I was able to loop through the code and see the values but cant now. Reference is set to Microsoft Excel 9.0 Object Library and DAO 3.6. Any help would be appreciated.

Just to clarify, I can manipulate this in access as needed but the users want it in excel and then want me to format it via colors and other irritating items as needed, so it is off to excel I go. Help.

Long code fragment moved to attachment by HansV

Reply | Quote

Replies

Gary

I think you need to prefix all your Excel commands with xlapp otherwise they will not work correctly. e.g. Sheets(“Query Imp final Data For Report”).Select should be xlapp.Sheets(“Query Imp final Data For Report”).Select

Nick

Reply | Quote

I will use your advice and make the change and see if it helps.

Reply | Quote

Got it – The following code does what I want – just a bit of trial and error. On to the next issue.

' Process and Format Sheet S1, RDT&E Lvl 4 & 5 by FY
    ' Set to current worksheet
        Set xlsht = xlwbk.Worksheets(S1)
        
    ' Get Number or rows of data + 1
        NumRows = xlsht.Cells(65536, 1).End(xlUp).Row + 1
    
    'Being Looping through and processing data
    'With xlapp.xlwbk.Worksheets(S1)
        With xlapp.Sheets(S1)
          For I = 2 To NumRows
            If .Cells(I, 2) > 5 Then
                RowRef = I & ":" & I
               .Rows(RowRef).Select
                Selection.Delete Shift:=xlUp
                I = I - 1
            End If
          Next I
        End With

edited to fix text formatting

Reply | Quote

To be on the safe side, you should also use xlApp.Selection instead of Selection,

Reply | Quote

Hans,

Thanks. I will make the correction. I still have a reference problem when changing tabs in excel but I will figure that out.

On a related note, do you know how would you find out the maximum number of columns being used in excel. By starting with cell A1 and using xldown and xlright I can get to the last cell of data. Perhaps by referencing the cell value (Just thinking outloud here).

Is there a formula similar to xlsht.Cells(65536, 1).End(xlUp).Row that would do this. I have tried variations on this but keep getting errors.

Thanks for the help.

Reply | Quote

If there is a row in which the “last” column is guaranteed to be filled, say row 2, you can use

xlSht.Range(“IV2″).End(xlToLeft).Column

A general method to find the last used column is

xlSht.Cells.Find(What:=”*”, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

Reply | Quote

Gary

Another possible way to find the last used cell in a worksheet is :

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)

Nick

Reply | Quote

True, but Excel doesn’t always keep track of the “real” last cell while a worksheet is being edited, in particular if rows or columns have been deleted.

Reply | Quote

Hans

I stand corrected & thanks for the info. According to http://www.ozgrid.com :

You can use Edit>Go to-Special-Last cell to try and find the last cell in the active sheet, but it is not very reliable. The reasons are two-fold:

1. The last cell is only re-set when you save. This means if you enter any number or text in say, cell A10 and A20 of a new Worksheet, then delete the content of A20, the Edit>Go to-Special-Last cell will keep taking you to A20, until you save.

2. It picks up cell fomatting. Let’s say you enter any text or number in cell A10 and then enter a valid date in cell A20 of a new Worksheet. Now delete the date in cell A20 and save. The Edit>Go to-Special-Last cell will still take you to A20. This is because entering a date in A20 has caused Excel to automatically format the cell from “General” to a Date format. To stop from going to A20 you will have to use Edit>Clear>All and then save.

So when using VBA you cannot rely on:

Range(“A1”).SpecialCells(xlCellTypeLastCell).Select

Thanks again

Nick

Reply | Quote

Thanks for the suggestion.

Reply | Quote

Hans,

Thanks for the help and suggestions. I truly appreciate it.

Reply | Quote

Just curious,

Since I reference the xlapp in the With clause, is it still proper to reference it again with Selection?

Reply | Quote

Yes, it’s still valid. With … End With is just a convenience. You could even use xlapp.Sheets(S1) within the With xlapp.Sheets(S1) … End With block.

Reply | Quote