Saving Excel as .dat file 1

[ajohn1124]

How do you save Excel as a .dat file? I tried both of the following methods:

METHOD ONE (does not pick up all the columns)
Application.CutCopyMode = False
With ActiveWorkbook.Styles("Normal&quot
.IncludeNumber = False
.IncludeFont = True
.IncludeAlignment = False
.IncludeBorder = False
.IncludePatterns = False
.IncludeProtection = False
End With
With ActiveWorkbook.Styles("Normal&quot.Font
.NAME = "Courier New"
.Size = 10
.Bold = False
.Italic = False
.Underline = xlUnderlineStyleNone
.Strikethrough = False
.ColorIndex = xlAutomatic

End With FileFormat:= _
xlText, CreateBackup:=True

METHOD 2 (DOES NOT SAVE FORMATTING)
Open "Flatfile" & Application.Text(Now(), "mmdd&quot & ".dat" For Output As #1
LASTROW = Application.CountA(ActiveSheet.Range("A:A&quot)

All help is appreciated.

 

[Kaah]

ajohn,

you'll have to fill us in on the datails of what you require of this "*.dat" file.
By the looks of things you're trying to get it back to some sort of a text format?
Have you tried :

activeworkbook.saveas "C:\yorExcelDir\myworkbook.csv", format:=xlcsv

this will get rid of the formatting and seperate each cell with a comma. There are other formats, try them out.

Dan

 

[ajohn1124]

I need the output to be in a fixed column width, with no commas -the "flatfile" command works well, but the drawback is it's 240 character limit per line. The DAT file will be imported back into a financial program. This program using only the .dat format with fixed column widths. I hope this clarifies the matter somewhat.

Thanks for your reply

 

[Kaah]

this code works to open a fixed width file, use it in reverse (and obviously modify the widths to you own end) to save a file:

Workbooks.OpenText FileName:= _
sFile, _
Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:= _
Array(Array(0, 2), Array(24, 2), Array(45, 2), Array(83, 1), Array(99, 1))

Sorry, dont have time to explain it too thoroughly, hope it helps.

K.

 

[ajohn1124]

Thanks for your reply. I am not sure what the code you sent is all about. If I understand, your code opens a fixed width file and if I reverse it, it should save as a fixed width file. Sorry, I am confused. I get information in as an Excel spreadsheet and I need to save it as a .dat file with each column a fixed length.

But thanks anyway.

Audrey

 

[Store300]

You can try the Print# command it allows you to dlimit things with Tabs.

MyFile = "C:\MyData.dat"
Open MyFile for output as #1
Print #1 MyString; Tab ; MyNextString; Tab ;AnotherString
Close #1