作者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://webptt.com/cn.aspx?n=bbs/Office/M.1641459996.A.765.html
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Office/M.1641459996.A.765.html
※ 编辑: gone19 (60.250.74.30 台湾), 01/06/2022 17:07:06
1F:→ waiter337: wS.SaveAs sPath & wB.Name & ".csv", xlCSV 改成 01/06 23:33
2F:→ waiter337: wS.SaveAs sPath & replace(wB.Name,".xls","") & ".cs 01/06 23:34
3F:→ waiter337: v", xlCSV 01/06 23:34
4F:→ waiter337: 不确定正不正确 测试时 别用原先的档案资料夹 01/06 23:35
5F:→ waiter337: 以免破坏原有的旧档案 先创新资料夹测试是否成功 01/06 23:35
6F:→ gone19: 真的非常感谢@waiter337!我成功了 01/07 11:31
7F:→ waiter337: 不过上面的方式不完整 遇到xlsx档案会出错 01/08 08:31
8F:→ waiter337: 建议从'MsgBox (wB.Name) 以下开始修改 01/08 08:32
9F:→ waiter337: dim wBN as string 01/08 08:32
10F:→ waiter337: wBN = replace(wB.Name,".xls","") 01/08 08:33
11F:→ waiter337: wBN = replace(wBN,".xlsx","") 01/08 08:33
12F:→ waiter337: For Each wS In wB.Sheets 01/08 08:33
13F:→ waiter337: wS.SaveAs sPath & wBN & ".csv", xlCSV 01/08 08:33
14F:→ waiter337: Next wS 01/08 08:34
15F:→ waiter337: wB.Close False 01/08 08:34
16F:→ waiter337: 糟糕 第二 与 第三 行 互换 没注意到 .xlsx要先过滤 01/08 08:35
17F:→ waiter337: 第二行改 wBN = replace(wB.Name,".xlsx","") 01/08 08:35
18F:→ waiter337: 第三行改 wBN = replace(wBN,".xls","") 01/08 08:36