在CSV导出Excel工作表数据中忽略空白单元格 [英] Ignoring Blank Cells in CSV Export of Excel Worksheet Data

查看：219
本文介绍了在CSV导出Excel工作表数据中忽略空白单元格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试确定一个方法，用于忽略由Excel工作表数据产生的CSV文件上的空白单元格。



空白单元格将是用户在一行或下一行输入的结果（两行都不会填充，但是脚本中引用的标题需要保持不变）。一行属于一个操作系统的磁盘要求，而其下一行属于另一个操作系统的磁盘要求（如果适用）;磁盘要求的格式仍将应用于当前格式的标题。



目前，当VBA代码应用于Excel工作表时，空白中缺少的数据行通过输入空白单元格来推送下一行的信息。我想找到一种方法让VBA代码忽略空白行，并将后续行的数据输入到此单元格。我使用的VBA代码：

  Sub WriteCSVFile（）
 
 Dim My_filenumber As Integer 
 Dim logSTR As String 
 
 My_filenumber = FreeFile 
 
 logSTR = logSTR& Header1& ，
logSTR = logSTR& Header2& ，
logSTR = logSTR& Header3& ，
logSTR = logSTR& Header4& ，
logSTR = logSTR& Header5& ，
logSTR = logSTR& Header6& ，
logSTR = logSTR& Header7& ，
logSTR = logSTR& Header8& ，
logSTR = logSTR& Header9& ，
logSTR = logSTR& Header10& ，
logSTR = logSTR& Header11& ，
logSTR = logSTR& Header12& ，
logSTR = logSTR& Header13& ，
logSTR = logSTR& Chr（13）
 logSTR = logSTR&单元格（18，C）。 ，
logSTR = logSTR&单元格（19，C）。 ，
logSTR = logSTR&单元格（20，C）。 ，
logSTR = logSTR&单元（21，C）。 ，
logSTR = logSTR&单元格（22，C）。 ，
logSTR = logSTR&单元（26，C）。 ，
logSTR = logSTR&单元格（27，C）。 ，
logSTR = logSTR&单元（28，C）。 ，
logSTR = logSTR&单元格（29，C）。 ，
logSTR = logSTR&单元格（30，C）。 ，
logSTR = logSTR&单元格（31，C）。 ，
logSTR = logSTR&单元（32，C）。 ，



如果没有从logSTR = logSTR&单元格（31，C）。 ，我想从logSTR = logSTR&单元（32，C）。 ，放置在该单元格中，而不是留下空白。这将允许数据的格式（3个值以逗号分隔）与Header11，Header12和Header13（而不是Header12，Header13和No Header）排成一行。

  logSTR = logSTR& Chr（13）
 logSTR = logSTR& & ，
logSTR = logSTR& & ，
logSTR = logSTR& & ，
logSTR = logSTR& & ，
logSTR = logSTR& & ，
logSTR = logSTR&单元（36，C）。 ，
logSTR = logSTR&单元格（37，C）。 ，
logSTR = logSTR&单元格（38，C）。 ，
logSTR = logSTR&单元格（39，C）。 ，
logSTR = logSTR&单元格（40，C）。 ，
logSTR = logSTR&单元（41，C）。 ，
logSTR = logSTR&单元（42，C）。 ，
logSTR = logSTR& Chr（13）
 logSTR = logSTR& & ，
logSTR = logSTR& & ，
logSTR = logSTR& & ，
logSTR = logSTR& & ，
logSTR = logSTR& & ，
logSTR = logSTR&单元（46，C）。 ，
logSTR = logSTR&单元格（47，C）。 ，
logSTR = logSTR&单元格（48，C）。 ，
logSTR = logSTR&单元格（49，C）。 ，
logSTR = logSTR&单元格（50，C）。 ，
logSTR = logSTR&单元（51，C）。 ，
logSTR = logSTR&单元（52，C）。 ，
 
打开Z：\SHARED DRIVE\RequestDirectory\& ThisWorkbook.Name& .csvFor Append As #My_filenumber 
打印#My_filenumber，logSTR 
关闭#My_filenumber 
 
 
结束子
  
解决方案
您可以使用如下的帮助函数：
 函数BuildValuesString（colIndex As String，rows As String）As String 
 Dim val As Variant 
 
对于每个val在Split ）
如果Cells（val，colIndex）<> 然后BuildValuesString = BuildValuesString&单元格（val，colIndex）。 ，
下一个val 
结束函数
  
代码，例如：
  logSTR = logSTR&单元格（18，C）。 ，
