Change format when exporting to CSV

I have this macro to export Excel to CSV:

Sub To_CSV()

Dim tmpWS As Worksheet

Application.DisplayAlerts = False



For Each WS In ThisWorkbook.Worksheets



If WS.Range("B1").Value <> "" Then

     If WS.Range("c1").Value <> "" Then

         filePath = Environ("USERPROFILE") & "Desktop" & "Pozo de Bombeo " & WS.Range("B1").Value & ".csv"

     Else

         filePath = Environ("USERPROFILE") & "Desktop" & "Pozo de Observacion " & WS.Range("B1").Value & ".csv"

     End If

     WS.Copy

     Set tmpWS = ActiveSheet

     tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV

     tmpWS.Parent.Close False



End If

Next





Application.DisplayAlerts = True

End Sub

But when I open the the CSV "POZO DE BOMBEO" the format of some dates changed, apparently at random.

edited Jan 7 at 8:04

Pᴇʜ

24.9k63052

asked Jan 2 at 20:09

Diego

HI Diego, dates are a real issue in CSV format. Could you provide what the format was before export, what you expect it to be, and how it appeared as examples? This will help us understand what is going on.

– Karlomanio
Jan 2 at 20:15

add a comment |

I have this macro to export Excel to CSV:

Sub To_CSV()

Dim tmpWS As Worksheet

Application.DisplayAlerts = False



For Each WS In ThisWorkbook.Worksheets



If WS.Range("B1").Value <> "" Then

     If WS.Range("c1").Value <> "" Then

         filePath = Environ("USERPROFILE") & "Desktop" & "Pozo de Bombeo " & WS.Range("B1").Value & ".csv"

     Else

         filePath = Environ("USERPROFILE") & "Desktop" & "Pozo de Observacion " & WS.Range("B1").Value & ".csv"

     End If

     WS.Copy

     Set tmpWS = ActiveSheet

     tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV

     tmpWS.Parent.Close False



End If

Next





Application.DisplayAlerts = True

End Sub

But when I open the the CSV "POZO DE BOMBEO" the format of some dates changed, apparently at random.

edited Jan 7 at 8:04

Pᴇʜ

24.9k63052

asked Jan 2 at 20:09

Diego

HI Diego, dates are a real issue in CSV format. Could you provide what the format was before export, what you expect it to be, and how it appeared as examples? This will help us understand what is going on.

– Karlomanio
Jan 2 at 20:15

add a comment |

I have this macro to export Excel to CSV:

Sub To_CSV()

Dim tmpWS As Worksheet

Application.DisplayAlerts = False



For Each WS In ThisWorkbook.Worksheets



If WS.Range("B1").Value <> "" Then

     If WS.Range("c1").Value <> "" Then

         filePath = Environ("USERPROFILE") & "Desktop" & "Pozo de Bombeo " & WS.Range("B1").Value & ".csv"

     Else

         filePath = Environ("USERPROFILE") & "Desktop" & "Pozo de Observacion " & WS.Range("B1").Value & ".csv"

     End If

     WS.Copy

     Set tmpWS = ActiveSheet

     tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV

     tmpWS.Parent.Close False



End If

Next





Application.DisplayAlerts = True

End Sub

But when I open the the CSV "POZO DE BOMBEO" the format of some dates changed, apparently at random.

edited Jan 7 at 8:04

Pᴇʜ

24.9k63052

asked Jan 2 at 20:09

Diego

I have this macro to export Excel to CSV:

Sub To_CSV()

Dim tmpWS As Worksheet

Application.DisplayAlerts = False



For Each WS In ThisWorkbook.Worksheets



If WS.Range("B1").Value <> "" Then

     If WS.Range("c1").Value <> "" Then

         filePath = Environ("USERPROFILE") & "Desktop" & "Pozo de Bombeo " & WS.Range("B1").Value & ".csv"

     Else

         filePath = Environ("USERPROFILE") & "Desktop" & "Pozo de Observacion " & WS.Range("B1").Value & ".csv"

     End If

     WS.Copy

     Set tmpWS = ActiveSheet

     tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV

     tmpWS.Parent.Close False



