VBA Excel Help

[twlim]

I'm trying to insert a creation date into the following filename:

eg. I hope to create the file as C:\Converted_txt\Customers_10_09_03.txt

Always save as current date.



Please help me to using minimal code changes. Thanks.





----------------------------------------------------------------

Private Sub Convert_Excel_2_txt()



On Error Resume Next



Dim fso, m_objExcel

Dim f, fileOut

Dim fDiag

Dim r, c, v





Const ForReading = 1, ForWriting = 2



Set CellLast = Selection.SpecialCells(xlCellTypeLastCell)

RowLast = CellLast.Row



'create variables

Set fso = CreateObject("Scripting.FileSystemObject")

fileOut = "C:\Converted_txt\Customers.txt"

Set m_objExcel = CreateObject("Excel.Application")

fDiag = m_objExcel.GetOpenFilename("Excel files (*.xls), *.xls")

m_objExcel.Workbooks.Open fDiag

Set f = fso.createtextfile(fileOut, ForWriting)



'loop on row

For r = 1 To RowLast

'loop on column

For c = 1 To 2



'activate worksheet - change all occurrences of "Sheet1" to
name of your sheet

m_objExcel.Worksheets("Sheet1").Activate



'get cell value at row, column (r,c)

If c = 1 Then

v = v & m_objExcel.Worksheets("Sheet1").Cells(r, c) & "^"
& "Your cases are "

Else

v = v & m_objExcel.Worksheets("Sheet1").Cells(r, c) & "
declined "

End If

Next

f.writeline v

v = ""

Next



'kill variables

f.Close

m_objExcel.Quit

Set m_objExcel = Nothing

Set fso = Nothing



MsgBox "The Excel has been converted to txt format, at
C:\Converted_txt\Customers.txt"



End Sub

 

[Jason]

Replace your fileOut statement with the following:

fileOut = "C:\Converted_txt\Customers" & Format(Now(), "mm_dd_yy") & ".txt"

Jason Goto
AnalysisWorks Inc
Evidence-Based Management Consulting
www.analysisworks.net