Tip 0409
Home Up Past Meetings Notes Tips Directory Links of Interest Site Map FAQs

Excel Tips (Sept. 2004)

Digital Certificates and Trusted Sources for Running Excel Macros under High Macro Security 
Links and Notes page on excelsig.org
 
Excel VBA: How to retrieve the Range Name of the Active Cell?
ActiveCell.Name.Name  
If it does not have a name, it error.  
Function wwActiveCellName2() As String
    On Error GoTo e1
    wwActiveCellName2 = ActiveCell.Name.Name
    Exit Function
e1:
    wwActiveCellName2 = ""
End Function

Sub Test2()
    Dim str1 As String
    str1 = wwActiveCellName2
End Sub
Access: How to put the current record at the top of a continuous form?
'move last, then find first.   from Kevin Waddle <thewaddles@SBCGLOBAL.NET>  Access-L 9/13/2004
Set rscMe = Me.RecordsetClone
strCriteria = "SchDate=#" & (Date) & "#"
rscMe.MoveLast
rscMe.FindFirst strCriteria
 
 
Access: linking to two tables in different databases (under different security)    3-Sept-2004  .access.tablesdbdesign
 
Ctrl-Enter, Ctrl-Shift-Enter: 
Select several cells, say B2:B20.    In the active cell (B2), enter the formula =A2.
If you press the Enter key, only B2 gets the formula.
If you press Ctrl-Enter, you enter the relative reference:   B2 gets =A2, B3 gets =A3, ... B20 get =A20.
If you press Ctrl-Shift-Enter, you enter an array reference:    B2:B20 all get {=A2}
Ctrl-Enter will paste into only the visible cells selected in an autofiltered range.
Ctrl-Shift-Enter will paste the array into the entire selected range, including the hidden cells.
If you Select Visible Cells, Ctrl-Enter pastes into all selected cells, Ctrl-Shift-Enter pastes into only the first connected group of selected cells.
 
Writing Your First VBA Function in Excel - ExcelTip.com
 
Using Folder API to manage Backend database File connections:
From DBS on microsoft.public.access.modulesdaovba, 8/30/2004 "VBA reference to Access Linked Table'
[Techniques for allowing a user to change the link to the backend database.]
 
How To Use a Common Dialog File Open Dialog with Win32 API
http://support.microsoft.com/default.aspx?scid=kb;en-us;161286

ACC2000: How to Use ADOX to Create and Refresh Linked Jet Tables
http://support.microsoft.com/default.aspx?scid=kb;en-us;275249&Product=acc

Basically, just replace the reference to Northwind with a call to the function that makes use of the Common Dialog tool above.

If you don't want to use ADO, but rather DAO: http://www.mvps.org/access/tables/tbl0009.htm (this site also has really good sample code for using the comdlg32.dll api)

More info on using the Common Dialog API:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnovba00/html/CommonDialogsPartI.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima01/html/ima0187.asp
 
 
PrintSetup on selected Sheet Group  [Tom Ogilvy 8/27/2004]
Sub formatToPrint()
For each sh in Worksheets
  With sh.PageSetup
     .Orientation = xlLandscape
  .Zoom = False
  .FitToPagesWide = 1
  .FitToPagesTall = 1
  End With
  ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
  Sheets("Cover page").Select
End Sub
Copy Excel Chart as a Picture
Hold down the shift key and choose Edit -> Copy Picture...
or in vba: ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen
[McGimpsey - 8/27/2004]
 

 
Google search engine add-in by Ron de Bruin saves me a lot of time looking up (VBA) in books   [David Adamson - 8/25/2004]
 
Get Intersection of ranges using labels
Select the entire table including the Column Headings.
Excel Menu: Insert, Name, Create,
Check Left Side, Top Row.
You will create 1xn range_names based upon the "label" in the first cell on
the left.

Now your second page should be =Intersect(range1, range2)
where range1 is Indirect("mike"),  range2 is Indirect("Total")
or just "=range1 range2"
 
Apply Proper Case to a range of cells:
For Each cell In Selection
        cell.Value = WorksheetFunction.Proper(cell.Value)
Next cell
 
Or do it manually:
Assuming Names and Addresses are in columns A & B
Insert two columns at C
In the new C =Proper(A1)
In the new D =Proper(B1)
Select C1:D1, Copy Down.
Select C:D, Copy, Paste Special Values.
Inspect and edit.
Delete Columns A:B
 
Copy only Formulas to a new sheet
Dim Rng1 As Range
For Each Rng1 In Worksheets("Sheet1").SpecialCells(xlCellTypeFormulas)
    Worksheets("Sheet2").Range(Rng1.Address).Formula = Rng1.Formula
Next Rng1
[Chip Pearson - 8/12/2004 .excel.programming]
 
Count Records by Date.  Don't count duplicate records.
Use An Advanced Filter  that extracts Date and Variable, Copy to New Location,
Unique Rows will eliminate all duplicates.
Then a Pivot table can count by date.
[Rasey - 8/12/2004]
 

[Run SQL-Server Stored Proc] [Access Link DBs] [Digital Certificates and Trusted]

For questions or comments concerning content on this website: Stephen Rasey
Design of this site by Cheryl D. Wise
Copyright © 2000-2004 by WiserWays. All rights reserved.
Revised: 2005-07-10 01:09 .