End If

Next





Application.DisplayAlerts = True

End Sub

But when I open the the CSV "POZO DE BOMBEO" the format of some dates changed, apparently at random.

excel vba

edited Jan 7 at 8:04

Pᴇʜ

24.9k63052

asked Jan 2 at 20:09

Diego

edited Jan 7 at 8:04

Pᴇʜ

24.9k63052

asked Jan 2 at 20:09

Diego

edited Jan 7 at 8:04

Pᴇʜ

24.9k63052

edited Jan 7 at 8:04

Pᴇʜ

24.9k63052

edited Jan 7 at 8:04

Pᴇʜ

24.9k63052

asked Jan 2 at 20:09

Diego

asked Jan 2 at 20:09

Diego

asked Jan 2 at 20:09

Diego

HI Diego, dates are a real issue in CSV format. Could you provide what the format was before export, what you expect it to be, and how it appeared as examples? This will help us understand what is going on.

– Karlomanio
Jan 2 at 20:15

add a comment |

HI Diego, dates are a real issue in CSV format. Could you provide what the format was before export, what you expect it to be, and how it appeared as examples? This will help us understand what is going on.

– Karlomanio
Jan 2 at 20:15

HI Diego, dates are a real issue in CSV format. Could you provide what the format was before export, what you expect it to be, and how it appeared as examples? This will help us understand what is going on.

– Karlomanio
Jan 2 at 20:15

add a comment |

1 Answer
1

active

oldest

votes

This is an example of my output:

11/27/2018 11:19
11/27/2018 11:20
11/27/2018 11:25
11/27/2018 11:30
11/27/2018 11:35
11/27/2018 11:40
11/27/2018 11:45
11/27/2018 13:00
11/27/2018 16:00
11/27/2018 17:15
27-11-2018 22:10
27-11-2018 23:40
28-11-2018 1:10
28-11-2018 2:30
28-11-2018 3:40
28-11-2018 4:10
28-11-2018 5:10

The original input was DD-MM-YYYY HH:MM but as you can see, for some reason, it changed. Luckily, I was able to reach the solution:

tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV, local:=True

Just added: "local:= True" in the Workbook.SaveAs method.

answered Jan 4 at 10:34

Diego

add a comment |

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54012536%2fchange-format-when-exporting-to-csv%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

This is an example of my output:

The original input was DD-MM-YYYY HH:MM but as you can see, for some reason, it changed. Luckily, I was able to reach the solution:

tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV, local:=True

Just added: "local:= True" in the Workbook.SaveAs method.

answered Jan 4 at 10:34

Diego

add a comment |

This is an example of my output:

The original input was DD-MM-YYYY HH:MM but as you can see, for some reason, it changed. Luckily, I was able to reach the solution:

tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV, local:=True

Just added: "local:= True" in the Workbook.SaveAs method.

answered Jan 4 at 10:34

Diego

add a comment |

This is an example of my output:

The original input was DD-MM-YYYY HH:MM but as you can see, for some reason, it changed. Luckily, I was able to reach the solution:

tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV, local:=True

Just added: "local:= True" in the Workbook.SaveAs method.

answered Jan 4 at 10:34

Diego

This is an example of my output:

The original input was DD-MM-YYYY HH:MM but as you can see, for some reason, it changed. Luckily, I was able to reach the solution:

tmpWS.SaveAs Filename:=filePath, FileFormat:=xlCSV, local:=True

Just added: "local:= True" in the Workbook.SaveAs method.

answered Jan 4 at 10:34

Diego

answered Jan 4 at 10:34

Diego

answered Jan 4 at 10:34

Diego

answered Jan 4 at 10:34

Diego

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu