TIPS FROM PINKILLER.COM

(1) 在 excel 中 執行 函數方法 

代碼:

Private Sub Worksheet_Change(ByVal Target As Range)  a = Application.Sum(Range("b1:d1"))  Range("a2").Value = a End Sub

application.指令

(2) 當前excel文件 , 所在路徑

代碼: ThisWorkbook.Path

(3) 找尋文件夾中的檔案

找尋文件夾中的檔案 代碼: Sub AAA() Dim xlsFile As String xlsFile = Dir(ActiveWorkbook.Path & "\*.XLS") Do If InStr(1, xlsFile, "總匯") = 0 Then     Cells(([A65536].End(xlUp).Row + 1), 1) = xlsFile  End If   xlsFile = Dir Loop Until Len(xlsFile) = 0 End Sub 代碼: xlsFile = Dir(ActiveWorkbook.Path & "\*.XLS") Dir 用來查出檔案名稱 , 可用 "*" 及 "?" 去查 如找出第一個後, 要查第二個就可 打以下例子 代碼: xlsFile = Dir 代碼: InStr(1, xlsFile, "總匯") Instr 用來查出字串內有沒有包含某個字串 , 如有就會返回大於0的整數 , 找不到就返回0.

(4) 查出最後一行(有資料)的位置

代碼:

[A65536].End(xlUp).Row

查出最後一行(有資料)的位置

是一個好好用工具

代碼:

Cells(([B65536].End(xlUp).Row + 1), 1)

最後一行之下一行位置,用來加新資料時很有用

(5) 用 shell 指令中 target 有 space 的問題

I found the command line switches on Microsoft's website. It was contained in Microsoft Knowledge Base Article - Q241422

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q241422

Here is an example of code that I used and tested:


Shell "C:\Program Files\Windows Media Player\wmplayer.exe /play /close" & Chr(34) & "C:\Apps\PopCap Games\Bejeweled Deluxe\sounds\bad2.mp3" & Chr(34), vbHide


If the path to the file that you want to play has any spaces in it (as your example did with the My Documents), then the entire path and file need to be enclosed in quotation marks (that is what the chr(34) is).

The /play tells the media player to play the file. The /close tells it to close after it's done or it will leave the media player open and hidden and taking up resources.

I hope this helps

用 shell 指令時 , 發現 target 位置有問題 ,
(1) 試用 "%20" 代替 space , failed
(2) 試用 位置 "c:\1.mp3" , 成功
原來 target 來 有 space 就要用 chr(34) ( 代表 " 這個附號 )