Access 97 Automation - VBA For Excel 
Author Message
 Access 97 Automation - VBA For Excel

I am trying to use Access 97 as an Automation client to send data to an
Excel file, then open the Excel file and do some special formatting
operations.  I am able to do most of the coding through simple automation
commands, but I am really stumped on one particular problem.  The data sent
to the Excel file can vary as far as the total number of rows that will be
in the workbook, and one of the things I must do is to select the cells in
the last row and format them as Bold font.  But how do I find and select
that last row?

Here is the fragment of code produced by the Excel 97 Macro Recorder that
does it for a particular instance:

   >>>>>>>.......
    Range("A1").Select                                'Start from top left
    ActiveCell.SpecialCells(xlLastCell).Select  'go to last cell on
worksheet
    Rows("172:172").Select                    'select that row
    Selection.Font.Bold = True                  'format the selected cells
    >>>>>>

This code works fine, but the problem is that it uses the absolute row
designation (172) to refer to the row.  That is OK for this instance but
the next time there may (will!) be more or less numbers of rows, so the
code will fail.  What I need is a way to (1) go to the last row, wherever
it is  (2) select all the cells in that row  (3) do the formatting.  While
we are at it, how about selecting the last column, which can vary also?

I tried to use SendKeys to send the "+( )" command ie SHIFT-SPACEBAR to
select the last row, that highlighted the cells but did not select the
cells for the formatting.

Any ideas are most appreciated.  I have searched the Range, Rows, etc XL
Help topics until I am blue in the face :-)
--
Pete B



Mon, 31 Jul 2000 03:00:00 GMT  
 Access 97 Automation - VBA For Excel

Hi Pete,

Try using this function that I often use in Excel.  You can just reference
it to the Application object probably.

Function findlast()
    For rowind = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
        findlast = rowind
        If Not IsEmpty(ActiveSheet.Cells(rowind, 1).Value) Then Exit
Function
    Next rowind
End Function

HTH

--
Just my $.001
Dev Ashish
---------------

:I am trying to use Access 97 as an Automation client to send data to an
:Excel file, then open the Excel file and do some special formatting
:operations.  I am able to do most of the coding through simple automation
:commands, but I am really stumped on one particular problem.  The data sent
:to the Excel file can vary as far as the total number of rows that will be
:in the workbook, and one of the things I must do is to select the cells in
:the last row and format them as Bold font.  But how do I find and select
:that last row?
:<<<snipped>>>



Mon, 31 Jul 2000 03:00:00 GMT  
 Access 97 Automation - VBA For Excel

Rows(ActiveCell.Row).Select will select the current row
Columns(ActiveCell.Column).Select will select the current column.



Mon, 31 Jul 2000 03:00:00 GMT  
 Access 97 Automation - VBA For Excel

That looks like exactly what I need, Randy, I will give it a try.

Thanks much, you made my day  :=)

--
Pete B



Quote:
> Rows(ActiveCell.Row).Select will select the current row
> Columns(ActiveCell.Column).Select will select the current column.



Tue, 01 Aug 2000 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Automation Problem with Access 97/Excel 97

2. vba code to copy table from access 97 to excel 97

3. Excel 97 VBA vs Excel 2000 VBA

4. Sending data to excel from access - automation 97.

5. Run Access 97 Automation Subroutines from Excel?

6. Excel References from Access (97) VBA

7. Access 97: VBA-DAO-Access 97 synchronization problem?

8. Import From Excel 2000 with VBA code into Access 97

9. Using VBA to Send info from Excel Back to Access in Office 97

10. Access 97 VBA v Access 2K VBA - different ?

11. Access 2000 VBA Handbook vis a vis Access 97 VBA Handbook

12. Automation seems to hang with Access 97 / Word 97

 

 
Powered by phpBB® Forum Software