2015年10月16日

儲存 Excel 指定區域為 bat 批次檔

我平常習慣用 Excel 彙總資料並產生批次的語法,但每次都要重複以下的步驟:
  1. 複製函數產生的命令指令
  2. 將指令貼到記事本軟體
  3. 另存成 .bat 批次檔案
  4. 執行 bat 檔案

透過以下這段語法,可以將選擇 Excel 函數產生出來的結果複製成 bat 檔案。

Sub myTSave()
Dim myFolder As String
'By Joe Was.
'Save Range as Text File.

ActiveSheet.Activate
'Ask user to select range for text file.
Set myRange = Application.InputBox(prompt:="請選擇要儲存的資料範圍!", _
Title:="Text File Range!", Type:=8)
myRange.Select
Selection.Copy
'This temporarily adds a sheet named "Test."
Workbooks.Add
Sheets.Add.Name = "Test"
Sheets("Test").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'Ask user for folder to save text file to.
myFolder = Application.GetSaveAsFilename(fileFilter:="Text Files (*.bat), *.bat")
'Save selected data as text file in users selected folder.
ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlText, CreateBackup:=False
'Remove temporary sheet.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
ActiveWorkbook.Close savechanges:=False
'Indicate save action.
MsgBox "複製指令檔: " & myFolder & " 儲存成功!"
'Go to top of sheet.
Range("A1").Select
End Sub


Ref.



沒有留言: