I am exporting a set of data including numbers, text, dates.
[Text may be long: up to 2000 char]
They are separated by ";;," to avoid a problem when uploading thereafter (Oracle SQL).
Yesterday I found a solution (posted by someone -- sorry I forgot who -- under another request) which solved my problems... but does not explain why there's no direct way to export from excel using a standard VBA "save / save as filetype" statement.
Below is the solution given which works.
Sub Export_Data(A As Integer)
Dim ObjFs As Object
Dim ObjText As Object
Dim StrData As String
Dim I As Integer
Set ObjFs = CreateObject("Scripting.FileSystemObject")
'create the text file with Path & text filename
Set ObjText = ObjFs.CreateTextFile("C:\MBE\M182\M182_exp.txt", True)
For I = 1 To A - 4
'create a string from the data
StrData = Cells(I, 1) + Cells(I, 2)
'write the string to the text file
'close the text file
'remove the file system object
Set ObjFs = Nothing