How to save Excel file to csv with blank cells with formula with out getting commas.

[xizor]

Hello every one!

I am working on a excel file that has a sheet that i want to convert to .csv. The sheet has formula's for the first three column that are warped an if statement that returns "" (blank) if certain conditions are not meet. However when i got to save the file as a csv i get a file with ",,,,,,," where there is no data.
I would like to save csv and have only commas where this data.
Example
When i convert a sheet like
Firstname Lastname Password
Tim Cats Bo3n9x

it saves as
Firstname Lastname Password
Tim Cats Bo3n9x
,,,
,,,
,,,

In the .csv file.

Files i am current working on are attached.
Any help would be much appreciated!

If there is any other info you would need let me know.

[TMS]

I suggest you copy the sheet. On the copy of the sheet, select all the cells and copy and paste special | values ... or simply delete the "blank" rows. Then save the copied sheet as a .csv file. Delete the copy sheet if it is no longer required.

If you do it often, record the actions as a macro.

Regards, TMS

[xizor]

I suggest you copy the sheet. On the copy of the sheet, select all the cells and copy and paste special | values ... or simply delete the "blank" rows. Then save the copied sheet as a .csv file. Delete the copy sheet if it is no longer required.

If you do it often, record the actions as a macro.

Regards, TMS

Thank you for your reply. I am currently trying to do you have suggested, copying and pasting special |values to a blank sheet then opening the .csv with not pad and it is still showing with ",,,," inside the .csv file down the file.
I have double checked the cells to make sure there are no formula's in them. I have tried to special past the values in a completely new work book but i still get the same results?

I much appreciate the assistance!

Regards,
Xizor

[xizor]

Thank you for your reply. I am currently trying to do you have suggested, copying and pasting special |values to a blank sheet then opening the .csv with not pad and it is still showing with ",,,," inside the .csv file down the file.
I have double checked the cells to make sure there are no formula's in them. I have tried to special past the values in a completely new work book but i still get the same results?

I much appreciate the assistance!

Regards,
Xizor

Any other suggestion from anyone?

[TMS]

OK, have a look at the attached workbook. It takes a lightly different approach. It uses a Worksheet Change event to monitor input on the Request Guest worksheet and, when all four columns/cells are completed, it generates the formulae on the Guest Wifi Template worksheet. It then converts the fomulae to values and save the Guest Wifi Template worksheet as a .csv file.

So, every time you add or change a row on the Request Guest worksheet, it will generate a complete new list. Note that it only re-generates codes if you make changes to a row. It doesn't have cells with "empty" formulae so it doesn't generate rows with just commas.

Regards, TMS

[xizor]

OK, have a look at the attached workbook. It takes a lightly different approach. It uses a Worksheet Change event to monitor input on the Request Guest worksheet and, when all four columns/cells are completed, it generates the formulae on the Guest Wifi Template worksheet. It then converts the fomulae to values and save the Guest Wifi Template worksheet as a .csv file.

So, every time you add or change a row on the Request Guest worksheet, it will generate a complete new list. Note that it only re-generates codes if you make changes to a row. It doesn't have cells with "empty" formulae so it doesn't generate rows with just commas.

Regards, TMS

This is amazing! Thanks. I am not to well versed in VBA but i am going to study what you have provided so that i know exactly how it works.
This is very much appreciated thanks!

[TMS]

You're welcome. Thanks for the rep.


I guess there might have been another way, but I tried a few options with your data and, despite copying and pasting values, etc., it still had all those rows of commas. Hopefully, it's something to work with and build on.

Regards, TMS




If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[xizor]

quick question to before we wrap this up.
How can i make edits to columns headers first name last name company with out breaking the code?

[xizor]

Also it seems that the Worksheet Change event has stopped working. When I go to add or delete guest it does not update template page. I have been searching for why that is, and one lead I have found is that Application.EnableEvents maybe disabled. But i am not sure how to turn it back on for this even?

[TMS]

Application.EnableEvents = True

Regards, TMS

[xizor]

Application.EnableEvents = True

Regards, TMS

Thanks for all your help! This has been a great help and an awesome learning experience. If I can bug you a little bit more, I am putting finishing touches on the work book and running into a few errors and wanted to see if you have any suggestion on where I am going wrong at?

In column C row 1 on Guest wifi template page the date is not populating. (Screen shot attached.) missing date in row c.PNG

The code no longer updates for the Guest Wifi Template page when I add or delete users in each cell on the Request Guest sheet.

