我有几百个文件名中有日期的文件。 我需要从名称中提取日期,并将其放在文件中每一行的每个文件的第一列中。 示例 - 数据请求20140209.csv
我已经从名称中提取了日期并重新排列了它 - 它对于所要求的是倒退的。 我可以添加列并正确设置第一个单元格 - 标题。 如果有超过 2 行(标题加 1),那么它可以正常工作并将日期放在单元格中。
但是,如果只有标题行和一行,则会在第二行中重复标题。 我一定错过了什么。 有什么建议吗?
这是我到目前为止的代码:
'Writing to the file
Dim objXLApp, objXLWb, objXLWs
'Getting modified date
Dim FSO, outFQN
Dim sfile, strFolder, strSub1
Dim sYear, sMonth, sDay, sName, sDate
Dim nPos, nPos2
strFolder = "C:test1"
set objFSO = CreateObject("Scripting.FileSystemObject")
'set objOutput = objFSO.OpenTextFile(strFolder & "" & strOutput, 8, true)
set objShell = CreateObject("WScript.Shell")
set objFolder = objFSO.GetFolder(strFolder)
for each objFile in objFolder.Files
sfile = objFile.Name
'Parse and fix date
nPos = InStr(sfile,"2014")
nPos2 = "8"
'msgbox(nPOS)
sName=MID(sfile, nPos,nPos2)
'msgbox(sName)
sYear=MID(sName, 1, 4)
sMonth=MID(sName, 5, 2)
sDay=MID(sName, 7, 2)
sDate = sMonth & "-" & sDay & "-" & sYear
'sYear = MID(nPO
'msgbox(sDate)
Set objXLApp = CreateObject("Excel.Application")
objXLApp.Visible = False
Set objXLWb = objXLApp.Workbooks.Open("C:test1"& sfile)
' Working with Sheet1
Set objXLWs = objXLWb.Sheets(1)
With objXLWs
'.Columns("A:A").NumberFormat = "123"
.Columns("A:A").Insert xlToRight
.Cells(1, 1).Value = "Date_Modified"
.Cells(2, 1).Value = sDate
set Range = objXLWs.Range("A2:A"&objXLWs.UsedRange.Rows.Count)
Range.FillDown
End With
' Save
StrSub1 = "C:test1sub1"
objXLWb.SaveAs StrSub1 & sfile
objXLWb.Close (False)
' File Formats
'51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)
'52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)
'50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)
'56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)
Set objXLWs = Nothing
Set objXLWb = Nothing
objXLApp.Quit
Set objXLApp = Nothing
next
谢谢!
因此,经过几分钟的沮丧,我想出了这个作为解决方法:
set Range = objXLWs.Range("A2:A"&objXLWs.UsedRange.Rows.Count)
if objXLWs.UsedRange.Rows.Count = "2" then
.Cells(2, 1).Value = sDate
Else
Range.FillDown
End if
End With
添加了一个 if 语句,如果只有 2 行,则忽略填充。 问题解决了!