asi_app


I'm trying to write out a data line that has a numeric format.

I'm extracting data from SQL into excel, I use some vlookups to change data and then write it out to a text file.

This file is then imported into another application that has specific data format requirements. For example strings have to be quote delimited, numbers are not. Using write, I get the proper formats, except for the dollar amounts. When the number is above $1 the format of 123 works for import. If the number is below $1, such as .71, I'm required to add a preceding 0 to be in format 0.71.

I've tried to add formats but the formats are stripped on output.

I've also looked at doing a text search and replace, to add the 0 but I can't seem to get it to work in VBA.

has someone run into this issue before

Thanks




Re: Writing formatted output

asi_app


I've solved my problem....

I used file system object to write out the fields.

dim fso, datafile

Set fso = CreateObject("Scripting.FileSystemObject")
Set datafile = fso.CreateTextFile(filestrhdr, True)


'file system object to write out the data

datafile.Write (Chr(34) & Hdr_rec_id & Chr(34))
datafile.Write (Chr(44))
datafile.Write (Chr(34) & Vendor_ID & Chr(34))
datafile.Write (Chr(44))
datafile.Write (Chr(34) & Invoice_Number & Chr(34))
datafile.Write (Chr(44))
datafile.Write (Chr(34) & servicer_code & Chr(34))
datafile.Write (Chr(44))
datafile.Write (Chr(34) & Loan_Number & Chr(34))
datafile.Write (Chr(44))
datafile.Write (Chr(34) & investor & Chr(34))
datafile.Write (Chr(44))
datafile.Write (loan_type)
datafile.Write (Chr(44))
datafile.Write (Chr(34) & Format(service_end_date, "mm/dd/yyyy") & Chr(34))
datafile.Write (Chr(44))
datafile.Writeline (Chr(34) & is_final_invoice & Chr(34))


datafile.close

Thanks