Problem with Print with timestamp and time format VBA excel

julien1h 0
Viewed 24

for my school project I want to do a macro with excel vba which will generate a .sql script.

But I have issues with the Print function.

1st : i have cells with time variables, it's formating as time cells hh:mm:ss but for exemple when I print this 02:28:0 I have this in result : 0,102777777777778

2nd: i have cells with timestamp variables, it's formating as custom cells with yyyy-mm-dd hh:mm:ss for exemple when I print this 2020-04-22 03:20:22 I have this in result : 27/08/2021 06:12:02. That means vba changes '-' for '/' and I don't want that.

Here is my code :` Sheets("subscription").Activate

    nbre_colonnes = ActiveSheet.UsedRange.Columns.Count
    nbre_lignes = ActiveSheet.UsedRange.Rows.Count

    For i = 5 To nbre_lignes
        Print #1, "INSERT INTO subscription"
        Print #1, "VALUES( ";
        For j = 1 To nbre_colonnes
            If j = nbre_colonnes Then
                Print #1, Cells(i, j).Value;
            Else
                Print #1, Cells(i, j).Value & ",";
            End If
        Next j
        Print #1, ");"
    Next i`
vba excel
1 Answers

Your problem will be resolved if you use the .Text method of Range rather than the .Value. You should also research how to format cells so that .Text gives you the format you want.

freeflow0
Related