Charlotte Foust
cfoust at infostatsystems.com
Mon Mar 24 14:46:04 CST 2003
>>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?
I missed this on Friday, Gustav. Are you trying to start a war? I
don't BRAG about that experience, I ADMIT to it. There's a big
difference. And John is far too old to be the son of someone as young
as I am. <VBG>
Charlotte Foust
-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk]
Sent: Friday, March 21, 2003 12: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
> I have a similar requirement, but I want to do something specific to
> selected ranges, from inside of Access using automation, to any excel
> spreadsheet I choose. Some background.
> As I posted earlier this week, I am seeing problems where the data in
> the first cells of a given column are numeric, but lower down the
> cells switch to text. The data is still numeric, i.e. the data is
> still simply a number
> - 2, 3.4 etc. but the FORMAT in the cell is text. As a result, when
linked
> to Access and displayed, Access decides that the column is numeric
because
> of the first few cells at the top of the column, then can't figure out
what
> to do with the cells that are actually text down below. This is all
> discussed in
http://support.microsoft.com/default.aspx?scid=kb;en-us;162539
> as Hayden pointed out.
> The "Fix" is to go into the spreadsheet, select the column of data,
> and prepend a space to the beginning of each cell, which apparently
> causes any numeric data to turn into text. Now, in the linked data
> inside of Access, because the entire column is a single type of data
> (text) it can be displayed all the way down. I can then use a cLng
> (or whatever is
> necessary) to convert the data back to the data type needed.
> PITA, but this is MS after all ;-)
> So, the code shown for doing this is:
> Sub Addspace()
> Dim cell As Object
> For Each cell In Selection
> cell.Value = " " & cell.Value
> cell.Value = Right(cell.Value, Len(cell.Value) - 1)
> Next
> End Sub
> run as a macro inside of Excel. Of course if this is to be generic,
> any given spreadsheet will not have this macro inside of it so I will
> have to insert the macro. Further I have to add code to select a
> given range, then run this code.
> Several years ago, when I lived in Mexico, I did a bunch of formatting
> of Excel. What I did in that case was to build a workbook in which I
> created my macros. I then copied the workbook to a new name, imported
> the
> worksheet(s) that needed formatting, and then ran the formatting
macros. I
> could do the same thing here but I would prefer to have code stored
inside
> of Access, open the spreadsheet that needs this process performed on
it,
> insert a module with the code, select the area, and run the macro.
> If this sounds like a major PITA to fix a bug in Excel / Access
> interaction, I couldn't agree more. But we do what we have to do.
> BTW, I have also seen dates with similar problems A data column looks
> prefect inside of Excel, but when linked and viewed inside of Access,
> some dates are hosed. The problem is exactly the same, they are text.
> Before I go off re-inventing the wheel, does anyone have code for
> doing anything similar? Pieces for doing parts of what I am trying to
> do? Interest in working with me to jointly solve this problem?
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com