[AccessD] OT: Excel 97 Macro

Gustav Brock gustav at cactus.dk
Fri Mar 21 14:00:00 CST 2003


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?




More information about the AccessD mailing list