Emily 分享學習 VBA

Use VBA SaveAs in Excel 2007

cat14051 — Tue, 01 Jul 2008 12:37:29 +0800

Use VBA SaveAs in Excel 2007

Information

If you run the code in Excel 2007 it will look at the FileFormat of the parent workbook and save the new file in that format.
Only if the parent workbook is an xlsm file and if there is no code in the new workbook it will save the new file as xlsx,
If the parent workbook is not an xlsx, xlsm, or xls then it will be saved as xlsb.

This are the main formats in Excel 2007 :

51 = xlOpenXMLWorkbook (without macro's in 2007, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007 with or without macro’s, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007, xls)

More details and examples: http://www.rondebruin.nl/saveas.htm

(閱讀全文)

Application.filesearch Replacement For Office 2007

cat14051 — Sat, 28 Jun 2008 00:20:08 +0800

Application.filesearch Replacement For Office 2007

Please read this post:

http://www.ozgrid.com/forum/showthread.php?t=71409

關於清空Office的剪切板 (Clear Office Clipboard)

cat14051 — Thu, 01 May 2008 10:41:44 +0800

關於清空Office的剪切板

wangminbai 一位令我對 Excel 再產生興趣的小伙子

A remarkable topic to clear Office Clipboard written in Chinese Simplified.

(閱讀全文)

More than 100000 Visitors

cat14051 — Tue, 01 Apr 2008 16:45:16 +0800

More than 100000 Visitors

Thanks

判斷 Excel 單格是否處於編輯狀態 (Determine Excel in Edit Mode)

cat14051 — Sun, 03 Feb 2008 12:17:32 +0800

判斷 Excel 單格是否處於編輯狀態

以下代碼就是當處於編輯狀態時在Excel的狀態欄上顯示 正處於編輯狀態…

The code will display "Excel in edit mode ......" in Status bar if Excel is in edit mode

(閱讀全文)

顯示自定檢視模式名稱 CurrentViews

cat14051 — Thu, 03 Jan 2008 13:34:54 +0800

顯示自定檢視模式名稱 CurrentViews

Function CurrentView(Optional DummyArg As Variant) As String
Dim cbo As CommandBarComboBox

On Error Resume Next
Set cbo = Application.CommandBars.FindControl(ID:=950)

On Error GoTo 0
If cbo Is Nothing Then
    With Application.CommandBars.Add(Temporary:=True)
        Set cbo = .Controls.Add(ID:=950)
        .Enabled = False
    End With
End If

CurrentView = cbo.Text

End Function

Sub Test()
    'Application.DisplayStatusBar = True
    'Application.StatusBar = CurrentView
    MsgBox CurrentView
End Sub

Download Videos from YouTube

cat14051 — Fri, 14 Dec 2007 21:27:49 +0800

Download Videos from YouTube

Please read Download Videos from YouTube by Ivan F Moala

More New Excel VBA Examples

Google Chart API

cat14051 — Sun, 09 Dec 2007 12:22:14 +0800

Google Chart API

The Google Chart API lets you dynamically generate charts.

For details please refer to http://code.google.com/apis/chart/

You'll find more examples here.

Using VBA to access the Net (Tushar Mehta)

cat14051 — Sun, 05 Aug 2007 12:11:15 +0800

Using VBA to access the Net

Tushar Mehta:
Over the past few years there has been an increase in people who want to programmatically access information available over the Internet. There are a few different ways of doing so with results available in a few different formats. In this chapter I discuss some of the methods and formats. The methods include the use of InternetExplorer and XMLHttp and the formats include the “human friendly” HTML as well as XML and JSON. As part of the discussion of XML, I present a XSL stylesheet to convert a webservice XML output into a SpreadsheetML form that can then be opened directly by Excel. By the end of the chapter, the reader will know how to develop fully functional code to support concurrent asynchronous VBA access to multiple web sites.

For details please refer to

http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/index.htm

移動 Mouse 定位在 A8

cat14051 — Sun, 24 Jun 2007 23:35:48 +0800

移動 Mouse 定位在 A8

'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  :
' Author    : Colo
' Purpose   : Set Cursor Position
'---------------------------------------------------------------------------------------

Public Declare Function SetCursorPos Lib "user32" (ByVal X As Long, ByVal Y As Long) As Long

Sub CursorPosition()
    Dim X As Long, Y As Long
    Dim Rg As Range
    Set Rg = Range("A8")
    X = ActiveWindow.PointsToScreenPixelsX((Rg.Left) * 96 / 72 * ActiveWindow.Zoom / 100)
    Y = ActiveWindow.PointsToScreenPixelsY((Rg.Top) * 96 / 72 * ActiveWindow.Zoom / 100)
    ' 72 (points per inch) is divided by a 96 (pixels per inch),
    ' then multiplied the percentage of the ActiveWindow.Zoom.
    ' Please note, a 96 /inch is a standard pixel resolution,
    ' so depending on the case, this coefficient must be changed to suit.
    SetCursorPos X, Y
End Sub

(閱讀全文)

Automatic Mouse Movement

cat14051 — Thu, 21 Jun 2007 08:19:44 +0800

Automatic Mouse Movement

Worth to read this example: Automatic Mouse Movement on UserForms

I have been using an Excel addin which I had need to automate some procedures overnight. There are a number a userforms which have certain commandbuttons to click. Unfortunately, you cannot access the Command Button click event from external code.
So I searched far and wide for a solution. I found the MouseMove API
routine and adapted it for VBA Userforms.