另存为.CSV时如何不跳过空的第一个单元格? [英] how not to skip empty first cell when saving as .CSV?

问题描述

我对数据保存到CSV文件的方式有疑问.仅当您尝试保存第一个单元格为空的单列时，才会发生这种情况.您可以选择整个列或一组相邻的单元格.一旦将其保存到CSV文件，就会跳过前导的空白单元格.我将其作为VBA子程序的一部分进行了操作，但手动进行却得到相同的结果.以下是我正在使用的代码和测试数据.

I have a question regarding the way data is saved to CSV files. It only occurs when you are trying to save a single column with first cell(s) empty. You can select the whole column or a group of adjacent cells. As soon as you save it to CSV file, the leading empty cells are skipped. I was doing it as a part of a VBA sub but doing it manually hold same results. Below is the code I'm using and the test data.

Sub CopyRange()
'Copy range: suppose B1 and B2 are empty, B3 to B10 are filled with anything
'e.g. with some numbers
  ActiveSheet.Range("B1:B10").Copy
'Create new workbook
  Workbooks.Add

'NOTE: I need to create a workbook with generic name, save the name and pass it 
'to another program - just to clarity why I don't do it all in one shot at .Copy
  csvPath = TEMP & "\" & ActiveWorkbook.Name & ".csv"

'Paste copied data to the newly created workbook
'NOTE: so far it keeps B1 and B2 empty
  ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Save the workbook as CSV
  ActiveWorkbook.SaveAs Filename:=csvPath, FileFormat:=xlCSV, _
    ReadOnlyRecommended:=False, CreateBackup:=False
'At this point the saved file moves all data 2 cells up 
'to fill the empty spaces in B1 and N2. That's the issue!
End Sub

有趣的是，如果您选择多于一列，则它将保存空白单元格并在正确的位置开始数据.

Interestingly enough, if you select more than one column then it saves the empty cells and starts data at the right location.

我尝试使用xlPasteValues代替xlPasteAll，这实际上使情况变得更糟-现在，即使您有1列以上，它也会在PasteSpecial步骤中跳过空单元格.

I tried using xlPasteValues instead of xlPasteAll which actually make matter worse - now it will skip empty cells even if you had more than 1 column, at PasteSpecial step.

我还尝试将其另存为xlCSVMSDOS，xlCSVWindows(均使用"\ r \ n"行尾字符)和xlCSVMac(使用"\ n |" EOL字符).什么都没用.

I also tried to save it as xlCSVMSDOS, xlCSVWindows (both using "\r\n" End Of Line character) and xlCSVMac (which uses "\n|" EOL character). Nothing worked.

请帮助！

P.S.我还尝试使用此方法但仍然没有运气.

P.S. I also tried to use the method described in this tread but still no luck.

推荐答案

另存为CSV时，Excel似乎使用

When saving as a CSV, Excel seems to use the UsedRange for the sheet.

您可以通过对范围应用某种格式来人为地扩展UsedRange.例如，在保存之前添加此行将使其保存整个范围.

You can artificially expand the UsedRange by applying some formatting to the range. For instance, adding this line prior to the save would make it save the entire range.

ActiveSheet.Range("A1:A10").Interior.ColorIndex = 7

因此，为了在上下文中将其提供给您，您的订阅的结尾将是:

So just to give it to you in context the end of your sub would be this:

ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, _
 Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'Apply some color to the range so that it all gets saved in the CSV.
ActiveSheet.Range("A1:A10").Interior.ColorIndex = 7
'Save the workbook as CSV
ActiveWorkbook.SaveAs Filename:=csvPath, FileFormat:=xlCSV, _
        ReadOnlyRecommended:=False, CreateBackup:=False
'Sheet gets saved as is with cell locations preserved.
End Sub

这篇关于另存为.CSV时如何不跳过空的第一个单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！