I have trying adding the Application.EnableEvents = Ture to the Immediate window and to vba code but this seems to not have resolved the issue.

Also i wanted to clean up the headers section of Request guest sheet, but when i change the names of the headers i get an Run time error '1004' Application defined or Object defined error. (screen shot attached.)excel error.PNG
excel vba error.PNG

Let me know if there is anything else I can provide. Again I thank you for all your help!

Regards,
Xizor

Guest Wifi Access Generator TMS.xlsm

[TMS]

The date in column C is dependent on the formula that you use. I just drop that formula in and convert the result to a value. If you want to see what formula is going in there, comment out the ".Value = .Value" line of code. You can then evaluate the formula.

That said, I just selected cell A2 and pressed F2 (to go into Edit mode) and then Enter (to recommit the value [Manchester]) and it generated the date value.

The code doesn't actually disable Event handling because it doesn't make any changes on the sheet where the event is being trapped. I note that you have spelt "True" as "Ture" both in your post and in the code:

'Closes the file
    .Close Ture

I guess the reason that you are having problems changing the headings is that the Change Event handler is monitoring the entire columns A to D. Open the workbook without enabling macros, make the changes to your headings and then save the workbook. Open it again, this time enabling macros.

Note: what is happening is that the code uses a variable, tr, for the Target Row. Because the csv doesn't have headers, the row is adjusted using the variable trm1 ... Target Row Minus 1. So, if you try to change row 1 on the Template sheet, the code is trying to write to row 0 which doesn't exist.


Regards, TMS

[xizor]

First things first i correct the typos in the code.

The date in column C is dependent on the formula that you use. I just drop that formula in and convert the result to a value. If you want to see what formula is going in there, comment out the ".Value = .Value" line of code. You can then evaluate the formula.

This bit was key for me as i understand what is happening now after commenting out this line. The function:
=If('Request Guest!D2="","",MAX(0,'Request Guest'!D3-Brain!B$2)*86400) is not being iterated. !D3 inside MAX(0,'Request Guest'!D3-Brain!B$2)*86400) is not changing to !D4, D5, D6 etc.. Do i need to make another formula for the change event?

I guess the reason that you are having problems changing the headings is that the Change Event handler is monitoring the entire columns A to D. Open the workbook without enabling macros, make the changes to your headings and then save the workbook. Open it again, this time enabling macros.

Note: what is happening is that the code uses a variable, tr, for the Target Row. Because the csv doesn't have headers, the row is adjusted using the variable trm1 ... Target Row Minus 1. So, if you try to change row 1 on the Template sheet, the code is trying to write to row 0 which doesn't exist.

This allowed me to fix the headings and I understand the error now.

[xizor]

The date in column C is dependent on the formula that you use. I just drop that formula in and convert the result to a value. If you want to see what formula is going in there, comment out the ".Value = .Value" line of code. You can then evaluate the formula.

That said, I just selected cell A2 and pressed F2 (to go into Edit mode) and then Enter (to recommit the value [Manchester]) and it generated the date value.

The code doesn't actually disable Event handling because it doesn't make any changes on the sheet where the event is being trapped. I note that you have spelt "True" as "Ture" both in your post and in the code:

'Closes the file
    .Close Ture

I guess the reason that you are having problems changing the headings is that the Change Event handler is monitoring the entire columns A to D. Open the workbook without enabling macros, make the changes to your headings and then save the workbook. Open it again, this time enabling macros.

Note: what is happening is that the code uses a variable, tr, for the Target Row. Because the csv doesn't have headers, the row is adjusted using the variable trm1 ... Target Row Minus 1. So, if you try to change row 1 on the Template sheet, the code is trying to write to row 0 which doesn't exist.


Regards, TMS

So I have been playing around with the code for

"=If('Request Guest!D2="","",MAX(0,'Request Guest'!D3-Brain!B$2)*86400)"

to try to get

MAX(0,'Request Guest'!D3-Brain!B$2)*86400)

to change to

MAX(0,'Request Guest'!D4-Brain!B$2)*86400)

but i can not seem to get this part

"=IF('Request Guest'!D" & tr & "="""","""",(('Request Guest'!D3-Brain!B$2))*86400)"

to change iterate to the next cell.

I have tried other functions as well, but none iterate!D3.

I was thinking that maybe the function only iterates the first part "=IF('Request Guest'!D" & tr & "="""","""", and that maybe I need to use code for MAX(0,'Request Guest'!D4-Brain!B$2)*86400) to make it go to the next number? But I am sure where to begin?