如何用递增的数字保存excel文件？ [英] How to save excel file with incrementing number?

问题描述

当前代码：

  Dim filepath As String 
 Dim filename As String 
 Dim filepatharch As String 
 Dim filedate As String 
 Dim filelist As String 
 
'抓取FROM列表号
表格（TD文件）。选择
范围（G4）。选择
 filelist = ActiveCell。价值
 
 
'抓住今天的日期
 filedate =格式（现在，MMDD01）--------------目前， 01'来自（见下文）
 
'设置保存位置和文件命名约定
 filepath =\\home\serverfolder\FileDrop\
 tdfilename =TD&归档和文件列表
'& .txt
 
 
'设置保存位置和文件命名约定
 filepatharch =\\home\myfolder\archive
 tdfilename =TD&归档和文件列表
'& .txt
 
 
'保存THXXXXXX.XXX& TDXXXXXX.XXX作为平面文件
'工作簿（MYWORK01）。激活
表格（TDflatfile）。复制
 ActiveWorkbook.SaveAs文件名：= _ 
\\\ \\home\serverfolder\FileDrop\& tdfilename，FileFormat：= xlCSV，_ 
 CreateBackup：= False 
 ActiveWindow.Close 
  

保存的文件名的示例为TD101401.600。 TD + MMDD + 01 + .XXX。我想要+ 01是增加的数字，这样我可以有一个文件是TD101402.600等等。目前，如果该文件存在相同的.XXX号码和日期组合，则会被覆盖。 .XXX不能是增量。

这是可能吗？

解决方案

有人建议，对我来说：

  Dim filecount As Integer 
 
 Do While Len（Dir（filepatharch& thfilename） ）< 0 
 filecount = filecount + 1 
 filedate = Format（现在，MMDD0& filecount&。）
 tdfilename =TD&归档和filelist 
 thfilename =TH&归档和文件列表
循环
  

I am looking for VBA to add to my macro that will increment the file name if the file name already exists.

Current Code:

Dim filepath As String
Dim filename As String
Dim filepatharch As String
Dim filedate As String
Dim filelist As String

'Grab FROM list number
Sheets("TD File").Select
Range("G4").Select
filelist = ActiveCell.Value


'Grab today's date
filedate = Format(Now, "MMDD01.") --------------Currently where the '01' comes from (see below)

'Set where to save and the file naming convention
filepath = "\\home\serverfolder\FileDrop\"
tdfilename = "TD" & filedate & filelist
'& ".txt"


'Set where to save and the file naming convention
filepatharch = "\\home\myfolder\archive"
tdfilename = "TD" & filedate & filelist
'& ".txt"


'Save THXXXXXX.XXX & TDXXXXXX.XXX as flat files
   'Workbooks("MYWORK01").Activate
    Sheets("TDflatfile").Copy
    ActiveWorkbook.SaveAs filename:= _
        "\\home\serverfolder\FileDrop\" & tdfilename, FileFormat:=xlCSV, _
        CreateBackup:=False
    ActiveWindow.Close

An example of the saved file name would be "TD101401.600". TD + MMDD + 01 + .XXX. I would like the "+ 01 " to be the number that increments, that way I could have a file that is "TD101402.600" and so forth. Currently if the file exists for the same .XXX number and date combo, it gets overwritten. The .XXX cannot be the increment.

Is this possible?

解决方案

Someone suggested this and it worked for me:

Dim filecount As Integer

Do While Len(Dir(filepatharch & thfilename)) <> 0
        filecount = filecount + 1
        filedate = Format(Now, "MMDD0" & filecount & ".")
        tdfilename = "TD" & filedate & filelist
        thfilename = "TH" & filedate & filelist
Loop

这篇关于如何用递增的数字保存excel文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！