[AccessD] OT: Excel 97 Macro

Gustav,

Must have been mom.  ;-)

I used dbase and 123 back in the 80s but never got into them at anything
close to this depth.  In fact I should guess that automation as we know it
didn't even exist.

You mention Macro Sheets.  I know about macro modules, which are just code
modules as we have in Access.  Are we talking about building up a macro
library in a separate workbook and then referencing that workbook from
another workbook, like we reference an MDA from another MDB?

Knowing the little I know about dynamically setting references in a running
Access App, I am not sure how to apply that to an Excel spreadsheet received
from a client.  These sheets have no code in them, or if they do that are
not my code.  IOW, I have to somehow insert a module or at least a macro
into a module inside of the received sheet, even in order to get the code
running that would link to my "library" workbook (assuming that this is even
the strategy you are discussing).  It seems that if I go to the bother of
doing that, then I would just insert all of the code I needed (an entire
module) into the target worksheet and just run it.

Given the lack of specific response to my question I guess I'll dive in to
trying to figure out how to do that process.  Open a worksheet, insert a
module, and run code.  I have done everything except insert the module, but
I suspect that process isn't too difficult.  

What would be nice is to know the ending row of the data.  Does anyone know
off the top of their head how to determine the max size (the largest row
index) of a populated row?  Remember I am working with what appears to be a
table - fixed column count / fixed row count.  What is the numbers of
records in that table (as determined in Excel).  I can always if necessary
just link the table in and get a record count but it would be nice to figure
it out in Excel.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Friday, March 21, 2003 3:00 PM
To: John W. Colby
Subject: Re: [AccessD] OT: Excel 97 Macro


Hi John

Don't you regularly brag of your experience with old DOS applications
like dBase and Lotus 1-2-3? Or is that your mother, Charlotte?

Anyway, Lotus 1-2-3 and Quattro Pro both sported the use of separate
macro sheets to keep code separated from data.
You can do the same in Excel - it's like the Named Ranges - it's a
splendid feature but only few use it.

The additional advantage is that your macro workbook can stay
protected if you write protect the file.

Here's a snippet from the start of a main function called from Access
(as you learnt me).
Access opens the macro book and runs this macro. This macro opens a
template and another workbook (with data only), then saves the
template as a temporary workbook which the function then manipulates:

---

' Filenames of standard workbooks.

  Public Const cstrWorkbookForm As String = "PeriodForm.xls"
  Public Const cstrWorkbookData As String = "PeriodData.xls"
  Public Const cstrWorkbookTemp As String = "PeriodTemp.xls"
  Public Const cstrWorkbookSend As String = "PeriodSend.xlt"
  Public Const cstrWorkbookDist As String = "PeriodSend.xls"

[snip]

Function CreateSurvey() As Boolean
  
  Const cstrWorksheetName       As String = "Survey"
  ' Maximum length of name for a sheet.
  Const cbytWorksheetNameLen    As Byte = 31
    
  Dim wkb                 As Workbook
  Dim wkbPeriod           As Workbook
  Dim wkbPeriodData       As Workbook
  Dim wks                 As Worksheet

  Dim rng                 As Range
  Dim rngGroups           As Range
  Dim rngFormat           As Range
  Dim rngData             As Range
  Dim rngTxtHeader        As Range
  Dim rngTxtPeriod        As Range
  Dim rngTxtPeriodData    As Range
  Dim rngTxtSeason        As Range
  Dim rngTxtSeasonData    As Range

[snip]

  With Application
    ' Disable automatic recalculation and screen updating.
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
  End With
  
  ' For debugging only.
  With Application
  '  .Visible = True
  '  .ScreenUpdating = True
  End With
  
  ' Close all files except the first if more than one should be open.
  Call CloseOtherWorkbooks(False)
  
  strPath = ActiveWorkbook.Path
  
  ' Open file with statistics as template.
  Set wkbPeriode = Workbooks.Add(strPath & vbBackSlash & cstrWorkbookForm)
  ' This is used by DLookupSystem():
  Set wkbPeriodData = Workbooks.Open(strPath & vbBackSlash &
cstrWorkbookData, UpdateLinks:=0, ReadOnly:=True)

  Application.DisplayAlerts = False
  strPathFileName = strPath & vbBackSlash & cstrWorkbookTemp
  ' Save statistics in temporary file in the smaller Excel5-format.
  wkbPeriod.SaveAs strPathFileName, xlExcel5
  Application.DisplayAlerts = True
  
  wkbPeriod.Activate
  ' Set ranges.
  Set rngGroups = Range("xlsGroupInterval")
  Set rngFormat = Range("FormatHeader")
  Set rngData = Range("xlsSurvey")
  Set rngTxtHeader = Range("TextHeader")
  Set rngTxtPeriod = Range("TextPeriod")
  Set rngTxtPeriodData = Range("TextPeriodData")
  Set rngTxtSeason = Range("TextSeason")
  Set rngTxtSeasonData = Range("TextSeasonData")

[snip]

Run formatting code ...

---

Finally (cut away from this example, no big deal) the finished range
in the temporary workbook is copied into an empty template workbook -
clean with no code or left-over data - and saved as the finished
workbook.

/gustav


----------------------------------------------------
Is email taking over your day?  Manage your time with eMailBoss.  
Try it free!  http://www.eMailBoss.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 4424 bytes
Desc: not available
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030322/c470928f/attachment-0001.bin>