VBA以值, 執行Open, Save As, Rename

2009-02-15 10:24 pm

如何跟據 CELL 內的 [值] or 計算結果
開啟 .xls 檔 >更改 Sheets 名稱 > 另存新檔
..
Cell B2 = 8052 or Vlookup 後的值 8052 ‘(客戶編號)
Cell C3 = 9398 ‘(DN 編號)
Cell D10 = DN Record ‘(DN 紀錄)
Cell A33 = 20/2/2009 '(日期)
..
..Open file [8052.xls] ‘(等於 [Cell B2] )
..Open file [DN Record.xls] ‘(等於 [Cell D10] )
..More [8052.xls] 內的 [ActiveSheet] to [DN Record.xls]
..Rename [ActiveSheet] to “9398”
.......( 更改Sheet 名稱 = 來源.xls檔內的 [Cell C3])
.......( <現在的來源.xls檔是8052.xls> )
..Save As [8052.xls] and rename to [8052-20090222.xls]
.......( 另存新檔 = [Cell B2] + [Cell A33] + [“.xls”] )
..Close File all files

更新1:

Hi CW Wan, 多謝你的回應, 我曾嘗試用錄製巨集, 但只董皮毛, Take Course VBA 班上的 Miss 又話等下星期在班上討論 ... 遠水不能救近火, 蒙各位賜教。

更新2:

HI, Calvin 蒙賜教, 快夾妥, 一矢中的, Thank you very mush !!!

回答 (3)

Calvin L

2009-02-16 5:34 am

✔ 最佳答案

Try this...

Dim sCstNo as string, sDnNo as string, sDnRec as string, sDat as strring

sCstNo = [B2]
sDnNo = [C3]
sDnRec = [D10]
sDat = [A33]

Workbooks.Open Filename:= sCstNo & ".xls"
Workbooks.Open Filename:= sDnRec & ".xls"
Windows(sCstNo & ".xls").Activate
ActiveSheet.Move Before:=Workbooks(sDnRec).Sheets(1)
Windows(sDnRec & ".xls").Activate
ActiveSheet.Name = sDnNo
ActiveWorkbook.SaveAs Filename:= sCstNo & "-" & sDat & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Calvin Lee, [email protected]

P.S. you have to make sure you want to
ActiveSheet.MOVE Before:=Workbooks(sDnRec).Sheets(1)
or
ActiveSheet.COPY Before:=Workbooks(sDnRec).Sheets(1)

👍 0 👎 0

Outing360hk

2009-02-16 6:18 am

Hi CW Wan, 多謝你的回應, 我曾嘗試用錄製巨集, 但只董皮毛, Take Course VBA 班上的 Miss 又話等下星期在班上討論 ... 遠水不能救近火, 蒙各位賜教。

👍 0 👎 0

用戶7469

2009-02-16 3:28 am

有無嘗試用錄製巨集（Record Macro）

👍 0 👎 0

收錄日期: 2021-05-01 16:26:17
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20090215000051KK01000

檢視 Wayback Machine 備份