Export with dates format to CSV file

[TG58]

Hi Guys,
I have been looking through the forums and I have found some code that I have been able to modify to “almost” perform the task I am trying to complete.

My stumbling block is in a date format.

The code I am using is exporting the contents of a single sheet and writing it as a CSV file.
The user would write the date in the DD/MM/YYYY format, this is then sent (with other data) to another sheet in the same workbook, to 2 other locations, G1 in DDMMYYYY format and H1 in MMYYY format (both without the (/) forward slash in the cells but with the / in the formula bar.

When the data is sent to the CSV file the dates appear as #DD/MM/YYYY# and #MM/YYYY#.

How can I get VBA to send these dates as DDMMYYY & MMYYYY without the / to the CSV file?

Thanks
Tina

[fshbet]

Hi TG58,

Use the code

Please Login or Register  to view this content.

[fshbet]

use this in VBA

Please Login or Register  to view this content.

[joeu2004]

The code I am using is exporting the contents of a single sheet and writing it as a CSV file. The user would write the date in the DD/MM/YYYY format, this is then sent (with other data) to another sheet in the same workbook, to 2 other locations, G1 in DDMMYYYY format and H1 in MMYYY format (both without the (/) forward slash in the cells but with the / in the formula bar. When the data is sent to the CSV file the dates appear as #DD/MM/YYYY# and #MM/YYYY#. How can I get VBA to send these dates as DDMMYYY & MMYYYY without the / to the CSV file?

I suggest that you show us the relevant code snippets (include declarations). Even better: attach an example Excel file.

Phrases like "sent to a worksheet" are inaccurate and unclear. Formulas reference cells in other worksheets. VBA code assigns values to cells in worksheets. Sometimes, cells are copy and pasted or pasted-value into other cells.

Even phrases like "exporting worksheet" and "writing as a CSV file" are inaccurate and unclear. Are you doing SaveAs in VBA code? Or are you doing Print# to a CSV file?

Dates in the form #dd/mm/yyyy# are how VBA represents date constants. It is unclear what VBA code you wrote that present dates of that form.

Aside.... I have encountered situations where VBA does represent variable date values as date constants in some contexts. I find it infuriating. That is why I avoid type Date and Variants that contain type Date.

If you are doing SaveAs to a CSV file, note that cell values are written to the file exactly as they appear in the worksheet. So the problem might not be with the form of the date in the file (open with Notepad to be sure), but with the way you are transferring or viewing the contents of the CSV file in Excel or VBA; and there might be nothing wrong with the contents of the CSV file at all. Again, your description is unclear.

If you are writing to a CSV file with VBA code, the solution might be as simple as referencing Range("G1").Text instead of Range("G1").Value (or without a qualifier).

Sorry for any misdirection.

[TG58]

Thank you both for your answers.

I have changed a .Value to a .Text and this has solved my problem.

The code below is how I got my solution.

Please Login or Register  to view this content.

As I mentioned before I have modified this code that I found with Google search.

How could I add to this so that, if the file exists, I could append another lot of data to it, but, without adding the first (header) line from the source data.

Source data is as the attached. Intrastat.xlsx

Thanks
Tina

[joeu2004]

How could I add to this so that, if the file exists, I could append another lot of data to it, but, without adding the first (header) line from the source data.

Some of the details of your code are unclear to me. But perhaps you can adapt the following paradigm (untested!).

Please Login or Register  to view this content.