Gustav Brock
gustav at cactus.dk
Sat Mar 22 10:32:00 CST 2003
Hi John
> Must have been mom. ;-)
All right.
> 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.
You can apply addins or references to a workbook and I guess
dynamically too but I haven't worked with that.
My suggestion (which works very well for me) is to let the macro
workbook open and manipulate the workbook(s) with data and/or
formatting. Look at my code snippet again which runs in the macro
workbook:
[snip]
' Open file with statistics as template.
Set wkbPeriod = 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
[snip]
wkbPeriod is the workbook to manipulate. All preformatting is done in
the template cstrWorkbookForm so only formatting etc. specific to this
single dataset has to be carried out. This saves a lot of work and
code. As the first task (shown above) wkbPeriod is saved to a working
file which later will be the finished workbook.
wkbPeriodData is another workbook which carries all the data to be
filled into wkbPeriod. The data workbook could be your customer data.
For my task it also contains a "system"-sheet.
Sheets and/or Named Ranges in wkbPeriodData are linked as tables to my
Access app which means that all data can be collected and calculated
in Access prior to calling the macro workbook and performing the
formatting etc. in Excel.
The complete function I use is quite complicated and very specific - I
can only understand it through my comments - and will rather bring
confusion than help to you.
I know this can be done in a lot of other ways. The great thing,
however, is that the clear separation between data, code and
basic formatting makes debugging if not simple then much easier.
/gustav
> 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.