By mkc


2019-06-12 07:38:03 8 Comments

I have the following VBA code that creates four files .csv csv.sha512 csv.sha256 and .csv.gz based on the excel table I have created.

Private Sub Export_Click()

Version = "1.0"
MyFileName = ActiveWorkbook.Name
MyFilePath = ActiveWorkbook.Path
MyFileName = Replace(MyFileName, ".xls", ".idl")
IdFiles = 1
fileName = MyFilePath & "\" & Cells(1, 3).Value & "_" & Format(Date, "yyyymmdd") & "_" & IdFiles & ".csv"

SelectionOnly = False
AppendData = False
delimeter = "|"
Application.ScreenUpdating = False
On Error GoTo EndMacro:


If SelectionOnly = True Then
    With Selection
        StartRow = 7
        StartCol = 2
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
Else
    With ActiveSheet.UsedRange
        StartRow = 7
        StartCol = 2
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
End If

CountRow = EndRow - StartRow + 1


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFSOBlokada = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(fileName) Then
    IsExistCSV = True


    Do While IsExistCSV = True
       fileName = MyFilePath & "\" & Cells(1, 3).Value & "_" & Format(Date, "yyyymmdd") & "_" & IdFiles & ".csv"

       If Not objFSO.FileExists(fileName) Then
          IsExistCSV = False
       End If
       IdFiles = IdFiles + 1 
    Loop
End If

Set objFile = objFSO.OpenTextFile(fileName, 2, True, -1)
objFile.Writeline Version & delimeter & CountRow

    For RowNdx = StartRow To EndRow
        WholeLine = ""
        For ColNdx = StartCol To EndCol

            If Cells(RowNdx, ColNdx).Value = "" Then
                CellValue = ""
            Else
               CellValue = Cells(RowNdx, ColNdx).Value
            End If


            If ColNdx = EndCol Then
                WholeLine = WholeLine & CellValue
            Else
                WholeLine = WholeLine & CellValue & delimeter
            End If
        Next ColNdx
        WholeLine = Left(WholeLine, Len(WholeLine) - Len(sep))
        objFile.Writeline WholeLine
    Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True

objFile.Close



Set objFileBlokada = objFSOBlokada.OpenTextFile(fileName & ".blokada", 2, True, -1)
objFileBlokada.Close


Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 0

shellcmd1 = "cmd.exe /C certutil -hashfile """ & fileName & """ SHA512 | find /i /v ""sha512"" | find /i /v ""certutil"" > """ & fileName & ".sha512"""
wsh.Run shellcmd1, windowStyle, waitOnReturn


shacmd256 = "cmd.exe /C certutil -hashfile """ & fileName & """ SHA256 | find /i /v ""sha256"" | find /i /v ""certutil"" > """ & fileName & ".sha256"""


shellcmd2 = "cmd /C """"c:\Program Files\7-Zip\7z.exe"" a -w""" & MyFilePath & """ -tgzip """ & fileName & ".gz"" " & """" & fileName & """"""


End Sub

However files created are with BOM and with UCS2 encoding I need to have them with utf-8 encoding with LF as line end whereas now i have crlf as line end. How to fixt that?

Futhermore I need to have utf-8 without BOM which is quite difficult to achieve.

Any ideas how to even approach that?

0 comments

Related Questions

Sponsored Content

17 Answered Questions

[SOLVED] Save PL/pgSQL output from PostgreSQL to a CSV file

13 Answered Questions

[SOLVED] How to avoid using Select in Excel VBA

  • 2012-05-23 05:57:58
  • BiGXERO
  • 250623 View
  • 500 Score
  • 13 Answer
  • Tags:   excel vba

9 Answered Questions

[SOLVED] Writing a pandas DataFrame to CSV file

30 Answered Questions

[SOLVED] How to output MySQL query results in CSV format?

  • 2008-12-10 15:59:51
  • MCS
  • 1094773 View
  • 1094 Score
  • 30 Answer
  • Tags:   mysql csv quotes

16 Answered Questions

[SOLVED] How to import CSV file data into a PostgreSQL table?

42 Answered Questions

[SOLVED] How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 1027860 View
  • 1776 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

36 Answered Questions

[SOLVED] Excel to CSV with UTF8 encoding

24 Answered Questions

[SOLVED] Dealing with commas in a CSV file

  • 2009-04-20 18:52:33
  • Bob The Janitor
  • 499422 View
  • 447 Score
  • 24 Answer
  • Tags:   csv

3 Answered Questions

[SOLVED] Excel VBA macro to send emails to unique users in range

  • 2018-07-02 17:22:03
  • HunterTTP
  • 918 View
  • 2 Score
  • 3 Answer
  • Tags:   excel vba excel-vba

1 Answered Questions

[SOLVED] VBA Export Excel to XML format and save to specific directory

Sponsored Content