How can store a table's range of excel worksheet in dbf format by unicode encoding?

Question

I'm using Excel 2010,and need store a range of table in dbf by unicode encoding. I tried bellow:

Workbook.SaveAs FileName:="test.dbf", FileFormat:=xlDBF4, CreateBackup:=False

but get error.

How can I do that?

Answer 1

You might want to look into using Scripting.FileSystemObject (fso), because with unicode there are subtle differences in how files are encoded and using fso gives you more control. For example, in one of my projects I needed to save files as plain utf-8 rather than utf-8-bom. The default behavior is to save files as utf-8-bom, which means there are 3 hidden characters called a byte order mark (BOM) placed at the begining of the file. In the code below, these 3 characters are removed by copying to a new stream before saving the file as plain utf-8

Dim fso As Scripting.FileSystemObject, stream1 As Stream, stream2 As Stream
Sub saveFileAfterRemovingBOM(path As String)
stream1.Position = 3 'skip BOM (byte order mark)
Set stream2 = CreateObject("ADODB.Stream")
 With stream2
  .Type = adTypeBinary
  .Mode = adModeReadWrite
  .Open
  stream1.CopyTo stream2
  stream1.Flush
  stream1.Close
  .SaveToFile path, adSaveCreateNotExist 'creates the file if it doesn't exist
  .Flush
  .Close
 End With
End Sub

The Stream & Scripting.FileSystemObject requires adding References in the VBE as follows: Microsoft Scripting Runtime & Microsoft ActiveX Data Objects Library (v6.1 as of 10/16). To add these in the VBE use Tools-->References...