Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

? asked in 電腦與網際網路程式設計 · 1 decade ago

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

比方說我有一個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

3 Answers

Rating
  • 1 decade ago
    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, suihug-multimedia@yahoo.com.hk

  • 1 decade ago

    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): 自己
  • ?
    Lv 4
    1 decade ago

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

    Me.Path可以取得目錄

    Me.Name可以取得檔名名稱

    (不知道你要甚麼)

    Dim s As String

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

Still have questions? Get your answers by asking now.