Replies

“12-34-567-8912345.JOHN DOE” .”MARY SMITH.MIKE JONES” .
7FF6FF6FFF6FFFFFFF0DDCD4CDC7407DCDE4EDCEC0DCDC4DDDCE7404444444444444444444444444
F12034056708912345A16850465F05F4198024938A4925016552F050000000000000000000000000

In this string the *0A* returns Z with an inverted caret above it. The tab works as expected.

I am the middle man here so I will try to explain things as I understand them. A file is created through a mainframe program which produces a text file. The file is then given an xls extension so the recipients open it in excel. The program that produces the file is where the wrap coding needs to be. That’s where 0A was tried. It produced a Z with the ^ above it. I am not writing the program. I was just boasting about Woody’s Lounge and I was asked to post the question. The objective is when the tab delimited file is opened in Excel it appropiately wraps text in the cells designated in the coding.

The recipients of these files are not “computer” literate, so you have to do everything for them.

Thanks for thinking about this. My dedication ends in 5 minutes and LET THE WEEKEND BEGIN!!!

We tried 0A. 0D wrapped the text to the next cell. We need to wrap within the cell.

It will be embedded in a mainframe text file to cause certain strings of text to wrap automatically when the file is opened in Excel. That is how the programmer explained it to me.

What would be the hexidecimal equivalent?

That is so slick. My comments always credit Woody’s Lounge. Thanks for the help.

When I read your suggestion, rather than ask how I would do it, I thought I would just do what I understood for now. Creating the text file was working…but then I began to get inconsistent results. When I set up the import specification I walked through the wizard and choosing TAB delimited it indicated two columns. The second column had the data I wanted so I imported column two. Today the same file, TAB delimited, is only one column so I get nothing. I walked through the wizard and if I use COMMA delimited I get two columns again but it splits at a different point. Now I am really confused. Then I remembered your suggestion to use the Dir command within my procedure, so I decided to ask how to modify my code to skip making the text file.

Here is my current procedure:

Private Sub cmdGetFileList_Click()
Shell “cmd /c del c:SCPFiles.txt”
Shell “cmd /c dir “”M:Synoptic Coding Project – 2006 FebruaryCompleted – Admin Reviews”” /s” _
& “>>c:SCPFiles.txt”
MsgBox “This step imports SCPFiles.txt into the impSCPFiles table.”, vbInformation, “Button Explanation”
DoCmd.SetWarnings False
DoCmd.OpenQuery “qry_EMPTY_impSCPFiles”
DoCmd.OpenQuery “qry_EMPTY_tblFileNames”
DoCmd.TransferText acImportDelim, “SCPFiles Import Specification”, _
“impSCPFiles”, “C:SCPFiles.txt”, False
DoCmd.OpenQuery “qryAppendFileNames”
DoCmd.SetWarnings True
MsgBox “Import Completed”
End Sub

Gee whiz…I replied to myself. Fortunately, it was a simple error made when the folder was created. It works fine.

Thanks again for your time. I really thought I had an issue here. Sorry.

Actually, I was able to test the runtime on a machine with office 2000 pro, but not with office 2000 standard. That’s how I found out the runtime disabled Access 2000 so every database opened in runtime. There was a service patch that fixed that.

Thanks for your time.

Thank You.

Why are you closing the form before the routine?

Gentleman, your expertise is greatly appreciated. God bless you.

rory, you know I love this stuff, but I know so little. I looked up late binding in the knowledge base since I was not familiar with the term and applied your example. I am converting text files to excel format. Here’s my procedure:
Public Function ConvertFiles()
Dim RS As DAO.Recordset, DB As DAO.Database
Dim strFileName As String
Dim xlObj As Object
Dim xlWbk As Object
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(“tblFileNames”)
‘On Error Resume Next
RS.MoveFirst
Do Until RS.EOF
strFileName = RS(“Folder”) & “” & RS(“FileNames”)
Set xlObj = CreateObject(“Excel.Application”)
xlObj.DisplayAlerts = False
Set xlWbk = xlObj.Workbooks.Open(strFileName)
xlWbk.SaveAs FileName:= _
strFileName, FileFormat:=xlNormal, Password:=””, WriteResPassword:=””, _
ReadOnlyRecommended:=False, CreateBackup:=False
RS.MoveNext
xlWbk.Close SaveChanges:=True
Set xlWbk = Nothing
xlObj.Quit
Set xlObj = Nothing
Loop
xlObj.DisplayAlerts = True
RS.Close
End Function

I figure I could drop everything past “strFileName, FileFormat:=xlNormal”, but I don’t know how to declare xlNormal.

That did it! Thanks again. Just curious though…why set the alerts to true after each loop? Why not just at the end?

Works every time now. Is there a way to turn off the warnings? Since I’m not renaming the files I get the “file already exists” warning for each file as it is saved.