Results 1 to 14 of 14
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Manipulating Excel (A2K SR1)

    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
    Regards,

    Gary
    (It's been a while!)

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating Excel (A2K SR1)

    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

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Manipulating Excel (A2K SR1)

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

    Gary
    (It's been a while!)

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Manipulating Excel (A2K SR1)

    Got it - The following code does what I want - just a bit of trial and error. On to the next issue.
    <pre>' 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</pre>



    edited to fix text formatting
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Manipulating Excel (A2K SR1)

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

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Manipulating Excel (A2K SR1)

    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.
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Manipulating Excel (A2K SR1)

    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

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating Excel (A2K SR1)

    Gary

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

    ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)

    Nick

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Manipulating Excel (A2K SR1)

    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.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating Excel (A2K SR1)

    Hans

    I stand corrected & thanks for the info. According to 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).Selec t

    Thanks again

    Nick

  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Manipulating Excel (A2K SR1)

    Hans,

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

    Gary
    (It's been a while!)

  12. #12
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Manipulating Excel (A2K SR1)

    Thanks for the suggestion. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

  13. #13
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Manipulating Excel (A2K SR1)

    Just curious,

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

    Gary
    (It's been a while!)

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Manipulating Excel (A2K SR1)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •