cristian2015
09-22-2015, 09:29 AM
My name is Cristian, and because I am beginner in VBA, can someone help me please:
I have the next macro in Excel:
In Sheet1:
Sub MyTimeStamp()
Dim DT
DT = Format(Now, "dd/mm/yyyy hh:mm:ss AM/PM")
Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"
ActiveCell.Value = DT
Sheets("Sheet1").Range("B1").Value = Now()
End Sub
In Module1:
Public Count As Integer
Sub MyTimeStamp()
Sheets("Sheet1").Range("B1").Value = Now()
End Sub
Sub writefile()
Dim cell, rng As Range
Dim output_str As String
Dim namefile
Dim indexfile
Call MyTimeStamp
Range("c1") = Count + 1
For Each rng In Range("A1:AV1").Rows
For Each cell In rng.Cells
output_str = output_str & cell.Value & ","
Next cell
output = output_str
Next rng
indexfile = 100 + Count \ 4999
namefile = "D:\report\" & indexfile & ".txt"
Open namefile For Append As #1
Print #1, output
Close #1
Count = Count + 1
Call timer
End Sub
Sub timer()
Application.OnTime Now + TimeValue("00:00:01"), "writefile"
Call MyTimeStamp
End Sub
I want the next macro to generate the first file as (insert date and time when each file is created, and number of lines as bellow descriptions):
100_date_Time.txt - (text file contain between 1 and 4 999 lines)
The following files will be created as:
101_date_time.txt - (text file contain between 5 000 and 9 999 lines)
102_date_time.txt - (text file contain between 10 000 and 14 999 lines)
103_date_time.txt - (text file contain between 15 000 and 19 999 lines)
and so on
Thanks for help
I have the next macro in Excel:
In Sheet1:
Sub MyTimeStamp()
Dim DT
DT = Format(Now, "dd/mm/yyyy hh:mm:ss AM/PM")
Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss AM/PM"
ActiveCell.Value = DT
Sheets("Sheet1").Range("B1").Value = Now()
End Sub
In Module1:
Public Count As Integer
Sub MyTimeStamp()
Sheets("Sheet1").Range("B1").Value = Now()
End Sub
Sub writefile()
Dim cell, rng As Range
Dim output_str As String
Dim namefile
Dim indexfile
Call MyTimeStamp
Range("c1") = Count + 1
For Each rng In Range("A1:AV1").Rows
For Each cell In rng.Cells
output_str = output_str & cell.Value & ","
Next cell
output = output_str
Next rng
indexfile = 100 + Count \ 4999
namefile = "D:\report\" & indexfile & ".txt"
Open namefile For Append As #1
Print #1, output
Close #1
Count = Count + 1
Call timer
End Sub
Sub timer()
Application.OnTime Now + TimeValue("00:00:01"), "writefile"
Call MyTimeStamp
End Sub
I want the next macro to generate the first file as (insert date and time when each file is created, and number of lines as bellow descriptions):
100_date_Time.txt - (text file contain between 1 and 4 999 lines)
The following files will be created as:
101_date_time.txt - (text file contain between 5 000 and 9 999 lines)
102_date_time.txt - (text file contain between 10 000 and 14 999 lines)
103_date_time.txt - (text file contain between 15 000 and 19 999 lines)
and so on
Thanks for help