作者gone19 (gone19)
看板Office
標題[問題] VBA_xls轉檔csv,檔名多字串
時間Thu Jan 6 17:06:34 2022
(若是和其他不同軟體互動之問題 請記得一併填寫)
軟體:Office Excel
版本:2013
我在網路上參考VBA CODE. XLS轉檔CSV
檔案類型轉檔成功
不過檔名都會出現XXXXXX.xls
原本的檔名是沒有.xls
請問可以就原有的CODE把.xls去掉嗎?
謝謝!
------------------------------------
Sub SaveToCSVs()
Dim fDir As String
Dim wB As Workbook
Dim wS As Worksheet
Dim fPath As String
Dim sPath As String
Dim sPath As String
fPath = "C:\Users\ra\Desktop\xls\"
sPath = "C:\Users\ra\Desktop\csv\"
fDir = Dir(fPath)Do While (fDir <> "")
If Right(fDir, 4) = ".xls" Or Right(fDir, 5) = ".xlsx" Then
On Error Resume Next
Set wB = Workbooks.Open(fPath & fDir)
'MsgBox (wB.Name)
For Each wS In wB.Sheets
wS.SaveAs sPath & wB.Name & ".csv", xlCSV
Next wS
wB.Close False
Set wB = Nothing
End If
fDir = Dir
On Error GoTo 0
Loop
End Sub
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 60.250.74.30 (臺灣)
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1641459996.A.765.html
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1641459996.A.765.html
※ 編輯: gone19 (60.250.74.30 臺灣), 01/06/2022 17:07:06
→ waiter337: wS.SaveAs sPath & wB.Name & ".csv", xlCSV 改成 01/06 23:33
→ waiter337: wS.SaveAs sPath & replace(wB.Name,".xls","") & ".cs 01/06 23:34
→ waiter337: v", xlCSV 01/06 23:34
→ waiter337: 不確定正不正確 測試時 別用原先的檔案資料夾 01/06 23:35
→ waiter337: 以免破壞原有的舊檔案 先創新資料夾測試是否成功 01/06 23:35
→ gone19: 真的非常感謝@waiter337！我成功了 01/07 11:31
→ waiter337: 不過上面的方式不完整 遇到xlsx檔案會出錯 01/08 08:31
→ waiter337: 建議從'MsgBox (wB.Name) 以下開始修改 01/08 08:32
→ waiter337: dim wBN as string 01/08 08:32
→ waiter337: wBN = replace(wB.Name,".xls","") 01/08 08:33
→ waiter337: wBN = replace(wBN,".xlsx","") 01/08 08:33
→ waiter337: For Each wS In wB.Sheets 01/08 08:33
→ waiter337: wS.SaveAs sPath & wBN & ".csv", xlCSV 01/08 08:33
→ waiter337: Next wS 01/08 08:34
→ waiter337: wB.Close False 01/08 08:34
→ waiter337: 糟糕 第二 與 第三 行 互換 沒注意到 .xlsx要先過濾 01/08 08:35
→ waiter337: 第二行改 wBN = replace(wB.Name,".xlsx","") 01/08 08:35
→ waiter337: 第三行改 wBN = replace(wBN,".xls","") 01/08 08:36