Problem with formatting

[weiwensg]

I'm a chess player from Singapore. The pairings of opponents during
competition is done by a very efficient program known as the Swiss-46
The only drawback is that it is in DOS. Thus, copying and pastin
information is a big problem.

Before the tournament, many tournament managers use Excel to handl
information as it comes along. Only during the tournament is Swiss-4
used. Thus, much time is wasted transferring information from Excel t
Swiss-46.

A typical blank Swiss-46 data file looks like this:


Code
-------------------

1 1 #001 0 W v -1--
2 2 #002 0 W v -1--
3 3 #003 0 W v -1--
4 4 #004 0 W v -1--
5 5 #005 0 W v -1--
6 6 #006 0 W v -1--
7 7 #007 0 W v -1--
8 8 #008 0 W v -1--
9 9 #009 0 W v -1--
10 10 #010 0 W v -1--
11 11 #011 0 W v -1--
12 12 #012 0 W v -1--
13 13 #013 0 W v -1--
14 14 #014 0 W v -1--
15 15 #015 0 W v -1--

-------------------


The columns are as such:
Number, Player Number (identical to 'Number' for our purposes), Playe
Name (#001 etc. by default), Player Rating/Seeding, followed b
technical columns for use by the program during the tournament.

The data has to be displayed in this way with exact spacing maintained
For example, there must be 27 letters allowed for player name. If ther
are 6 letters, there must be 21 spaces. If there are 11 letters, ther
must be 16 spaces.

It is the same for Player Rating/Seeding and the other columns.

Lets say we have a typical Excel worksheet:

No. Name Seeding .... (other columns)
1 Andy 1333
2 Bauer 1345
3 Cindy 1357
4 Dick 1369
5 Ely 1381
6 Francis 1393
7 Gaius 1405
8 Huang 1417
9 Ian 1429
10 Jimson 1441
11 Kenneth 1453
12 Lee 1465
13 Murray 1477
14 Nathan 1489
15 Oller 1501

(end of worksheet)

HOW DO I TRANSFER THIS FROM EXCEL TO SWISS-46? IT SEEMS EASY TO ENTE
IT INTO A PROGRAM FOR THIS INSTANCE, BUT FOR OUR MAJOR TOURNAMENT
THERE ARE OVER 500 PARTICIPANTS

It is estimated that we spend 1000 man hours every year transferrin
our data from Excel to Swiss-46. *Is there a way to transfer th
information directly?* I know Excel allows you to save as "Text (Ta
Delimited)", but it doesn't seem to get the problem solved

 

[Trevor Shuttleworth]

This is my reply to a similar problem where all the fields needed to be a
fixed length. Perhaps you can adapt this?

Sub WriteFixedFile()
Dim LastRow As Long
Dim i As Long
Dim sRecord As String
LastRow = Range("A65536").End(xlUp).Row
Open "c:\TESTFILE" For Output As #1 ' Open file for output.
For i = 1 To LastRow
sRecord = Range("A" & i).Value & Space(15 - Len(Range("A" & i)))
sRecord = sRecord & Range("B" & i).Value & Space(5 - Len(Range("B" &
i)))
sRecord = sRecord & Range("C" & i).Value & Space(10 - Len(Range("C" &
i)))
sRecord = sRecord & Range("D" & i).Value & Space(15 - Len(Range("D" &
i)))
'Write #1, sRecord ' data surrounded by quotes
Print #1, sRecord ' no quotes
Next 'i
Close #1
End Sub

You'll need to change the space values to the sizes of the fields and
include entries for each cell in the row.

The assumption with the above code was that the data would always be less in
length than the maximum size of the fields.

Regards

Trevor

I'm a chess player from Singapore. The pairings of opponents during a
competition is done by a very efficient program known as the Swiss-46.
The only drawback is that it is in DOS. Thus, copying and pasting
information is a big problem.

Before the tournament, many tournament managers use Excel to handle
information as it comes along. Only during the tournament is Swiss-46
used. Thus, much time is wasted transferring information from Excel to
Swiss-46.

A typical blank Swiss-46 data file looks like this:


Code:
--------------------

1 1 #001 0 W v -1--
2 2 #002 0 W v -1--
3 3 #003 0 W v -1--
4 4 #004 0 W v -1--
5 5 #005 0 W v -1--
6 6 #006 0 W v -1--
7 7 #007 0 W v -1--
8 8 #008 0 W v -1--
9 9 #009 0 W v -1--
10 10 #010 0 W v -1--
11 11 #011 0 W v -1--
12 12 #012 0 W v -1--
13 13 #013 0 W v -1--
14 14 #014 0 W v -1--
15 15 #015 0 W v -1--

--------------------


