时间戳

首页 > 解决方案 > 将选定范围导出到 *.txt

excel - 将选定范围导出到 *.txt

问题描述

我有一张这样的桌子：

Date        Réf A   Réf B   Client
21/05/18    AA      BB      Alain
22/05/18    CC      DD      Denise
22/05/18    CC      DD      Denise
25/05/18    EE      FF      Fred
25/05/18    EE      FF      Felipe
25/05/18    EE      FF      Fred
26/05/18    GG      HH      Hugo
02/06/18    II      JJ      Jacky

我想构建一个宏，将我导出到 txt 文件（制表符分隔符（这很重要）），此表但仅在所选范围内。

例如：

宏（Date1，Date2），带有

日期1 = 21/05/2018

日期 2 = 25/05/2018

...只向我导出前 6 行。

我有一段代码在定义的范围内构建导出，但我不知道如何调整它：

Sub Export()
    Dim Plage As Object, oL As Object, oC As Object, Sep$, Tmp$
    Dim FileN As String
    FileN = Sheets("Feuil1").Range("Z1")         'Nom du fichier créé
    Sep = vbTab
    With Worksheets(1)
        Set Plage = .Range("A1:D11")
    End With
    FileN = ThisWorkbook.Path & "\test\" & FileN
    Open FileN & ".txt" For Output As #1
    For Each oL In Plage.Rows
        Tmp = ""
        For Each oC In oL.Cells
            Tmp = Tmp & CStr(oC.Text) & Sep
        Next
        Print #1, Tmp
    Next
    Close
End Sub

这里我附上了一个例子： 示例图像

标签： excelvba

解决方案

好的，让我知道这是否适合您。我冒昧地重新格式化了您的初始代码。最大的变化是现在Plage是根据存储在Sheets(1). 新函数GetRange尝试将 A 列中的值与开始和结束日期单元格中的值进行匹配。

Sub Export()

    Dim FileN As String
    FileN = ThisWorkbook.path & "\test\" & Sheets("Feuil1").Range("Z1").Text     'Nom du fichier créé

    Dim Plage As Range
    Set Plage = getRange

    Dim oL As Range
    Dim oC As Range

    Dim Tmp As String
    Dim Sep As String
    Sep = vbTab

    Open FileN & ".txt" For Output As #1

    For Each oL In Plage.Rows
        Tmp = ""
        For Each oC In oL.Cells
            Tmp = Tmp & CStr(oC.Text) & Sep
        Next
        Print #1, Tmp
    Next

    Close

End Sub

Function GetRange() As Range

    Dim date1 As String
    date1 = Sheets(1).Range("H3").Text

    Dim date2 As String
    date2 = Sheets(1).Range("H4").Text

    Dim i As Integer
    Dim RowStart As Integer
    Dim RowStop As Integer

    With Sheets(1)
        For i = 2 To .Cells(.Rows.count, "A").End(xlUp).Row
            If .Cells(i, "A").Text = date1 Then
                RowStart = i
                Exit For
            End If
        Next i

        For i = 2 To .Cells(.Rows.count, "A").End(xlUp).Row
            If .Cells(i, "A").Text = date2 Then
                RowStop = i
                Exit For
            End If
        Next i

        'depending on your data, maybe make sure RowStop > RowStart
    End With

    Set getRange = Range("A" & RowStart & ":D" & RowStop)

End Function

推荐阅读