MrExcel Publishing
Your One Stop for Excel Tips & Solutions

moveing from Lotus 123 to Excel 2000 how do I...

Posted by Jim on August 24, 2001 5:59 AM

We use the spreadsheets as data entry models for uploads
to your AS/400. Currently, once the data is input into
Lotus the user will save BY RANGE (B2..U244) part of the
"worksheet" to a text file. How do I do the same function
in Excel?

Thanks for the help


Posted by Mark W. on August 24, 2001 7:45 AM

Copy/Paste to a worksheet and save it as .txt or .csv.

Posted by Jim on August 27, 2001 10:47 AM

Answering my own question

I found several close samples on the internet by
not all the automation I was looking for... the
following is with mods I've made to get the fixed
format I needed...

Original Source from Microsoft Product Suppport Services Article ID: Q249885
Modified 08 Aug 2001

Prerequisites: (Before you run this macro, do the following)

- Select the cells to be included in the text file.
- Use the Style menu commands to format the worksheet to use a fixed-width font.
(For example, Courier is a fixed-width font)


- If the string is wider than the colomn width the left most positions, up to the column width, will be put into the output file. This may truncate your numbers. If you enlarge your column width the problem will be resolved.

Sub textfile()

Dim delimiter As String
Dim quotes As Integer
Dim Returned As String

delimiter = " "

' Specify if you want quotes to surround the cell information
' (0 = no 1 = yes)
quotes = 0

' Call the WriteFile function passing the delimiter and quotes options.
Returned = WriteFile(delimiter, quotes)

' Print a message box indicating if the process was completed.
Select Case Returned
Case "Canceled"
MsgBox "The export operation was canceled."
Case "Exported"
MsgBox "The information was exported."
End Select

End Sub


Function WriteFile(delimiter As String, quotes As Integer) As String

' Dimension variables to be used in this function.
Dim CurFile As String
Dim SaveFileName
Dim CellText As String
Dim RowNum As Integer
Dim ColNum As Integer
Dim FNum As Integer
Dim TotalRows As Double
Dim TotalCols As Double
Dim HalfWidth As Integer

' Show Save As dialog box with the .TXT file name as the default.
' Test to see what kind of system this macro is being run on.
If Left(Application.OperatingSystem, 3) = "Win" Then
SaveFileName = Application.GetSaveAsFilename(CurFile, _
"Text Delimited (*.txt), *.txt", , "Text Delimited Exporter")
SaveFileName = Application.GetSaveAsFilename(CurFile, _
"TEXT", , "Text Delimited Exporter")
End If

' Check to see if Cancel was clicked.
If SaveFileName = False Then
WriteFile = "Canceled"
Exit Function
End If
' Obtain the next free file number.
FNum = FreeFile()

' Open the selected file name for data output.
Open SaveFileName For Output As #FNum

' Store the total number of rows and columns to variables.
TotalRows = Selection.Rows.Count
TotalCols = Selection.Columns.Count

' Loop through every cell, from left to right and top to bottom.
For RowNum = 1 To TotalRows
For ColNum = 1 To TotalCols
With Selection.Cells(RowNum, ColNum)
Dim ColWidth As Integer
ColWidth = Application.RoundUp(.ColumnWidth, 0)
' Store the current cells contents to a variable.
Select Case .HorizontalAlignment
Case xlRight
CellText = Space(ColWidth - Len(.Text)) & .Text
Case xlCenter
HalfWidth = (ColWidth - Len(.Text)) / 2
CellText = Space(HalfWidth) & .Text & _
Space(ColWidth - Len(.Text) - HalfWidth)
Case Else
If ColWidth >= Len(.Text) Then
CellText = .Text & Space(ColWidth - Len(.Text))
CellText = Left(.Text, ColWidth)
End If
End Select
End With
' Write the contents to the file.
' With or without quotation marks around the cell information.
Select Case quotes
Case vbYes
CellText = Chr(34) & CellText & Chr(34) & delimiter
Case vbNo
End Select
Print #FNum, CellText;

' Update the status bar with the progress.
Application.StatusBar = Format((((RowNum - 1) * TotalCols) _
+ ColNum) / (TotalRows * TotalCols), "0%") & " Completed."

' Loop to the next column.
Next ColNum
' Add a linefeed character at the end of each row.
If RowNum <> TotalRows Then Print #FNum, ""
' Loop to the next row.
Next RowNum

' Close the .prn file.
Close #FNum

' Reset the status bar.
Application.StatusBar = False
WriteFile = "Exported"

End Function