The columns are as such:
Number, Player Number (identical to 'Number' for our purposes), Player
Name (#001 etc. by default), Player Rating/Seeding, followed by
technical columns for use by the program during the tournament.

The data has to be displayed in this way with exact spacing maintained.
For example, there must be 27 letters allowed for player name. If there
are 6 letters, there must be 21 spaces. If there are 11 letters, there
must be 16 spaces.

It is the same for Player Rating/Seeding and the other columns.

Lets say we have a typical Excel worksheet:

No. Name Seeding .... (other columns)
1 Andy 1333
2 Bauer 1345
3 Cindy 1357
4 Dick 1369
5 Ely 1381
6 Francis 1393
7 Gaius 1405
8 Huang 1417
9 Ian 1429
10 Jimson 1441
11 Kenneth 1453
12 Lee 1465
13 Murray 1477
14 Nathan 1489
15 Oller 1501

(end of worksheet)

HOW DO I TRANSFER THIS FROM EXCEL TO SWISS-46? IT SEEMS EASY TO ENTER
IT INTO A PROGRAM FOR THIS INSTANCE, BUT FOR OUR MAJOR TOURNAMENTS
THERE ARE OVER 500 PARTICIPANTS.

It is estimated that we spend 1000 man hours every year transferring
our data from Excel to Swiss-46. *Is there a way to transfer the
information directly?* I know Excel allows you to save as "Text (Tab
Delimited)", but it doesn't seem to get the problem solved.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[Stephen Bye]

Set the column widths to correspond to the number of characters allowed for
each field, then save as type "Formatted text (space delimited) (*.prn)".

I'm a chess player from Singapore. The pairings of opponents during a
competition is done by a very efficient program known as the Swiss-46.
The only drawback is that it is in DOS. Thus, copying and pasting
information is a big problem.

Before the tournament, many tournament managers use Excel to handle
information as it comes along. Only during the tournament is Swiss-46
used. Thus, much time is wasted transferring information from Excel to
Swiss-46.

A typical blank Swiss-46 data file looks like this:


Code:
--------------------

1 1 #001 0 W v -1--
2 2 #002 0 W v -1--
3 3 #003 0 W v -1--
4 4 #004 0 W v -1--
5 5 #005 0 W v -1--
6 6 #006 0 W v -1--
7 7 #007 0 W v -1--
8 8 #008 0 W v -1--
9 9 #009 0 W v -1--
10 10 #010 0 W v -1--
11 11 #011 0 W v -1--
12 12 #012 0 W v -1--
13 13 #013 0 W v -1--
14 14 #014 0 W v -1--
15 15 #015 0 W v -1--

--------------------


The columns are as such:
Number, Player Number (identical to 'Number' for our purposes), Player
Name (#001 etc. by default), Player Rating/Seeding, followed by
technical columns for use by the program during the tournament.

The data has to be displayed in this way with exact spacing maintained.
For example, there must be 27 letters allowed for player name. If there
are 6 letters, there must be 21 spaces. If there are 11 letters, there
must be 16 spaces.

It is the same for Player Rating/Seeding and the other columns.

Lets say we have a typical Excel worksheet:

No. Name Seeding .... (other columns)
1 Andy 1333
2 Bauer 1345
3 Cindy 1357
4 Dick 1369
5 Ely 1381
6 Francis 1393
7 Gaius 1405
8 Huang 1417
9 Ian 1429
10 Jimson 1441
11 Kenneth 1453
12 Lee 1465
13 Murray 1477
14 Nathan 1489
15 Oller 1501

(end of worksheet)

HOW DO I TRANSFER THIS FROM EXCEL TO SWISS-46? IT SEEMS EASY TO ENTER
IT INTO A PROGRAM FOR THIS INSTANCE, BUT FOR OUR MAJOR TOURNAMENTS
THERE ARE OVER 500 PARTICIPANTS.

It is estimated that we spend 1000 man hours every year transferring
our data from Excel to Swiss-46. *Is there a way to transfer the
information directly?* I know Excel allows you to save as "Text (Tab
Delimited)", but it doesn't seem to get the problem solved.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[weiwensg]

Thanks very much for your reply. Should I open the files in .xls or .tx
format? Similarily, in which format should I save them?

This is my reply to a similar problem where all the fields neede
to be a
fixed length. Perhaps you can adapt this?

Sub WriteFixedFile()
Dim LastRow As Long
Dim i As Long
Dim sRecord As String
LastRow = Range("A65536").End(xlUp).Row
Open "c:\TESTFILE" For Output As #1 ' Open file for output.
For i = 1 To LastRow
sRecord = Range("A" & i).Value & Space(15 - Len(Range("A" & i)))
sRecord = sRecord & Range("B" & i).Value & Space(5 - Len(Range("B" &
i)))
sRecord = sRecord & Range("C" & i).Value & Space(10 - Len(Range("C
&
i)))
sRecord = sRecord & Range("D" & i).Value & Space(15 - Len(Range("D
&
i)))
'Write #1, sRecord ' data surrounded by quotes
Print #1, sRecord ' no quotes
Next 'i
Close #1
End Sub

You'll need to change the space values to the sizes of the field
and
include entries for each cell in the row.

The assumption with the above code was that the data would always b
less in
length than the maximum size of the fields.

Regards

Trevo