Excel的VBA裡，該怎麼寫取得這個檔的檔名並另存同名新檔

Question

比方說我有一個Excel檔

因為從另一個系統撈下來

儲存的格式為「文字檔(Tab字元分隔)」

我想要另存新檔

並存成同樣檔名但為「Microsoft Office活頁簿」

我知道另存新檔可以這樣寫(用錄製巨集再參考其程式碼)

ActiveWorkbook.SaveAs Filename:= _

"C:\Documents and Settings\Tom Chen\My Documents\Book1.xls", FileFormat:= _

xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _

, CreateBackup:=False

但這樣只能存成固定的檔名

因為單用Save(儲存)，好像沒辦法改變存檔的格式

只好用SaveAs(另存新檔)，並於參數中設定為xlNormal

有何方法可取得此檔檔名並存成xlNormal

Answer 1

Favorite Answer

ActiveWorkbook.Path & "\" & ActiveWorkbook.Name 應該可以指定畫面上現時看著的檔案

...但這句子看起來好似會Save作.txt檔, 所以我改了全句如下. 試試看吧

ActiveWorkbook.SaveAs Filename:= _

ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 3) & "xls", FileFormat _

:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _

False, CreateBackup:=False

Calvin Lee, [e-mail]

Answer 2

Dim name as string

name=Activeworkbook.name & ".xls"

ActiveWorkbook.SaveAs Filename:= _

"C:\Documents and Settings\Tom Chen\My Documents\" & name & "", FileFormat:= _

xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _

, CreateBackup:=False

試看看吧

Source(s): 自己

Answer 3

我雖然不怎麼懂啦..但聽起來好像是要取得檔名..

Me.Path可以取得目錄

Me.Name可以取得檔名名稱

(不知道你要甚麼)

Dim s As String

s= Me.Path & "\" & "檔名"