Macro to Save Excel sheet as comma delimited txt file

Hi,

i am new to macros. can anyone help me in providing a macro to save an excel sheet to comma delimited txt file. Also, My sheet has 1st row as table columns and i dont want to export them in my txt file.

Thanks,
-Dileep

To not include the first row you could always just delete it before saving it.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thank you jessebranum777, this code was helpful. i have another requirement:
now i want to export to csv by excluding 1st 4 columns. i should not delete these because i am using them as references in other columns.
i tried to hide them and then export but the result csv file still have these columns.

can you help me on this.
thanks,
-Dileep

CSV doesn't have hide features to the best of my knowledge so you are correct that won't work. Are you saying you need the first four columns on the CSV file too? If so I don't understand why you don't want to export them. If not then why not delete them from the export if they are not needed? I guess if you could upload a excel sheet with what you have and what you expect I could understand better.

Hi,

i have attached my Excel workbook.

In this workbook, I have two sheets "Test", "Output Data". Test sheet has my original data and i am using formulas to pull required data ans saving in "Output Data".

Now i have to export the "Output Data" sheet to csv file but i dont want Columns(A:E) to be exported
The reason why i am not deleting those columns is if you look at the sheet "Output Data".K2 cell, i am using formulas referencing those "A:E" columns.

Also, i dont want to export my header row.

so my o/p csv file should be like

Mgr1,1234,4,15,14,24,1,10046,1,0,,0,,,,,,,,,,,,,,,,,
mgr2,5678,4,15,14,24,1,8403,1,0,,0,,,,,,,,,,,,,,,,,
Mgr3,91011,4,15,14,24,1,12024,1,0,,0,,,,,,,,,,,,,,,,,


Appreciate your prompt responses.

thanks,
Dileep

Thanks for the code; I am calling this code in Access Button click event and it works fine for the first run. Now when i run this without closing the application i am getting an error message "Method 'Columns' of object '_Global' failed" at the line LastCol = Wks.Cells(Rng.Row, Columns.Count).end(xlToLeft).Column. can you explain why am i getting this error?

thanks for all the help.

but Ross, it works fine for the 1st run and we are not deleting any data from the sheet. So, data should be there in Row1 for the 2nd run right? Am i missing something here?

No, i did not do any changes to it. actually in my button click event, i am opening the file similar to what i have shared. it has one i/p sheet ("Test") and multiple o/p sheets "Output1" "Output2" etc... i am looping through all those sheets and used your code to generate several csv files with Output1, Output2 etc.. as names.

this process works good in the 1st run but fails if i run for second run.
Below is the button click event:

Please Login or Register  to view this content.

No Ross, still facing the same error

No, its only one person who would do this and ideally it would be run only once for each file. I am testing this for multiple runs to ensure if the user clicks it twice, it doesnot throw any error.

Ok Ross, is there a way where we can check if that sheet is open and is empty so that we can handle it rather than throwing an error message?

Hey Ross,I have modified the code to get last column as LastCol = Wks.Cells(Rng.Row, Sheet.Columns.Count).End(xlToLeft).Column
now the error was fixed but i am getting same error at Set Row = Intersect(Row, Row.Offset(0, 5)) inside the for loop

Hurray, seems like everything looks good now. i tried several runs and it is working fine... Thanks Ross for all the help