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

Originally Posted by 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

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

Originally Posted by 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?

Originally Posted by 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

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!

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?

Originally Posted by TMS

Please Login or Register  to view this content.

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

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.

Originally Posted by 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:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

to try to get

Formula:

Please Login or Register  to view this content.

to change to

Formula:

Please Login or Register  to view this content.

but i can not seem to get this part

Formula:

Please Login or Register  to view this content.

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?