logSTR = logSTR&单元格（19，C）。 ，
logSTR = logSTR&单元格（20，C）。 ，
logSTR = logSTR&单元（21，C）。 ，
logSTR = logSTR&单元格（22，C）。 ，
logSTR = logSTR&单元（26，C）。 ，
logSTR = logSTR&单元格（27，C）。 ，
logSTR = logSTR&单元（28，C）。 ，
logSTR = logSTR&单元格（29，C）。 ，
logSTR = logSTR&单元格（30，C）。 ，
logSTR = logSTR&单元格（31，C）。 ，
logSTR = logSTR&单元（32，C）。 ，
  
会变成：
  logSTR = logSTR& BuildValuesString（C，18,19,20,21,22,26,27,28,29,30,31,32）
  
，同样可以使用其他两个辅助函数：
 函数BuildNullStrings （numNullStrings As Long）As String 
 Dim iNullStrings As Long 
 
对于iNullStrings = 1到numNullStrings 
 BuildNullStrings = BuildNullStrings& & ，
 Next iNullStrings 
结束函数
 
函数BuildHeadersString（maxHeader As Long）As String 
 Dim iHeader As Long 
 
对于iHeader = 1 To maxHeader 
 BuildHeadersString = BuildHeadersString& Header& iHeader& ，
 Next iHeader 
 End Function 
  
缩写为：
  Sub WriteCSVFile2（）
 
 Dim My_filenumber As Integer 
 Dim logSTR As String 
 
 My_filenumber = FreeFile 
 
 logSTR = logSTR& BuildHeadersString（13）
 logSTR = logSTR& Chr（13）
 
 logSTR = logSTR& BuildValuesString（C，18,19,20,21,22,26,27,28,29,30,31,32）
 
 logSTR = logSTR& Chr（13）
 logSTR = logSTR& BuildNullStrings（5）
 
 logSTR = logSTR& BuildValuesString（C，36,37,38,39,40,41,42）
 
 logSTR = logSTR& Chr（13）
 logSTR = logSTR& BuildNullStrings（5）
 
 logSTR = logSTR& BuildValuesString（C，46,47,48,49,50,51,52）
 
 
打开Z：\ SHARED DRIVE\RequestDirectory\& ThisWorkbook.Name& .csvFor Append As #My_filenumber 
打印#My_filenumber，logSTR 
关闭#My_filenumber 
 
 
结束子
  
 
I am trying to determine a method for 'ignoring' blank cells on a CSV file that is resulting from an Excel worksheet's data. 



The blank cell will be a result from user's input in one row OR the row below (both rows will never be filled in however, the header being referenced in a script needs to remain the same). One row pertains to one OS's disk requirements while the row below it pertains to an alternative OS's disk requirements, if applicable; the format of the disk requirements will still apply to the header in its current format.



Currently when the VBA code is applied to the Excel worksheet, the missing data from the blank row pertaining to the first OS disk requirements 'pushes' the next row's information by inputting a blank cell. I would like to find a way to have the VBA code 'ignore' the blank row and have the proceeding row's data be input into this cell instead. The VBA code I am using:
Sub WriteCSVFile()

Dim My_filenumber As Integer
Dim logSTR As String

My_filenumber = FreeFile

logSTR = logSTR & "Header1" & " , "
logSTR = logSTR & "Header2" & " , "
logSTR = logSTR & "Header3" & " , "
logSTR = logSTR & "Header4" & " , "
logSTR = logSTR & "Header5" & " , "
logSTR = logSTR & "Header6" & " , "
logSTR = logSTR & "Header7" & " , "
logSTR = logSTR & "Header8" & " , "
logSTR = logSTR & "Header9" & " , "
logSTR = logSTR & "Header10" & " , "
logSTR = logSTR & "Header11" & " , "
logSTR = logSTR & "Header12" & " , "
logSTR = logSTR & "Header13" & " , "
logSTR = logSTR & Chr(13)
logSTR = logSTR & Cells(18, "C").Value & " , "
logSTR = logSTR & Cells(19, "C").Value & " , "
logSTR = logSTR & Cells(20, "C").Value & " , "
logSTR = logSTR & Cells(21, "C").Value & " , "
logSTR = logSTR & Cells(22, "C").Value & " , "
logSTR = logSTR & Cells(26, "C").Value & " , "
logSTR = logSTR & Cells(27, "C").Value & " , "
logSTR = logSTR & Cells(28, "C").Value & " , "
logSTR = logSTR & Cells(29, "C").Value & " , "
logSTR = logSTR & Cells(30, "C").Value & " , "
logSTR = logSTR & Cells(31, "C").Value & " , "
logSTR = logSTR & Cells(32, "C").Value & " , "

