How do I save a CSV file (Comma Separated Value) with VBA in Excel 2000?

[motilulla]

Hello,

Below is my file looks like this

AUDCAD D1 (Export generated by OpenClose Data )
DATE,HOUR,CLOSE-OPEN ,HIGH-LOW,HIGH-OPEN,OPEN-LOW
25 May 2016,00:00,6.70,20.50,18.00,2.50
24 May 2016,00:00,-62.50,100.00,13.00,87.00
23 May 2016,00:00,29.30,55.30,49.20,6.10
20 May 2016,00:00,-0.30,46.00,37.50,8.50
19 May 2016,00:00,41.50,74.10,52.10,22.00
18 May 2016,00:00,-34.50,71.40,4.80,66.60
17 May 2016,00:00,56.50,95.20,90.70,4.50
16 May 2016,00:00,16.50,63.30,57.80,5.50
13 May 2016,00:00,-7.00,56.50,8.50,48.00
12 May 2016,00:00,-68.50,106.30,6.30,100.00
11 May 2016,00:00,-21.80,68.90,39.30,29.60
10 May 2016,00:00,17.50,77.10,62.10,15.00

I want after VBA it saves in excel and look as follow

Book1
	A	B	C	D	E	F
1	AUDCAD D1 (Export generated by OpenClose Data )
2	DATE	HOUR	CLOSE-OPEN	HIGH-LOW	HIGH-OPEN	OPEN-LOW
3	25-may-16	0:00	6,7	20,5	18	2,5
4	24-may-16	0:00	-62,5	100	13	87
5	23-may-16	0:00	29,3	55,3	49,2	6,1
6	20-may-16	0:00	-0,3	46	37,5	8,5
7	19-may-16	0:00	41,5	74,1	52,1	22
8	18-may-16	0:00	-34,5	71,4	4,8	66,6
9	17-may-16	0:00	56,5	95,2	90,7	4,5
10	16-may-16	0:00	16,5	63,3	57,8	5,5
11	13-may-16	0:00	-7	56,5	8,5	48
12	12-may-16	0:00	-68,5	106,3	6,3	100
13	11-may-16	0:00	-21,8	68,9	39,3	29,6
14	10-may-16	0:00	17,5	77,1	62,1	15
OpenClose_AUDCAD D1

Thanks In Advance

Regards,
Moti

 

[motilulla]

Hello,

When I open “OpenClose_AUDCAD D1.CSV” file data look like as shown below

AUDCAD D1 (Export generated by OpenClose Data )
DATE,HOUR,CLOSE-OPEN ,HIGH-LOW,HIGH-OPEN,OPEN-LOW
25 May 2016,00:00,6.70,20.50,18.00,2.50
24 May 2016,00:00,-62.50,100.00,13.00,87.00
23 May 2016,00:00,29.30,55.30,49.20,6.10
20 May 2016,00:00,-0.30,46.00,37.50,8.50
19 May 2016,00:00,41.50,74.10,52.10,22.00
18 May 2016,00:00,-34.50,71.40,4.80,66.60
17 May 2016,00:00,56.50,95.20,90.70,4.50
16 May 2016,00:00,16.50,63.30,57.80,5.50
13 May 2016,00:00,-7.00,56.50,8.50,48.00
12 May 2016,00:00,-68.50,106.30,6.30,100.00
11 May 2016,00:00,-21.80,68.90,39.30,29.60
10 May 2016,00:00,17.50,77.10,62.10,15.00

I need to import above file in to workbook excel and save as excel file

Book1
	A	B	C	D	E	F
1	AUDCAD D1 (Export generated by OpenClose Data )
2	DATE	HOUR	CLOSE-OPEN	HIGH-LOW	HIGH-OPEN	OPEN-LOW
3	25-may-16	0:00	6,7	20,5	18	2,5
4	24-may-16	0:00	-62,5	100	13	87
5	23-may-16	0:00	29,3	55,3	49,2	6,1
6	20-may-16	0:00	-0,3	46	37,5	8,5
7	19-may-16	0:00	41,5	74,1	52,1	22
8	18-may-16	0:00	-34,5	71,4	4,8	66,6
9	17-may-16	0:00	56,5	95,2	90,7	4,5
10	16-may-16	0:00	16,5	63,3	57,8	5,5
11	13-may-16	0:00	-7	56,5	8,5	48
12	12-may-16	0:00	-68,5	106,3	6,3	100
13	11-may-16	0:00	-21,8	68,9	39,3	29,6
14	10-may-16	0:00	17,5	77,1	62,1	15
OpenClose_AUDCAD D1

Please help with some VBA to get this done

Regards,
Moti

 

[xenou]

Hi,
That's exactly how it looks if you open in excel so really it's good already. For information on saving files with vba check out the vba SaveAs function:
Use VBA SaveAs in Excel 2007-2016

 

[motilulla]

Hi,
That's exactly how it looks if you open in excel so really it's good already. For information on saving files with vba check out the vba SaveAs function:
Use VBA SaveAs in Excel 2007-2016

xenou , just now I checked file is CSV but it is checked to open with programme excel, it open exactly how it looks
In #post 1 & 2

Yes your suggestion did the trick once file is opened I tried to save as Microsoft excel 2000 it works perfect file saved with *.xls

Thank you for your help

Now I need one more solution I have total 21 total files in one folder is located at desktop under name "Data"
and every day once old files are replaced with new data once so is there any code if I run it, that open all 21 files and save them as Microsoft excel instead it doing manually one by one?

Regards,
Moti

 

[xenou]

Hi, I'd suggest opening a new thread for the last since I won't be able to help you with that in a timely manner. If you want to try it, the short answer is to use some functions for handling files to open workbooks and then save them using saveas. You may need to add some code to do something with the old files. This would all involve a loop over all the files in the folder. And you can probably run it from a "master workbook" in another folder (although it could also be written as a VBScript script).
ξ

 

[motilulla]

Hi, I'd suggest opening a new thread for the last since I won't be able to help you with that in a timely manner. If you want to try it, the short answer is to use some functions for handling files to open workbooks and then save them using saveas. You may need to add some code to do something with the old files. This would all involve a loop over all the files in the folder. And you can probably run it from a "master workbook" in another folder (although it could also be written as a VBScript script).ξ

Thank you xenou, i will open a new thread

Regards,
Moti