Trouble with Paste Special values ony - blank cells are being ignored.

[esbelfer]

Hi

First post, so here goes:


I have a spreadsheet with 20 columns, and need to copy these rows of data (ie without the header rows) into a new sheet, and then save as a csv file.


Several columns have formulas, and the last 3 columns often will not contain any data in them at all.


Regardless of whether or not there is data in the cells, I need all 20 columns (fields) to be exported to the csv file, to be used for importing to a database - so I need to have all 20 fields.


I have worked out the VB code to select and copy the correct range, paste it to a new worksheet, and then save the new worksheet as a csv file.


But here is my problem:

If I use "Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False", then it will copy accross all 20 fields correctly (including the blank ones at the end), as it copies 'ALL', althogh it does copy all 20 fields, it will copy accross the FORUMLAS, and not the data which is what I want.


So then I tried "Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False", thinking that if I used Paste:=xlPasteValues it should work.

Well, now it does copy the 'data' and not the formulas, but I am only getting accross the 17 columns worth, (as the last three are blank). :-(


I have checked that the option 'SkipBlanks' is set to false, and I also tried removing this altogether.


Any ideas or help?


I am running Office 2003.


Thanks in advance...

Eli

 

[NARAYANK991]

Hi Eli ,


Correct me if I am wrong.


There are two steps to your process :


1. Copy a range from one worksheet to another , where you can use the SkipBlanks option to either copy or not copy blank cells


2. Save a worksheet as a .csv file to save only the data.


The second step is in no way dependent on the first.


When a worksheet is saved as a .csv file , blanks cannot be saved since you are saving a worksheet and not a range. When a worksheet is saved , it is inevitable that there will be a lot of blank cells , and saving it cannot save all the blank cells.


I think you have to have at least spaces in those blank cells , for them to be saved to the .csv file.


Narayan

 

[asa]

Hi Eli,


Also, formulas, as long as they still result in the values you want, pose no problem.


When you save that worksheet as a CSV file, all formulas are converted to values during save.


Excel does not save formulas to CSV files.


Asa

 

[asa]

As to your PasteSpecial problem, that's interesting behavior you're seeing. It works fine for me. I tested two blank columns on the right side of my copy selection, and used the line of code you indicated, and the paste definitely did copy the blank cells, as it overwrote other data I had in the paste range.


Like Narayank991 said, though, I don't think Excel's CSV save will include commas for blank columns on the right side of your table.

An alternative to Narayank's suggestion of having a space in those cells, would probably be the formula:

="" which is a blank text string. Excel will not consider the cell blank for most purposes, but will consider it a used area of your worksheet.


Aas

 

[esbelfer]

Thank you both for your replies.


Please see below for my individual replies to your feedback.


Anyways, what I did:


I did my copy and paste twice:

Once using the PasteSpecial=Values to get the correct info, and export only the data I wanted.

Then a second Copy, this time of only the last 3 columns (as the fourth last column will always have data), and I did a PasteSpecial=ALL for the last 3 columns in the right position!


And it worked!

I now successfully have the outputted csv file with all 20 fields for every row, whether or not there is data in the last coumns or not.


But now reading your comments, I got it working a second way:

I also tried asa's sugesstion of having "" in each cell (and not " " as per NARAYANK991, as that would mean having spaces transmitted to the database, which is not what I want. I want blanks, not spaces!)... But thanks for this suggestion. I can now get rid of my double copy/pasting code!


---


My individual responses to your feedback:


@NARAYANK991: Correct, I hacve two steps. Copy/Paste and Save as csv.

But it wasnt the csv-saving that got rid of my empy column, it was the Paste=Values (as when I was doing OPaste=All it worked fine!!)


@asa 1: saving to csv might gt rid of the formula and only save the string output, but when it was pasted in to the temp xls file, it was still a formula, and gave me an error!

Eg: in my sourcefile, coulmn 5 has a formula to do a vlookup from a different sheet (within that same original workbook) and return the lookup value. Which it did correctly.

But when I copy that cell, and try pasting (All) to a different workbook (ie my temp workbook that will later become the csv file); then as the new workbook doesnt have the same sheets (ie with the source info for the vlookup), I still got the formula, which as it couldnt do the lookup correctly, all I got was #N/A !!

That's why I had to use PasteSpecial=Values.


@asa 2)

I tried backwards and forwards.

Yes, it was weird behaviour that I was noticing !

I also found that if I had some data in any row in column 20, hen all rows had 20 fields worth of data (most of them empty, ie ",," --- even if only one row had something in column 20.

But if there was only something in the 19th column (and the 20th was totally empty) then I would onlyu get 19 columns worth of data,m and not the 20th!

Anyways, as per above, I got it working via copy/pasting twice, or usin ="" in all of the last 3 columns.


But thanks to you both again :-D

 

[NARAYANK991]

Hi Tisa ,


Thanks for the feedback.


Narayan