Prevent excel vba to save a range of cells, containing timestamps to a csv with timestamps converted to numbers

[Baloubeer]

Dear all,

I'm trying to save below sheet to a csv file using vba which succeeds but I run into 2 issues.
1) I need the timestamps to be shown exactly the same.
2) I don't need the commas at the end of the first two and last two lines.
Any help would be highly appreciated.
Thanks in advance.

My VBA code is as follows:

Function DateToText$(dbl#)
DateToText = Format(dbl, "DD/MM/YYYY hh:mm:ss AM/PM")
End Function

Sub CollectDataForCSV()

Dim LastRow As Long
Dim csvarea As String
Dim CurrentTag As String
Dim myCSVFileName As String
Dim tempWB As Workbook
Dim rngToSave As Range

Application.DisplayAlerts = False
On Error GoTo err

'### START OF TESTWM ###

'Determine Last Row of CurrentTag data (modify Column)
With ActiveSheet
LastRow = Worksheets("Data Collection").Cells(.Rows.Count, "W").End(xlUp).Row
End With
'Set CurrentTag (modify tagname)
CurrentTag = "TESTWM"
'Check if there is data for CurrentTag and skip copying the data if not (modify Cell column and nodataname)
If Worksheets("Data Collection").Range("W4").Value = "No more values:" Then
GoTo NoDataTESTWM
End If
'Define range of CurrentTag data (modify columns)
Let csvarea = "W4:W" & LastRow
'Add a new sheet named CurrentTag
Sheets.Add(After:=Sheets("Data Collection")).Name = CurrentTag
'Copy first three standard commands to CSV data
Worksheets("Input").Range("A1:B3").Copy
Worksheets(CurrentTag).Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'Copy data for CurrentTag to sheet named to CurrentTag
Worksheets("Data Collection").Range(csvarea).Copy
Worksheets(CurrentTag).Range("B4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'Define last row after copying the data for CurrentTag
With ActiveSheet
LastRow = Worksheets(CurrentTag).Cells(.Rows.Count, "B").End(xlUp).Row
End With
'Fill cells before timestamps with tagname
Let csvarea = "A4:A" & LastRow
Worksheets(CurrentTag).Range(csvarea) = CurrentTag
'Define start Cell and past last two standard commands to CSV data
Let csvarea = "A" & LastRow + 1
Worksheets("Input").Range("A4:A5").Copy
Worksheets(CurrentTag).Range(csvarea).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'Auto size the first to columns for CSV data
Worksheets(CurrentTag).Columns("A:B").AutoFit
Application.CutCopyMode = False
'Define last row after copying the data for CurrentTag
With ActiveSheet
LastRow = Worksheets(CurrentTag).Cells(.Rows.Count, "A").End(xlUp).Row
End With
'Save data for CurrentTag to CSV file
Let csvarea = "A1:B" & LastRow
myCSVFileName = ActiveWorkbook.Path & "\" & CurrentTag & ".csv"

Set rngToSave = Worksheets(CurrentTag).Range(csvarea)
rngToSave.Copy

Set tempWB = Application.Workbooks.Add(1)
With tempWB
.Sheets(1).Range("A1").PasteSpecial xlPasteValues
.SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close
End With
'Delete Sheet CurrentTag
'Sheets(CurrentTag).Delete
'Nodata line (modify NoDateName)
NoDataTESTWM:

'### END OF TESTWM ###

err:
Application.DisplayAlerts = True
End Sub

 

[Baloubeer]

In the meanwhile I changed ".Sheets(1).Range("A1").PasteSpecial xlPasteValues" into ".Sheets(1).Range("A1").PasteSpecial xlPasteValuesAndNumberFormats" which resulted in the outcome shown below.
So questions are changed to:
1) I need the timestamps to be shown exactly the same (dashes instead of slashes).
2) I don't need the commas at the end of the first two and last two lines.
Any help would be highly appreciated.
Thanks in advance.

 

[Baloubeer]

 

[Marc L]

As a reminder a correct csv file should have a comma even if the next field is empty so you just need some text file rather than a true csv ?​

​

Use this forum tool XL2BB to attach your sample data …​

 

[Baloubeer]

​

As a reminder a correct csv file should have a comma even if the next field is empty so you just need some text file rather than a true csv ?​

​

Use this forum tool XL2BB to attach your sample data …​

Hi Marc L,

I'm afraid this tool doesn't work because of the VBA scripting and/or add in "SMT" for OSISOFT PI.
When I use the addin tool XL2BB, it does the copy but the paste still gives a huge amount of code.
Perhaps share the file or some more screenshots?
Thanks in advance.

 

[Fluff]

When I use the addin tool XL2BB, it does the copy but the paste still gives a huge amount of code.

That is what it should do, but when you hit the reply button it will be converted to a mini sheet.