[AccessD] Reports to Excel

The other issue when the user is watching the Excel sheet updating, if the user gets impatient and clicks anywhere in the active Excel window - the code stops and an error is generated.  If it's a long process, I'd recommend a progress bar to let the user know it's still working.

Jim 
jhewson at karta.com
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, May 01, 2007 10:41 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Reports to Excel

Hi Mark

That's what I mean. Unless you wish the user to sit and watch what's going on, make the worksheet invisible, set a range and not a selection, and operate on the range. For example:

  Dim rng As Range
  
        Set rng = .Range("A1:AC1")
        With rng.Font
          .Bold = True
          .Name = "Arial"
          .Size = 12
        End With

  Set rng = Nothing

Much faster too.

/gustav

>>> markamatte at hotmail.com 01-05-2007 17:16 >>>
Thanks Gustav,

I'didn't really care for what the macro recorder gave.  I found some code 
examples on line...and modified.  Below is what I used.

Thanks,

Mark A. Matte

    With appExcel
        .Workbooks.Open strpathname, 0
        .Visible = True 'just to watch the sheet
        .Range("A1:AC1").Select
        .Selection.Font.Bold = True
        .Selection.Font.Name = "Arial"
        .Selection.Font.Size = 12
        Do Until MyRst.NoMatch
            Counter = Counter + 1
            .Cells(1, Counter) = MyRst!Reas_cd
            If MyRst![View] = 0 Then .Cells(1, Counter).Font.Color = 255
            MyRst.FindNext strSQL
        Loop
        .Columns.AutoFit
        .Rows.AutoFit
        .Columns.Borders.Color = vbBlack
        .ActiveWorkbook.SaveAs FileName:=strpathnew, FileFormat:=xlNormal
    End With
     appExcel.Quit


>From: "Gustav Brock" <Gustav at cactus.dk>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: <accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Reports to Excel
>Date: Tue, 01 May 2007 11:07:08 +0200
>
>Hi Mark
>
>You really should clean up the code. Indeed, get rid of all the Selection 
>stuff you do not need (that's most if not all of it) and replace with 
>Ranges - if you don't, your code will look like, well, it has been created 
>by the Macro Recorder!
>
>/gustav
>
> >>> jwcolby at colbyconsulting.com 30-04-2007 22:21 >>>
>
>As Jim mentioned, you might want to clean up the code, but you don't have 
>to.



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com