If there is no data resulting from logSTR = logSTR & Cells(31, "C").Value & " , " I would like to have the data from logSTR = logSTR & Cells(32, "C").Value & " , " placed in that cell rather than leaving a blank. This would allow the data's format (3 values separated by a comma) line up with Header11, Header12 and Header13 (and not Header12, Header13 and No Header).
logSTR = logSTR & Chr(13)
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & Cells(36, "C").Value & " , "
logSTR = logSTR & Cells(37, "C").Value & " , "
logSTR = logSTR & Cells(38, "C").Value & " , "
logSTR = logSTR & Cells(39, "C").Value & " , "
logSTR = logSTR & Cells(40, "C").Value & " , "
logSTR = logSTR & Cells(41, "C").Value & " , "
logSTR = logSTR & Cells(42, "C").Value & " , "
logSTR = logSTR & Chr(13)
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & "" & " , "
logSTR = logSTR & Cells(46, "C").Value & " , "
logSTR = logSTR & Cells(47, "C").Value & " , "
logSTR = logSTR & Cells(48, "C").Value & " , "
logSTR = logSTR & Cells(49, "C").Value & " , "
logSTR = logSTR & Cells(50, "C").Value & " , "
logSTR = logSTR & Cells(51, "C").Value & " , "
logSTR = logSTR & Cells(52, "C").Value & " , "

Open "Z:\SHARED DRIVE\RequestDirectory\" & ThisWorkbook.Name & ".csv" For Append As #My_filenumber
    Print #My_filenumber, logSTR
Close #My_filenumber


End Sub 



 解决方案 
you could use a helper function like follows:
Function BuildValuesString(colIndex As String, rows As String) As String
    Dim val As Variant

    For Each val In Split(rows, ",")
        If Cells(val, colIndex) <> "" Then BuildValuesString = BuildValuesString & Cells(val, colIndex).Value & " , "
    Next val
End Function

and exploit it in your main code where, for instance:
logSTR = logSTR & Cells(18, "C").Value & " , "
logSTR = logSTR & Cells(19, "C").Value & " , "
logSTR = logSTR & Cells(20, "C").Value & " , "
logSTR = logSTR & Cells(21, "C").Value & " , "
logSTR = logSTR & Cells(22, "C").Value & " , "
logSTR = logSTR & Cells(26, "C").Value & " , "
logSTR = logSTR & Cells(27, "C").Value & " , "
logSTR = logSTR & Cells(28, "C").Value & " , "
logSTR = logSTR & Cells(29, "C").Value & " , "
logSTR = logSTR & Cells(30, "C").Value & " , "
logSTR = logSTR & Cells(31, "C").Value & " , "
logSTR = logSTR & Cells(32, "C").Value & " , "

would become:
logSTR = logSTR & BuildValuesString("C", "18,19,20,21,22,26,27,28,29,30,31,32")

and, in the same fashion you could use other two helpers:
Function BuildNullStrings(numNullStrings As Long) As String
    Dim iNullStrings As Long

    For iNullStrings = 1 To numNullStrings
        BuildNullStrings = BuildNullStrings & "" & " , "
    Next iNullStrings
End Function

Function BuildHeadersString(maxHeader As Long) As String
    Dim iHeader As Long

    For iHeader = 1 To maxHeader
        BuildHeadersString = BuildHeadersString & "Header" & iHeader & " , "
    Next iHeader
End Function

so that your whole code would shorten down to:
Sub WriteCSVFile2()

    Dim My_filenumber As Integer
    Dim logSTR As String

    My_filenumber = FreeFile

    logSTR = logSTR & BuildHeadersString(13)
    logSTR = logSTR & Chr(13)

    logSTR = logSTR & BuildValuesString("C", "18,19,20,21,22,26,27,28,29,30,31,32")

    logSTR = logSTR & Chr(13)
    logSTR = logSTR & BuildNullStrings(5)

    logSTR = logSTR & BuildValuesString("C", "36,37,38,39,40,41,42")

    logSTR = logSTR & Chr(13)
    logSTR = logSTR & BuildNullStrings(5)

    logSTR = logSTR & BuildValuesString("C", "46,47,48,49,50,51,52")


    Open "Z:\SHARED DRIVE\RequestDirectory\" & ThisWorkbook.Name & ".csv" For Append As #My_filenumber
        Print #My_filenumber, logSTR
    Close #My_filenumber


End Sub



                        
这篇关于在CSV导出Excel工作表数据中忽略空白单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
相关文章
Office最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