dodaj hasło do csv w vba i spakuj

Question

Jak dodać szyfrowanie z hasłem do pliku csv i spakowanie (.zip) pliku wyjściowego?

Sub ExportRange()
    Dim Filename As String
    Dim NumRows As Long, NumCols As Integer
    Dim r As Long, c As Integer
    Dim Data
    Dim ExpRng As Range
    Set ExpRng = Application.Intersect(Selection, ActiveSheet.UsedRange)
    NumCols = ExpRng.Columns.Count
    NumRows = ExpRng.Rows.Count
    Filename = Application.DefaultFilePath & "\textfile.csv"
    Open Filename For Output As #1
        For r = 1 To NumRows
            For c = 1 To NumCols
                Data = ExpRng.Cells(r, c).Value
                If IsNumeric(Data) Then Data = Val(Data)
                If IsEmpty(ExpRng.Cells(r, c)) Then Data = ""
                If c <> NumCols Then
                    Write #1, Data;
                Else
                    Write #1, Data
                End If
            Next c
        Next r
    Close #1
    MsgBox ExpRng.Count & " cells were exported to " & Filename, vbInformation
End Sub

dodać szyfrowanie z hasłem do pliku csv i ** zip ** (. zip) Plik wyjściowy?

Answer 1

Możliwe jest również spakowanie pliku przy użyciu standardowego programu WinZip. Proszę, wypróbuj następny kod. Mam zainstalowany program WinZip na ścieżce w kodzie. Można go również wyodrębnić z rejestru:

Private Sub testZipPassProtected()
 Dim strWinZipDir As String, strDestFileName As String, strSourceFileName As String
 Dim strCommand As String, strWinzip As String, strPassword As String

 strWinZipDir = "C:\Program Files\WinZip\WINZIP64.exe"
 strDestFileName = Application.DefaultFilePath & "\fileCSVStandard.zip"
 strSourceFileName = Application.DefaultFilePath & "\textfile.csv"
 strPassword = "1234"

 strCommand = """" & strWinZipDir & """ -min -a -s""" & strPassword & _
    """ -r """ & strDestFileName & """ " & """" & strSourceFileName & """"
 Shell strCommand

 MsgBox "The file " & vbCrLf & _
        """" & strSourceFileName & """ has been Zipped as " & vbCrLf & _
        """" & strDestFileName & """, using Password: " & strPassword & "."
End Sub