Excel VBA:宏仅导出非空白单元格 [英] Excel VBA: Macro to only export non blank cells

问题描述

大家好，

我有这个宏，该宏可以导出所有带公式的单元格，但带有空白输出.

I have this macro, which exports all cells with formulas, BUT with blank outputs.

我只希望导出显示为非空白的单元格.有什么想法吗?

I only want the cells displaying as non blank to export. Any ideas?

Sub Export_A()

Dim sPath As String
Dim SFile As String
Dim nLog As Integer

sPath = "C:\AAAWork\"
SFile = sPath & ActiveSheet.Range("P9") & ".txt"

nfile = FreeFile

Open SFile For Output As #nfile

For i = 1 To ActiveSheet.UsedRange.Rows.Count

Set ThisCell = ActiveSheet.Range("A" & i)

If ThisCell.Text <> "" Then
 '   sInDate = ThisCell.Text

    'sOutDate = Format(ThisCell.Value, "mm/yyyy")
    sOutDate = Format(ThisCell.Value, "yyyy-mm")
    'stemp = """" & sOutDate & """" this gives the date the " in the    
beginning and end
    stemp = "" & sOutDate & ""


    For j = 1 To 10
        If j = 1 Or j = 2 Or j = 9 Then
            stemp = stemp & ";" & ThisCell.Offset(0, j)
        Else
            'stemp = stemp & "," & """" & ThisCell.Offset(0, j) & """" This 
gives every value a " beginning and end
            stemp = stemp & ";" & ThisCell.Offset(0, j)
        End If
    Next
End If
Print #nfile, stemp
Next
Close #nfile


MsgBox ("Completed a file called " & SFile & " has been generated")

End Sub

这是导出为CSV的一种有趣方式，但它是继承的，并且在其他方​​面做得很好.

This is an interesting way of exporting to CSV, but it was inherited and does everything else very well.

推荐答案

尝试将写入行置于For循环的末尾

Try placing the Write line at the end of the For loop

Sub Export_A()

Dim sPath As String
Dim SFile As String
Dim nLog As Integer

sPath = "C:\AAAWork\"
SFile = sPath & ActiveSheet.Range("P9") & ".txt"

nfile = FreeFile

Open SFile For Output As #nfile

For i = 1 To ActiveSheet.UsedRange.Rows.Count

Set ThisCell = ActiveSheet.Range("A" & i)

If ThisCell.Text <> "" Then
 '   sInDate = ThisCell.Text

    'sOutDate = Format(ThisCell.Value, "mm/yyyy")
    sOutDate = Format(ThisCell.Value, "yyyy-mm")
    'stemp = """" & sOutDate & """" this gives the date the " in the beginning and end
    stemp = "" & sOutDate & ""


    For j = 1 To 10
        stemp = stemp & ";" & ThisCell.Offset(0, j)
    Next

    Print #nfile, stemp

End If

Next
Close #nfile


MsgBox ("Completed a file called " & SFile & " has been generated")

End Sub

这篇关于Excel VBA:宏仅导出非空白单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！