More on loading a CVS into an empty formatted XLS

[Paul H]

Dave,

I tried the macro solution - it works! I can read the entire 5005 row x 42
column file in about 3 seconds. Now I have confusion about the process.
Where is the macro stored? I created a folder with 2 files - the .CSV file
to be read and the empty .XLS file with the name I want it to have. I
invoke it by pressing ctl-m. I'll change that to Autpen so it will run
when I open the .XLS file. When I move the folder with the 2 files to
another computer, the macro doesn't work. So the macro didn't come with the
..XLS file.

I don't know what you mean about the "You could plop the date into a cell in
a hidden sheet so that it only runs once. And add a save at the end".
Also, I want to delete a macro so I can re-record it, but it talks about
un-hiding something. What?

Thanks for your help.
Paul
=========================================================


the macro recorder will show you the very basic VBA code. you'll want to
tidy up
but its a great way to start

I tried it using Excel 2003 and it works there also. I'll try the entire
process tomorrow (Monday). Wish me luck, and thanks.
Paul

=========================================================

In Excel 2007 you will need to be able to see the [Developer] tab. If it
is
not visible now, click the Office Button, then the [Excel Options] button
near the lower right of the window that opens. In the "Popular" group,
enable the "Show Developer tab in the ribbon" option.
To record a macro, choose the Developer tab and click the "Record Macro" -
give it a name and procede with the steps you wish to record. The "Record
Macro" option will have changed to "Stop Recording". Click it when you
have
finished recording the steps you need to repeat later.

=========================================================

How do I record a macro? Do you mean I can create a macro that will do
my
steps 1 thru 8, below?

=========================================================
It's too difficult to push a button?

If that's true, then name your macro Autpen. It'll run the first time
someone opens the workbook. You could plop the date into a cell in a
hidden
sheet so that it only runs once. And add a save at the end.

If that doesn't work, good luck with the automation.

I have an empty, formatted spreadsheet, that I created by writing a few rows
into it from my COBOL program, field by field, then deleting all of the
rows.

The process of creating the entire XLS or XLSX runs much too slow (100
records per minute or less), and uses memory up, so can never allow me to
finish converting some of my large CSV files into formatted XLS (Excel 2003)
or XLSX (Excel 2007) files. Someone told me to "turn off continual
re-calculating". How?

I wonder if I can somehow import my large .CSV file into my .XLS empty
formatted file? I cannot find a way. Any help would be appreciated.

TIA, Paul

 

[Paul H]

I meant to put this in both Excel newsgroups.


Dave,

I tried the macro solution - it works! I can read the entire 5005 row x 42
column file in about 3 seconds. Now I have confusion about the process.
Where is the macro stored? I created a folder with 2 files - the .CSV file
to be read and the empty .XLS file with the name I want it to have. I
invoke it by pressing ctl-m. I'll change that to Autpen so it will run
when I open the .XLS file. When I move the folder with the 2 files to
another computer, the macro doesn't work. So the macro didn't come with the
..XLS file.

I don't know what you mean about the "You could plop the date into a cell in
a hidden sheet so that it only runs once. And add a save at the end".
Also, I want to delete a macro so I can re-record it, but it talks about
un-hiding something. What?

Thanks for your help.
Paul
=========================================================


the macro recorder will show you the very basic VBA code. you'll want to
tidy up
but its a great way to start

I tried it using Excel 2003 and it works there also. I'll try the entire
process tomorrow (Monday). Wish me luck, and thanks.
Paul

=========================================================

In Excel 2007 you will need to be able to see the [Developer] tab. If it
is
not visible now, click the Office Button, then the [Excel Options] button
near the lower right of the window that opens. In the "Popular" group,
enable the "Show Developer tab in the ribbon" option.
To record a macro, choose the Developer tab and click the "Record Macro" -
give it a name and procede with the steps you wish to record. The "Record
Macro" option will have changed to "Stop Recording". Click it when you
have
finished recording the steps you need to repeat later.

=========================================================

How do I record a macro? Do you mean I can create a macro that will do
my
steps 1 thru 8, below?

=========================================================
It's too difficult to push a button?

If that's true, then name your macro Autpen. It'll run the first time
someone opens the workbook. You could plop the date into a cell in a
hidden
sheet so that it only runs once. And add a save at the end.

If that doesn't work, good luck with the automation.

I have an empty, formatted spreadsheet, that I created by writing a few rows
into it from my COBOL program, field by field, then deleting all of the
rows.

The process of creating the entire XLS or XLSX runs much too slow (100
records per minute or less), and uses memory up, so can never allow me to
finish converting some of my large CSV files into formatted XLS (Excel 2003)
or XLSX (Excel 2007) files. Someone told me to "turn off continual
re-calculating". How?

I wonder if I can somehow import my large .CSV file into my .XLS empty
formatted file? I cannot find a way. Any help would be appreciated.

TIA, Paul

 

[Dave Peterson]

Macros live in workbooks. So your macro (probably???) lives in that .xls file
in the same folder as the .csv. But I don't have a real guess at where you
stored this macro--could it be in your personal.xl* workbook?

Personally, I don't like running this kind of macro by a shortcut or by using
auto_open. I have to remember the shortcut and make sure that anyone who runs
this when I'm not there (vacation???) can remember it, too.

And I wouldn't use auto_open. If I open the workbook for some other purpose
later in the day, I don't want to have it run automatically.

Instead, I just plop a button from the Forms toolbar onto a worksheet and assign
the macro to that button. Add some instructions (for both me and other users)
to a separate worksheet (or below the button) and I'm set.

This will mean that I don't have to check to see if the macro has been run
earlier in the day in my code.

But the choice is yours (obviously).

Dave,

I tried the macro solution - it works! I can read the entire 5005 row x 42
column file in about 3 seconds. Now I have confusion about the process.
Where is the macro stored? I created a folder with 2 files - the .CSV file
to be read and the empty .XLS file with the name I want it to have. I
invoke it by pressing ctl-m. I'll change that to Autpen so it will run
when I open the .XLS file. When I move the folder with the 2 files to
another computer, the macro doesn't work. So the macro didn't come with the
.XLS file.

I don't know what you mean about the "You could plop the date into a cell in
a hidden sheet so that it only runs once. And add a save at the end".
Also, I want to delete a macro so I can re-record it, but it talks about
un-hiding something. What?

Thanks for your help.
Paul
=========================================================


the macro recorder will show you the very basic VBA code. you'll want to
tidy up
but its a great way to start

I tried it using Excel 2003 and it works there also. I'll try the entire
process tomorrow (Monday). Wish me luck, and thanks.
Paul

=========================================================

In Excel 2007 you will need to be able to see the [Developer] tab. If it
is
not visible now, click the Office Button, then the [Excel Options] button
near the lower right of the window that opens. In the "Popular" group,
enable the "Show Developer tab in the ribbon" option.
To record a macro, choose the Developer tab and click the "Record Macro" -
give it a name and procede with the steps you wish to record. The "Record
Macro" option will have changed to "Stop Recording". Click it when you
have
finished recording the steps you need to repeat later.

=========================================================

How do I record a macro? Do you mean I can create a macro that will do
my
steps 1 thru 8, below?

=========================================================
It's too difficult to push a button?

If that's true, then name your macro Autpen. It'll run the first time
someone opens the workbook. You could plop the date into a cell in a
hidden
sheet so that it only runs once. And add a save at the end.

If that doesn't work, good luck with the automation.

Paul H wrote:

=========================================================
I need the COBOL program to properly format the data. Multiple
end-users
will use this, sometimes daily, with data that changes daily, so I have
been
requested to make it completely automatic. Another subsequent program
shows
them the choices of reports and automatically starts the one they
select.

=========================================================

Maybe you could drop the requirement that the COBOL program do it.

Set up a workbook with two sheets (instructions for the user and the
actual
data).

Record a macro that does all the work in the second sheet.

The plop a button from the Forms toolbar onto the instruction sheet
that
calls
that macro.

You only have to rename the worksheet if you're doing File|Open (or the
equivalent in code). If you use the import external data stuff, you
can
leave
it named .csv.

=========================================================

Paul H wrote:

Thanks Dave,

I used your "import text" method and it does succeed in importing my
.CSV
file into my empty .XLS file, in about 2 seconds. That is, after I
manually:

1. Open empty XLS file.
2. Data, import external data, import data.
3. "Select Data Source" screen comes up.
4. Type in my xxxx.TXT file name.
5. Import wizard step 1 - select delimited, then next.
6. Import wizard step 2 - select only Comma, then finish.
7. Import data to existing worksheet.
8. It imports the entire file in about 2 seconds, formatted
correctly,
with
columns as described in my empty .XLS file.

Now I need to figure out how to accomplish these steps automatically
by
my
COBOL program.

Thanks again,
Paul

=========================================================
If you rename the .csv file to .txt, you may be able to import the
file
quicker
by setting each field the way you want.

Then you could format the numeric fields as percentages or whatever
you
needed.

If you have fields that have implicit decimals, you could import them
as
Generals and then put a factor of 10 (10, 100, 1000, ...) in an empty
cell.
Edit|copy, edit|paste special|Values and divide (all in code) to
convert
that
field.
=========================================================
Paul H wrote:

I have an empty, formatted spreadsheet, that I created by writing a few rows
into it from my COBOL program, field by field, then deleting all of the
rows.

The process of creating the entire XLS or XLSX runs much too slow (100
records per minute or less), and uses memory up, so can never allow me to
finish converting some of my large CSV files into formatted XLS (Excel 2003)
or XLSX (Excel 2007) files. Someone told me to "turn off continual
re-calculating". How?

I wonder if I can somehow import my large .CSV file into my .XLS empty
formatted file? I cannot find a way. Any help would be appreciated.

TIA, Paul

 

[Paul H]

=========================================================

Will the macro stay with the .XLS file? Can I distribute just the 2 files -
the .CSV file to be read, and the empty .XLS file with the name I want it to
have?

And what do I un-hide so I can remove a macro I want to do over or get rid
of? I have set security low beause I trust my anto-virus. Will my users
have a security problem, running my macro?

=========================================================

Macros live in workbooks. So your macro (probably???) lives in that .xls
file
in the same folder as the .csv. But I don't have a real guess at where you
stored this macro--could it be in your personal.xl* workbook?

Personally, I don't like running this kind of macro by a shortcut or by
using
auto_open. I have to remember the shortcut and make sure that anyone who
runs
this when I'm not there (vacation???) can remember it, too.

And I wouldn't use auto_open. If I open the workbook for some other purpose
later in the day, I don't want to have it run automatically.

Instead, I just plop a button from the Forms toolbar onto a worksheet and
assign
the macro to that button. Add some instructions (for both me and other
users)
to a separate worksheet (or below the button) and I'm set.

This will mean that I don't have to check to see if the macro has been run
earlier in the day in my code.

But the choice is yours (obviously).

=========================================================

Dave,

I tried the macro solution - it works! I can read the entire 5005 row x
42
column file in about 3 seconds. Now I have confusion about the process.
Where is the macro stored? I created a folder with 2 files - the .CSV
file
to be read and the empty .XLS file with the name I want it to have. I
invoke it by pressing ctl-m. I'll change that to Autpen so it will run
when I open the .XLS file. When I move the folder with the 2 files to
another computer, the macro doesn't work. So the macro didn't come with
the
.XLS file.

I don't know what you mean about the "You could plop the date into a cell
in
a hidden sheet so that it only runs once. And add a save at the end".
Also, I want to delete a macro so I can re-record it, but it talks about
un-hiding something. What?

Thanks for your help.
Paul
=========================================================


the macro recorder will show you the very basic VBA code. you'll want to
tidy up
but its a great way to start

I tried it using Excel 2003 and it works there also. I'll try the
entire
process tomorrow (Monday). Wish me luck, and thanks.
Paul

=========================================================

In Excel 2007 you will need to be able to see the [Developer] tab. If
it
is
not visible now, click the Office Button, then the [Excel Options]
button
near the lower right of the window that opens. In the "Popular" group,
enable the "Show Developer tab in the ribbon" option.
To record a macro, choose the Developer tab and click the "Record
Macro" -
give it a name and procede with the steps you wish to record. The
"Record
Macro" option will have changed to "Stop Recording". Click it when you
have
finished recording the steps you need to repeat later.

=========================================================

How do I record a macro? Do you mean I can create a macro that will do
my
steps 1 thru 8, below?

=========================================================
It's too difficult to push a button?

If that's true, then name your macro Autpen. It'll run the first
time
someone opens the workbook. You could plop the date into a cell in a
hidden
sheet so that it only runs once. And add a save at the end.

If that doesn't work, good luck with the automation.

Paul H wrote:

=========================================================
I need the COBOL program to properly format the data. Multiple
end-users
will use this, sometimes daily, with data that changes daily, so I
have
been
requested to make it completely automatic. Another subsequent
program
shows
them the choices of reports and automatically starts the one they
select.

=========================================================

Maybe you could drop the requirement that the COBOL program do it.

Set up a workbook with two sheets (instructions for the user and the
actual
data).

Record a macro that does all the work in the second sheet.

The plop a button from the Forms toolbar onto the instruction sheet
that
calls
that macro.

You only have to rename the worksheet if you're doing File|Open (or
the
equivalent in code). If you use the import external data stuff, you
can
leave
it named .csv.

=========================================================

Paul H wrote:

Thanks Dave,

I used your "import text" method and it does succeed in importing
my
.CSV
file into my empty .XLS file, in about 2 seconds. That is, after I
manually:

1. Open empty XLS file.
2. Data, import external data, import data.
3. "Select Data Source" screen comes up.
4. Type in my xxxx.TXT file name.
5. Import wizard step 1 - select delimited, then next.
6. Import wizard step 2 - select only Comma, then finish.
7. Import data to existing worksheet.
8. It imports the entire file in about 2 seconds, formatted
correctly,
with
columns as described in my empty .XLS file.

Now I need to figure out how to accomplish these steps
automatically
by
my
COBOL program.

Thanks again,
Paul

=========================================================
If you rename the .csv file to .txt, you may be able to import the
file
quicker
by setting each field the way you want.

Then you could format the numeric fields as percentages or whatever
you
needed.

If you have fields that have implicit decimals, you could import
them
as
Generals and then put a factor of 10 (10, 100, 1000, ...) in an
empty
cell.
Edit|copy, edit|paste special|Values and divide (all in code) to
convert
that
field.
=========================================================
Paul H wrote:

I have an empty, formatted spreadsheet, that I created by writing a few
rows
into it from my COBOL program, field by field, then deleting all of the
rows.

The process of creating the entire XLS or XLSX runs much too slow (100
records per minute or less), and uses memory up, so can never allow me to
finish converting some of my large CSV files into formatted XLS (Excel
2003)
or XLSX (Excel 2007) files. Someone told me to "turn off continual
re-calculating". How?

I wonder if I can somehow import my large .CSV file into my .XLS empty
formatted file? I cannot find a way. Any help would be appreciated.

TIA, Paul

 

[Dave Peterson]

You can distribute the two (*.xls and *.csv) files, but that sounds kind of
weird to me.

If you only have a single *.csv file, then you should do the importing, save it
as a .xls file and distribute that.

If you have a *.csv file that's updated lots of times, then distribute the
single *.xls file and then redistribute (as often as it's updated) the *.csv
files.

If you put the procedure in its own module, you can remove the module. Debra
Dalgleish shows how:
http://contextures.com/xlfaqMac.html#NoMacros

If you used a single module and have lots of procedures, then you'll just select
the procedure that you want to remove and hit the delete key -- just like
deleting a word/phrase/paragraph in Word or clearing contents in an excel range.

Be careful, though. Make sure you have a backup (just in case). You can do
that by copying the .xls file to a safe location.

And yep, your users will have to let macros run when they open your workbook.

=========================================================

Will the macro stay with the .XLS file? Can I distribute just the 2 files -
the .CSV file to be read, and the empty .XLS file with the name I want it to
have?

And what do I un-hide so I can remove a macro I want to do over or get rid
of? I have set security low beause I trust my anto-virus. Will my users
have a security problem, running my macro?

=========================================================

Macros live in workbooks. So your macro (probably???) lives in that .xls
file
in the same folder as the .csv. But I don't have a real guess at where you
stored this macro--could it be in your personal.xl* workbook?

Personally, I don't like running this kind of macro by a shortcut or by
using
auto_open. I have to remember the shortcut and make sure that anyone who
runs
this when I'm not there (vacation???) can remember it, too.

And I wouldn't use auto_open. If I open the workbook for some other purpose
later in the day, I don't want to have it run automatically.

Instead, I just plop a button from the Forms toolbar onto a worksheet and
assign
the macro to that button. Add some instructions (for both me and other
users)
to a separate worksheet (or below the button) and I'm set.

This will mean that I don't have to check to see if the macro has been run
earlier in the day in my code.

But the choice is yours (obviously).

=========================================================

Dave,

I tried the macro solution - it works! I can read the entire 5005 row x
42
column file in about 3 seconds. Now I have confusion about the process.
Where is the macro stored? I created a folder with 2 files - the .CSV
file
to be read and the empty .XLS file with the name I want it to have. I
invoke it by pressing ctl-m. I'll change that to Autpen so it will run
when I open the .XLS file. When I move the folder with the 2 files to
another computer, the macro doesn't work. So the macro didn't come with
the
.XLS file.

I don't know what you mean about the "You could plop the date into a cell
in
a hidden sheet so that it only runs once. And add a save at the end".
Also, I want to delete a macro so I can re-record it, but it talks about
un-hiding something. What?

Thanks for your help.
Paul
=========================================================


the macro recorder will show you the very basic VBA code. you'll want to
tidy up
but its a great way to start

I tried it using Excel 2003 and it works there also. I'll try the
entire
process tomorrow (Monday). Wish me luck, and thanks.
Paul

=========================================================

In Excel 2007 you will need to be able to see the [Developer] tab. If
it
is
not visible now, click the Office Button, then the [Excel Options]
button
near the lower right of the window that opens. In the "Popular" group,
enable the "Show Developer tab in the ribbon" option.
To record a macro, choose the Developer tab and click the "Record
Macro" -
give it a name and procede with the steps you wish to record. The
"Record
Macro" option will have changed to "Stop Recording". Click it when you
have
finished recording the steps you need to repeat later.

:


=========================================================

How do I record a macro? Do you mean I can create a macro that will do
my
steps 1 thru 8, below?

=========================================================
It's too difficult to push a button?

If that's true, then name your macro Autpen. It'll run the first
time
someone opens the workbook. You could plop the date into a cell in a
hidden
sheet so that it only runs once. And add a save at the end.

If that doesn't work, good luck with the automation.

Paul H wrote:

=========================================================
I need the COBOL program to properly format the data. Multiple
end-users
will use this, sometimes daily, with data that changes daily, so I
have
been
requested to make it completely automatic. Another subsequent
program
shows
them the choices of reports and automatically starts the one they
select.

=========================================================

Maybe you could drop the requirement that the COBOL program do it.

Set up a workbook with two sheets (instructions for the user and the
actual
data).

Record a macro that does all the work in the second sheet.

The plop a button from the Forms toolbar onto the instruction sheet
that
calls
that macro.

You only have to rename the worksheet if you're doing File|Open (or
the
equivalent in code). If you use the import external data stuff, you
can
leave
it named .csv.

=========================================================

Paul H wrote:

Thanks Dave,

I used your "import text" method and it does succeed in importing
my
.CSV
file into my empty .XLS file, in about 2 seconds. That is, after I
manually:

1. Open empty XLS file.
2. Data, import external data, import data.
3. "Select Data Source" screen comes up.
4. Type in my xxxx.TXT file name.
5. Import wizard step 1 - select delimited, then next.
6. Import wizard step 2 - select only Comma, then finish.
7. Import data to existing worksheet.
8. It imports the entire file in about 2 seconds, formatted
correctly,
with
columns as described in my empty .XLS file.

Now I need to figure out how to accomplish these steps
automatically
by
my
COBOL program.

Thanks again,
Paul

=========================================================
If you rename the .csv file to .txt, you may be able to import the
file
quicker
by setting each field the way you want.

Then you could format the numeric fields as percentages or whatever
you
needed.

If you have fields that have implicit decimals, you could import
them
as
Generals and then put a factor of 10 (10, 100, 1000, ...) in an
empty
cell.
Edit|copy, edit|paste special|Values and divide (all in code) to
convert
that
field.
=========================================================
Paul H wrote:

I have an empty, formatted spreadsheet, that I created by writing a few
rows
into it from my COBOL program, field by field, then deleting all of the
rows.

The process of creating the entire XLS or XLSX runs much too slow (100
records per minute or less), and uses memory up, so can never allow me to
finish converting some of my large CSV files into formatted XLS (Excel
2003)
or XLSX (Excel 2007) files. Someone told me to "turn off continual
re-calculating". How?

I wonder if I can somehow import my large .CSV file into my .XLS empty
formatted file? I cannot find a way. Any help would be appreciated.

TIA, Paul

 

[Paul H]

=========================================================

Dave,
One user will be using these Excel reports.
Each having many or few rows and columns.
I will be developing 15 or 20 different reports for this user.
Some will be run multiple times per day as batches are processed.
Others may only be run once a week or once a month.
The .CSV files will be in the same folder.
The user is used to pointing to the correct file for other processes.
So, the process can not require my participation.
I like putting the "import" button at the left edge of the toolbar.
Please comment...
Thanks,
Paul

=========================================================

You can distribute the two (*.xls and *.csv) files, but that sounds kind of
weird to me.

If you only have a single *.csv file, then you should do the importing, save
it
as a .xls file and distribute that.

If you have a *.csv file that's updated lots of times, then distribute the
single *.xls file and then redistribute (as often as it's updated) the *.csv
files.

If you put the procedure in its own module, you can remove the module.
Debra
Dalgleish shows how:
http://contextures.com/xlfaqMac.html#NoMacros

If you used a single module and have lots of procedures, then you'll just
select
the procedure that you want to remove and hit the delete key -- just like
deleting a word/phrase/paragraph in Word or clearing contents in an excel
range.

Be careful, though. Make sure you have a backup (just in case). You can do
that by copying the .xls file to a safe location.

And yep, your users will have to let macros run when they open your
workbook.

=========================================================

Will the macro stay with the .XLS file? Can I distribute just the 2
files -
the .CSV file to be read, and the empty .XLS file with the name I want it
to
have?

And what do I un-hide so I can remove a macro I want to do over or get rid
of? I have set security low beause I trust my anto-virus. Will my users
have a security problem, running my macro?

=========================================================

Macros live in workbooks. So your macro (probably???) lives in that .xls
file
in the same folder as the .csv. But I don't have a real guess at where
you
stored this macro--could it be in your personal.xl* workbook?

Personally, I don't like running this kind of macro by a shortcut or by
using
auto_open. I have to remember the shortcut and make sure that anyone who
runs
this when I'm not there (vacation???) can remember it, too.

And I wouldn't use auto_open. If I open the workbook for some other
purpose
later in the day, I don't want to have it run automatically.

Instead, I just plop a button from the Forms toolbar onto a worksheet and
assign
the macro to that button. Add some instructions (for both me and other
users)
to a separate worksheet (or below the button) and I'm set.

This will mean that I don't have to check to see if the macro has been run
earlier in the day in my code.

But the choice is yours (obviously).

=========================================================

Dave,

I tried the macro solution - it works! I can read the entire 5005 row x
42
column file in about 3 seconds. Now I have confusion about the process.
Where is the macro stored? I created a folder with 2 files - the .CSV
file
to be read and the empty .XLS file with the name I want it to have. I
invoke it by pressing ctl-m. I'll change that to Autpen so it will
run
when I open the .XLS file. When I move the folder with the 2 files to
another computer, the macro doesn't work. So the macro didn't come with
the
.XLS file.

I don't know what you mean about the "You could plop the date into a
cell
in
a hidden sheet so that it only runs once. And add a save at the end".
Also, I want to delete a macro so I can re-record it, but it talks about
un-hiding something. What?

Thanks for your help.
Paul
=========================================================


the macro recorder will show you the very basic VBA code. you'll want to
tidy up
but its a great way to start

I tried it using Excel 2003 and it works there also. I'll try the
entire
process tomorrow (Monday). Wish me luck, and thanks.
Paul

=========================================================

In Excel 2007 you will need to be able to see the [Developer] tab. If
it
is
not visible now, click the Office Button, then the [Excel Options]
button
near the lower right of the window that opens. In the "Popular"
group,
enable the "Show Developer tab in the ribbon" option.
To record a macro, choose the Developer tab and click the "Record
Macro" -
give it a name and procede with the steps you wish to record. The
"Record
Macro" option will have changed to "Stop Recording". Click it when
you
have
finished recording the steps you need to repeat later.

:


=========================================================

How do I record a macro? Do you mean I can create a macro that will
do
my
steps 1 thru 8, below?

=========================================================
It's too difficult to push a button?

If that's true, then name your macro Autpen. It'll run the first
time
someone opens the workbook. You could plop the date into a cell in a
hidden
sheet so that it only runs once. And add a save at the end.

If that doesn't work, good luck with the automation.

Paul H wrote:

=========================================================
I need the COBOL program to properly format the data. Multiple
end-users
will use this, sometimes daily, with data that changes daily, so I
have
been
requested to make it completely automatic. Another subsequent
program
shows
them the choices of reports and automatically starts the one they
select.

=========================================================

Maybe you could drop the requirement that the COBOL program do it.

Set up a workbook with two sheets (instructions for the user and
the
actual
data).

Record a macro that does all the work in the second sheet.

The plop a button from the Forms toolbar onto the instruction sheet
that
calls
that macro.

You only have to rename the worksheet if you're doing File|Open (or
the
equivalent in code). If you use the import external data stuff,
you
can
leave
it named .csv.

=========================================================

Paul H wrote:

Thanks Dave,

I used your "import text" method and it does succeed in importing
my
.CSV
file into my empty .XLS file, in about 2 seconds. That is, after
I
manually:

1. Open empty XLS file.
2. Data, import external data, import data.
3. "Select Data Source" screen comes up.
4. Type in my xxxx.TXT file name.
5. Import wizard step 1 - select delimited, then next.
6. Import wizard step 2 - select only Comma, then finish.
7. Import data to existing worksheet.
8. It imports the entire file in about 2 seconds, formatted
correctly,
with
columns as described in my empty .XLS file.

Now I need to figure out how to accomplish these steps
automatically
by
my
COBOL program.

Thanks again,
Paul

=========================================================
If you rename the .csv file to .txt, you may be able to import
the
file
quicker
by setting each field the way you want.

Then you could format the numeric fields as percentages or
whatever
you
needed.

If you have fields that have implicit decimals, you could import
them
as
Generals and then put a factor of 10 (10, 100, 1000, ...) in an
empty
cell.
Edit|copy, edit|paste special|Values and divide (all in code) to
convert
that
field.
=========================================================
Paul H wrote:

I have an empty, formatted spreadsheet, that I created by writing a few
rows
into it from my COBOL program, field by field, then deleting all of the
rows.

The process of creating the entire XLS or XLSX runs much too slow (100
records per minute or less), and uses memory up, so can never allow me
to
finish converting some of my large CSV files into formatted XLS (Excel
2003)
or XLSX (Excel 2007) files. Someone told me to "turn off continual
re-calculating". How?

I wonder if I can somehow import my large .CSV file into my .XLS empty
formatted file? I cannot find a way. Any help would be appreciated.

TIA, Paul

 

[Dave Peterson]

You could make a toolbar that has the 15-20 different macros on it. But I think
I'd use multiple buttons from the Forms toolbar placed directly on a worksheet.

And if some of those reports are always run at the same time as others, then I'd
have one button that ran those reports.

Caption one of the buttons "Click me to run reports 1-6"

And assign a "combined" macro to that button:

Option Explicit
Sub DoReports_1_6()
call DoReport1
call DoReport2
call DoReport3
call DoReport4
call DoReport5
call DoReport6
End Sub

Where those doReport# procedures are in that same workbook's project.

====
By using buttons from the Forms toolbar placed directly on the worksheet, you
can have as many notes as you like near that button.

=========================================================

Dave,
One user will be using these Excel reports.
Each having many or few rows and columns.
I will be developing 15 or 20 different reports for this user.
Some will be run multiple times per day as batches are processed.
Others may only be run once a week or once a month.
The .CSV files will be in the same folder.
The user is used to pointing to the correct file for other processes.
So, the process can not require my participation.
I like putting the "import" button at the left edge of the toolbar.
Please comment...
Thanks,
Paul

=========================================================

You can distribute the two (*.xls and *.csv) files, but that sounds kind of
weird to me.

If you only have a single *.csv file, then you should do the importing, save
it
as a .xls file and distribute that.

If you have a *.csv file that's updated lots of times, then distribute the
single *.xls file and then redistribute (as often as it's updated) the *.csv
files.

If you put the procedure in its own module, you can remove the module.
Debra
Dalgleish shows how:
http://contextures.com/xlfaqMac.html#NoMacros

If you used a single module and have lots of procedures, then you'll just
select
the procedure that you want to remove and hit the delete key -- just like
deleting a word/phrase/paragraph in Word or clearing contents in an excel
range.

Be careful, though. Make sure you have a backup (just in case). You can do
that by copying the .xls file to a safe location.

And yep, your users will have to let macros run when they open your
workbook.

=========================================================

Will the macro stay with the .XLS file? Can I distribute just the 2
files -
the .CSV file to be read, and the empty .XLS file with the name I want it
to
have?

And what do I un-hide so I can remove a macro I want to do over or get rid
of? I have set security low beause I trust my anto-virus. Will my users
have a security problem, running my macro?

=========================================================

Macros live in workbooks. So your macro (probably???) lives in that .xls
file
in the same folder as the .csv. But I don't have a real guess at where
you
stored this macro--could it be in your personal.xl* workbook?

Personally, I don't like running this kind of macro by a shortcut or by
using
auto_open. I have to remember the shortcut and make sure that anyone who
runs
this when I'm not there (vacation???) can remember it, too.

And I wouldn't use auto_open. If I open the workbook for some other
purpose
later in the day, I don't want to have it run automatically.

Instead, I just plop a button from the Forms toolbar onto a worksheet and
assign
the macro to that button. Add some instructions (for both me and other
users)
to a separate worksheet (or below the button) and I'm set.

This will mean that I don't have to check to see if the macro has been run
earlier in the day in my code.

But the choice is yours (obviously).

=========================================================

Dave,

I tried the macro solution - it works! I can read the entire 5005 row x
42
column file in about 3 seconds. Now I have confusion about the process.
Where is the macro stored? I created a folder with 2 files - the .CSV
file
to be read and the empty .XLS file with the name I want it to have. I
invoke it by pressing ctl-m. I'll change that to Autpen so it will
run
when I open the .XLS file. When I move the folder with the 2 files to
another computer, the macro doesn't work. So the macro didn't come with
the
.XLS file.

I don't know what you mean about the "You could plop the date into a
cell
in
a hidden sheet so that it only runs once. And add a save at the end".
Also, I want to delete a macro so I can re-record it, but it talks about
un-hiding something. What?

Thanks for your help.
Paul
=========================================================


the macro recorder will show you the very basic VBA code. you'll want to
tidy up
but its a great way to start


I tried it using Excel 2003 and it works there also. I'll try the
entire
process tomorrow (Monday). Wish me luck, and thanks.
Paul

=========================================================

In Excel 2007 you will need to be able to see the [Developer] tab. If
it
is
not visible now, click the Office Button, then the [Excel Options]
button
near the lower right of the window that opens. In the "Popular"
group,
enable the "Show Developer tab in the ribbon" option.
To record a macro, choose the Developer tab and click the "Record
Macro" -
give it a name and procede with the steps you wish to record. The
"Record
Macro" option will have changed to "Stop Recording". Click it when
you
have
finished recording the steps you need to repeat later.

:


=========================================================

How do I record a macro? Do you mean I can create a macro that will
do
my
steps 1 thru 8, below?

=========================================================
It's too difficult to push a button?

If that's true, then name your macro Autpen. It'll run the first
time
someone opens the workbook. You could plop the date into a cell in a
hidden
sheet so that it only runs once. And add a save at the end.

If that doesn't work, good luck with the automation.

Paul H wrote:

=========================================================
I need the COBOL program to properly format the data. Multiple
end-users
will use this, sometimes daily, with data that changes daily, so I
have
been
requested to make it completely automatic. Another subsequent
program
shows
them the choices of reports and automatically starts the one they
select.

=========================================================

Maybe you could drop the requirement that the COBOL program do it.

Set up a workbook with two sheets (instructions for the user and
the
actual
data).

Record a macro that does all the work in the second sheet.

The plop a button from the Forms toolbar onto the instruction sheet
that
calls
that macro.

You only have to rename the worksheet if you're doing File|Open (or
the
equivalent in code). If you use the import external data stuff,
you
can
leave
it named .csv.

=========================================================

Paul H wrote:

Thanks Dave,

I used your "import text" method and it does succeed in importing
my
.CSV
file into my empty .XLS file, in about 2 seconds. That is, after
I
manually:

1. Open empty XLS file.
2. Data, import external data, import data.
3. "Select Data Source" screen comes up.
4. Type in my xxxx.TXT file name.
5. Import wizard step 1 - select delimited, then next.
6. Import wizard step 2 - select only Comma, then finish.
7. Import data to existing worksheet.
8. It imports the entire file in about 2 seconds, formatted
correctly,
with
columns as described in my empty .XLS file.

Now I need to figure out how to accomplish these steps
automatically
by
my
COBOL program.

Thanks again,
Paul

=========================================================
If you rename the .csv file to .txt, you may be able to import
the
file
quicker
by setting each field the way you want.

Then you could format the numeric fields as percentages or
whatever
you
needed.

If you have fields that have implicit decimals, you could import
them
as
Generals and then put a factor of 10 (10, 100, 1000, ...) in an
empty
cell.
Edit|copy, edit|paste special|Values and divide (all in code) to
convert
that
field.
=========================================================
Paul H wrote:

I have an empty, formatted spreadsheet, that I created by writing a few
rows
into it from my COBOL program, field by field, then deleting all of the
rows.

The process of creating the entire XLS or XLSX runs much too slow (100
records per minute or less), and uses memory up, so can never allow me
to
finish converting some of my large CSV files into formatted XLS (Excel
2003)
or XLSX (Excel 2007) files. Someone told me to "turn off continual
re-calculating". How?

I wonder if I can somehow import my large .CSV file into my .XLS empty
formatted file? I cannot find a way. Any help would be appreciated.

TIA, Paul

 

[Paul H]

=========================================================
I prefer to have each report stand alone. All the .CSV files for one report
will be in one folder. Other reports will have their own folders. So for
each of the reports, I need to have the macro let the user browse the folder
for his preferred file. Is this possible? Each spreadsheet will only need
one "import" button on the left end of the toolbar. Can each .XLS have it's
own macro embedded it? Or must the user have one workbook project, that
contains the macros? This will be harder for me to maintain than if they
stand alone.
=========================================================

You could make a toolbar that has the 15-20 different macros on it. But I
think
I'd use multiple buttons from the Forms toolbar placed directly on a
worksheet.

And if some of those reports are always run at the same time as others, then
I'd
have one button that ran those reports.

Caption one of the buttons "Click me to run reports 1-6"

And assign a "combined" macro to that button:

Option Explicit
Sub DoReports_1_6()
call DoReport1
call DoReport2
call DoReport3
call DoReport4
call DoReport5
call DoReport6
End Sub

Where those doReport# procedures are in that same workbook's project.

====
By using buttons from the Forms toolbar placed directly on the worksheet,
you
can have as many notes as you like near that button.

=========================================================

Dave,
One user will be using these Excel reports.
Each having many or few rows and columns.
I will be developing 15 or 20 different reports for this user.
Some will be run multiple times per day as batches are processed.
Others may only be run once a week or once a month.
The .CSV files will be in the same folder.
The user is used to pointing to the correct file for other processes.
So, the process can not require my participation.
I like putting the "import" button at the left edge of the toolbar.
Please comment...
Thanks,
Paul

=========================================================

You can distribute the two (*.xls and *.csv) files, but that sounds kind
of
weird to me.

If you only have a single *.csv file, then you should do the importing,
save
it
as a .xls file and distribute that.

If you have a *.csv file that's updated lots of times, then distribute the
single *.xls file and then redistribute (as often as it's updated) the
*.csv
files.

If you put the procedure in its own module, you can remove the module.
Debra
Dalgleish shows how:
http://contextures.com/xlfaqMac.html#NoMacros

If you used a single module and have lots of procedures, then you'll just
select
the procedure that you want to remove and hit the delete key -- just like
deleting a word/phrase/paragraph in Word or clearing contents in an excel
range.

Be careful, though. Make sure you have a backup (just in case). You can
do
that by copying the .xls file to a safe location.

And yep, your users will have to let macros run when they open your
workbook.

=========================================================

Will the macro stay with the .XLS file? Can I distribute just the 2
files -
the .CSV file to be read, and the empty .XLS file with the name I want
it
to
have?

And what do I un-hide so I can remove a macro I want to do over or get
rid
of? I have set security low beause I trust my anto-virus. Will my
users
have a security problem, running my macro?

=========================================================

Macros live in workbooks. So your macro (probably???) lives in that
.xls
file
in the same folder as the .csv. But I don't have a real guess at where
you
stored this macro--could it be in your personal.xl* workbook?

Personally, I don't like running this kind of macro by a shortcut or by
using
auto_open. I have to remember the shortcut and make sure that anyone
who
runs
this when I'm not there (vacation???) can remember it, too.

And I wouldn't use auto_open. If I open the workbook for some other
purpose
later in the day, I don't want to have it run automatically.

Instead, I just plop a button from the Forms toolbar onto a worksheet
and
assign
the macro to that button. Add some instructions (for both me and other
users)
to a separate worksheet (or below the button) and I'm set.

This will mean that I don't have to check to see if the macro has been
run
earlier in the day in my code.

But the choice is yours (obviously).

=========================================================

Dave,

I tried the macro solution - it works! I can read the entire 5005 row
x
42
column file in about 3 seconds. Now I have confusion about the
process.
Where is the macro stored? I created a folder with 2 files - the .CSV
file
to be read and the empty .XLS file with the name I want it to have. I
invoke it by pressing ctl-m. I'll change that to Autpen so it will
run
when I open the .XLS file. When I move the folder with the 2 files to
another computer, the macro doesn't work. So the macro didn't come
with
the
.XLS file.

I don't know what you mean about the "You could plop the date into a
cell
in
a hidden sheet so that it only runs once. And add a save at the end".
Also, I want to delete a macro so I can re-record it, but it talks
about
un-hiding something. What?

Thanks for your help.
Paul
=========================================================


the macro recorder will show you the very basic VBA code. you'll want
to
tidy up
but its a great way to start


I tried it using Excel 2003 and it works there also. I'll try the
entire
process tomorrow (Monday). Wish me luck, and thanks.
Paul

=========================================================

In Excel 2007 you will need to be able to see the [Developer] tab.
If
it
is
not visible now, click the Office Button, then the [Excel Options]
button
near the lower right of the window that opens. In the "Popular"
group,
enable the "Show Developer tab in the ribbon" option.
To record a macro, choose the Developer tab and click the "Record
Macro" -
give it a name and procede with the steps you wish to record. The
"Record
Macro" option will have changed to "Stop Recording". Click it when
you
have
finished recording the steps you need to repeat later.

:


=========================================================

How do I record a macro? Do you mean I can create a macro that
will
do
my
steps 1 thru 8, below?

=========================================================
It's too difficult to push a button?

If that's true, then name your macro Autpen. It'll run the
first
time
someone opens the workbook. You could plop the date into a cell in
a
hidden
sheet so that it only runs once. And add a save at the end.

If that doesn't work, good luck with the automation.

Paul H wrote:

=========================================================
I need the COBOL program to properly format the data. Multiple
end-users
will use this, sometimes daily, with data that changes daily, so
I
have
been
requested to make it completely automatic. Another subsequent
program
shows
them the choices of reports and automatically starts the one they
select.

=========================================================

Maybe you could drop the requirement that the COBOL program do
it.

Set up a workbook with two sheets (instructions for the user and
the
actual
data).

Record a macro that does all the work in the second sheet.

The plop a button from the Forms toolbar onto the instruction
sheet
that
calls
that macro.

You only have to rename the worksheet if you're doing File|Open
(or
the
equivalent in code). If you use the import external data stuff,
you
can
leave
it named .csv.

=========================================================

Paul H wrote:

Thanks Dave,

I used your "import text" method and it does succeed in
importing
my
.CSV
file into my empty .XLS file, in about 2 seconds. That is,
after
I
manually:

1. Open empty XLS file.
2. Data, import external data, import data.
3. "Select Data Source" screen comes up.
4. Type in my xxxx.TXT file name.
5. Import wizard step 1 - select delimited, then next.
6. Import wizard step 2 - select only Comma, then finish.
7. Import data to existing worksheet.
8. It imports the entire file in about 2 seconds, formatted
correctly,
with
columns as described in my empty .XLS file.

Now I need to figure out how to accomplish these steps
automatically
by
my
COBOL program.

Thanks again,
Paul

=========================================================
If you rename the .csv file to .txt, you may be able to import
the
file
quicker
by setting each field the way you want.

Then you could format the numeric fields as percentages or
whatever
you
needed.

If you have fields that have implicit decimals, you could
import
them
as
Generals and then put a factor of 10 (10, 100, 1000, ...) in an
empty
cell.
Edit|copy, edit|paste special|Values and divide (all in code)
to
convert
that
field.
=========================================================
Paul H wrote:

I have an empty, formatted spreadsheet, that I created by writing a
few
rows
into it from my COBOL program, field by field, then deleting all of
the
rows.

The process of creating the entire XLS or XLSX runs much too slow (100
records per minute or less), and uses memory up, so can never allow me
to
finish converting some of my large CSV files into formatted XLS (Excel
2003)
or XLSX (Excel 2007) files. Someone told me to "turn off continual
re-calculating". How?

I wonder if I can somehow import my large .CSV file into my .XLS empty
formatted file? I cannot find a way. Any help would be appreciated.

TIA, Paul

 

[Dave Peterson]

You can use as many workbooks as you want.

You can have 20 different workbooks or a single workbook. Or even a few--where
you put the macros that generate similar reports in those few workbooks.

You could use something like this to ask for the filename:

tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim Wkbk as workbook

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

set wkbk = Workbooks.Open(Filename:=Filename:=myFileName)

'....rest of recorded code here!

End Sub

=========================================================
I prefer to have each report stand alone. All the .CSV files for one report
will be in one folder. Other reports will have their own folders. So for
each of the reports, I need to have the macro let the user browse the folder
for his preferred file. Is this possible? Each spreadsheet will only need
one "import" button on the left end of the toolbar. Can each .XLS have it's
own macro embedded it? Or must the user have one workbook project, that
contains the macros? This will be harder for me to maintain than if they
stand alone.
=========================================================

You could make a toolbar that has the 15-20 different macros on it. But I
think
I'd use multiple buttons from the Forms toolbar placed directly on a
worksheet.

And if some of those reports are always run at the same time as others, then
I'd
have one button that ran those reports.

Caption one of the buttons "Click me to run reports 1-6"

And assign a "combined" macro to that button:

Option Explicit
Sub DoReports_1_6()
call DoReport1
call DoReport2
call DoReport3
call DoReport4
call DoReport5
call DoReport6
End Sub

Where those doReport# procedures are in that same workbook's project.

====
By using buttons from the Forms toolbar placed directly on the worksheet,
you
can have as many notes as you like near that button.

=========================================================

Dave,
One user will be using these Excel reports.
Each having many or few rows and columns.
I will be developing 15 or 20 different reports for this user.
Some will be run multiple times per day as batches are processed.
Others may only be run once a week or once a month.
The .CSV files will be in the same folder.
The user is used to pointing to the correct file for other processes.
So, the process can not require my participation.
I like putting the "import" button at the left edge of the toolbar.
Please comment...
Thanks,
Paul

=========================================================

You can distribute the two (*.xls and *.csv) files, but that sounds kind
of
weird to me.

If you only have a single *.csv file, then you should do the importing,
save
it
as a .xls file and distribute that.

If you have a *.csv file that's updated lots of times, then distribute the
single *.xls file and then redistribute (as often as it's updated) the
*.csv
files.

If you put the procedure in its own module, you can remove the module.
Debra
Dalgleish shows how:
http://contextures.com/xlfaqMac.html#NoMacros

If you used a single module and have lots of procedures, then you'll just
select
the procedure that you want to remove and hit the delete key -- just like
deleting a word/phrase/paragraph in Word or clearing contents in an excel
range.

Be careful, though. Make sure you have a backup (just in case). You can
do
that by copying the .xls file to a safe location.

And yep, your users will have to let macros run when they open your
workbook.

=========================================================

Will the macro stay with the .XLS file? Can I distribute just the 2
files -
the .CSV file to be read, and the empty .XLS file with the name I want
it
to
have?

And what do I un-hide so I can remove a macro I want to do over or get
rid
of? I have set security low beause I trust my anto-virus. Will my
users
have a security problem, running my macro?

=========================================================

Macros live in workbooks. So your macro (probably???) lives in that
.xls
file
in the same folder as the .csv. But I don't have a real guess at where
you
stored this macro--could it be in your personal.xl* workbook?

Personally, I don't like running this kind of macro by a shortcut or by
using
auto_open. I have to remember the shortcut and make sure that anyone
who
runs
this when I'm not there (vacation???) can remember it, too.

And I wouldn't use auto_open. If I open the workbook for some other
purpose
later in the day, I don't want to have it run automatically.

Instead, I just plop a button from the Forms toolbar onto a worksheet
and
assign
the macro to that button. Add some instructions (for both me and other
users)
to a separate worksheet (or below the button) and I'm set.

This will mean that I don't have to check to see if the macro has been
run
earlier in the day in my code.

But the choice is yours (obviously).

=========================================================

Paul H wrote:

Dave,

I tried the macro solution - it works! I can read the entire 5005 row
x
42
column file in about 3 seconds. Now I have confusion about the
process.
Where is the macro stored? I created a folder with 2 files - the .CSV
file
to be read and the empty .XLS file with the name I want it to have. I
invoke it by pressing ctl-m. I'll change that to Autpen so it will
run
when I open the .XLS file. When I move the folder with the 2 files to
another computer, the macro doesn't work. So the macro didn't come
with
the
.XLS file.

I don't know what you mean about the "You could plop the date into a
cell
in
a hidden sheet so that it only runs once. And add a save at the end".
Also, I want to delete a macro so I can re-record it, but it talks
about
un-hiding something. What?

Thanks for your help.
Paul
=========================================================


the macro recorder will show you the very basic VBA code. you'll want
to
tidy up
but its a great way to start


I tried it using Excel 2003 and it works there also. I'll try the
entire
process tomorrow (Monday). Wish me luck, and thanks.
Paul

=========================================================

In Excel 2007 you will need to be able to see the [Developer] tab.
If
it
is
not visible now, click the Office Button, then the [Excel Options]
button
near the lower right of the window that opens. In the "Popular"
group,
enable the "Show Developer tab in the ribbon" option.
To record a macro, choose the Developer tab and click the "Record
Macro" -
give it a name and procede with the steps you wish to record. The
"Record
Macro" option will have changed to "Stop Recording". Click it when
you
have
finished recording the steps you need to repeat later.

:


=========================================================

How do I record a macro? Do you mean I can create a macro that
will
do
my
steps 1 thru 8, below?

=========================================================
It's too difficult to push a button?

If that's true, then name your macro Autpen. It'll run the
first
time
someone opens the workbook. You could plop the date into a cell in
a
hidden
sheet so that it only runs once. And add a save at the end.

If that doesn't work, good luck with the automation.

Paul H wrote:

=========================================================
I need the COBOL program to properly format the data. Multiple
end-users
will use this, sometimes daily, with data that changes daily, so
I
have
been
requested to make it completely automatic. Another subsequent
program
shows
them the choices of reports and automatically starts the one they
select.

=========================================================

Maybe you could drop the requirement that the COBOL program do
it.

Set up a workbook with two sheets (instructions for the user and
the
actual
data).

Record a macro that does all the work in the second sheet.

The plop a button from the Forms toolbar onto the instruction
sheet
that
calls
that macro.

You only have to rename the worksheet if you're doing File|Open
(or
the
equivalent in code). If you use the import external data stuff,
you
can
leave
it named .csv.

=========================================================

Paul H wrote:

Thanks Dave,

I used your "import text" method and it does succeed in
importing
my
.CSV
file into my empty .XLS file, in about 2 seconds. That is,
after
I
manually:

1. Open empty XLS file.
2. Data, import external data, import data.
3. "Select Data Source" screen comes up.
4. Type in my xxxx.TXT file name.
5. Import wizard step 1 - select delimited, then next.
6. Import wizard step 2 - select only Comma, then finish.
7. Import data to existing worksheet.
8. It imports the entire file in about 2 seconds, formatted
correctly,
with
columns as described in my empty .XLS file.

Now I need to figure out how to accomplish these steps
automatically
by
my
COBOL program.

Thanks again,
Paul

=========================================================
If you rename the .csv file to .txt, you may be able to import
the
file
quicker
by setting each field the way you want.

Then you could format the numeric fields as percentages or
whatever
you
needed.

If you have fields that have implicit decimals, you could
import
them
as
Generals and then put a factor of 10 (10, 100, 1000, ...) in an
empty
cell.
Edit|copy, edit|paste special|Values and divide (all in code)
to
convert
that
field.
=========================================================
Paul H wrote:

I have an empty, formatted spreadsheet, that I created by writing a
few
rows
into it from my COBOL program, field by field, then deleting all of
the
rows.

The process of creating the entire XLS or XLSX runs much too slow (100
records per minute or less), and uses memory up, so can never allow me
to
finish converting some of my large CSV files into formatted XLS (Excel
2003)
or XLSX (Excel 2007) files. Someone told me to "turn off continual
re-calculating". How?

I wonder if I can somehow import my large .CSV file into my .XLS empty
formatted file? I cannot find a way. Any help would be appreciated.

TIA, Paul

 

[Paul H]

=========================================================
Dave,
I have no idea what I'm doing - is there book on this subject. I clicked
"step into" to get into the VB editor process, then pasted your stuff in.
Should your stuff go 1st?
Paul


Sub Loader1()
'
' Loader1 Macro
' Macro recorded 6/30/2009 by Paul Hoberg
'
' Keyboard Shortcut: Ctrl+m
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\aaa\CreditData-021809dater.csv", Destination:=Range("A1"))
.Name = "CreditData-021809dater"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim Wkbk As Workbook

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV",
_
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

set wkbk = Workbooks.Open(Filename:=Filename:=myFileName)

Range("A1").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
Range("A1:AO1").Select
Selection.Font.Bold = True
End Sub


=========================================================

You can use as many workbooks as you want.

You can have 20 different workbooks or a single workbook. Or even a
few--where
you put the macros that generate similar reports in those few workbooks.

You could use something like this to ask for the filename:

tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim Wkbk as workbook

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV",
_
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

set wkbk = Workbooks.Open(Filename:=Filename:=myFileName)

'....rest of recorded code here!

End Sub

=========================================================
I prefer to have each report stand alone. All the .CSV files for one
report
will be in one folder. Other reports will have their own folders. So for
each of the reports, I need to have the macro let the user browse the
folder
for his preferred file. Is this possible? Each spreadsheet will only
need
one "import" button on the left end of the toolbar. Can each .XLS have
it's
own macro embedded it? Or must the user have one workbook project, that
contains the macros? This will be harder for me to maintain than if they
stand alone.
=========================================================

You could make a toolbar that has the 15-20 different macros on it. But I
think
I'd use multiple buttons from the Forms toolbar placed directly on a
worksheet.

And if some of those reports are always run at the same time as others,
then
I'd
have one button that ran those reports.

Caption one of the buttons "Click me to run reports 1-6"

And assign a "combined" macro to that button:

Option Explicit
Sub DoReports_1_6()
call DoReport1
call DoReport2
call DoReport3
call DoReport4
call DoReport5
call DoReport6
End Sub

Where those doReport# procedures are in that same workbook's project.

====
By using buttons from the Forms toolbar placed directly on the worksheet,
you
can have as many notes as you like near that button.

=========================================================

Dave,
One user will be using these Excel reports.
Each having many or few rows and columns.
I will be developing 15 or 20 different reports for this user.
Some will be run multiple times per day as batches are processed.
Others may only be run once a week or once a month.
The .CSV files will be in the same folder.
The user is used to pointing to the correct file for other processes.
So, the process can not require my participation.
I like putting the "import" button at the left edge of the toolbar.
Please comment...
Thanks,
Paul

=========================================================

You can distribute the two (*.xls and *.csv) files, but that sounds kind
of
weird to me.

If you only have a single *.csv file, then you should do the importing,
save
it
as a .xls file and distribute that.

If you have a *.csv file that's updated lots of times, then distribute
the
single *.xls file and then redistribute (as often as it's updated) the
*.csv
files.

If you put the procedure in its own module, you can remove the module.
Debra
Dalgleish shows how:
http://contextures.com/xlfaqMac.html#NoMacros

If you used a single module and have lots of procedures, then you'll
just
select
the procedure that you want to remove and hit the delete key -- just
like
deleting a word/phrase/paragraph in Word or clearing contents in an
excel
range.

Be careful, though. Make sure you have a backup (just in case). You
can
do
that by copying the .xls file to a safe location.

And yep, your users will have to let macros run when they open your
workbook.

=========================================================

Will the macro stay with the .XLS file? Can I distribute just the 2
files -
the .CSV file to be read, and the empty .XLS file with the name I want
it
to
have?

And what do I un-hide so I can remove a macro I want to do over or get
rid
of? I have set security low beause I trust my anto-virus. Will my
users
have a security problem, running my macro?

=========================================================

Macros live in workbooks. So your macro (probably???) lives in that
.xls
file
in the same folder as the .csv. But I don't have a real guess at
where
you
stored this macro--could it be in your personal.xl* workbook?

Personally, I don't like running this kind of macro by a shortcut or
by
using
auto_open. I have to remember the shortcut and make sure that anyone
who
runs
this when I'm not there (vacation???) can remember it, too.

And I wouldn't use auto_open. If I open the workbook for some other
purpose
later in the day, I don't want to have it run automatically.

Instead, I just plop a button from the Forms toolbar onto a worksheet
and
assign
the macro to that button. Add some instructions (for both me and
other
users)
to a separate worksheet (or below the button) and I'm set.

This will mean that I don't have to check to see if the macro has been
run
earlier in the day in my code.

But the choice is yours (obviously).

=========================================================

Paul H wrote:

Dave,

I tried the macro solution - it works! I can read the entire 5005
row
x
42
column file in about 3 seconds. Now I have confusion about the
process.
Where is the macro stored? I created a folder with 2 files - the
.CSV
file
to be read and the empty .XLS file with the name I want it to have.
I
invoke it by pressing ctl-m. I'll change that to Autpen so it
will
run
when I open the .XLS file. When I move the folder with the 2 files
to
another computer, the macro doesn't work. So the macro didn't come
with
the
.XLS file.

I don't know what you mean about the "You could plop the date into a
cell
in
a hidden sheet so that it only runs once. And add a save at the
end".
Also, I want to delete a macro so I can re-record it, but it talks
about
un-hiding something. What?

Thanks for your help.
Paul
=========================================================


the macro recorder will show you the very basic VBA code. you'll
want
to
tidy up
but its a great way to start


I tried it using Excel 2003 and it works there also. I'll try the
entire
process tomorrow (Monday). Wish me luck, and thanks.
Paul

=========================================================

In Excel 2007 you will need to be able to see the [Developer] tab.
If
it
is
not visible now, click the Office Button, then the [Excel Options]
button
near the lower right of the window that opens. In the "Popular"
group,
enable the "Show Developer tab in the ribbon" option.
To record a macro, choose the Developer tab and click the "Record
Macro" -
give it a name and procede with the steps you wish to record. The
"Record
Macro" option will have changed to "Stop Recording". Click it
when
you
have
finished recording the steps you need to repeat later.

:


=========================================================

How do I record a macro? Do you mean I can create a macro that
will
do
my
steps 1 thru 8, below?

=========================================================
It's too difficult to push a button?

If that's true, then name your macro Autpen. It'll run the
first
time
someone opens the workbook. You could plop the date into a cell
in
a
hidden
sheet so that it only runs once. And add a save at the end.

If that doesn't work, good luck with the automation.

Paul H wrote:

=========================================================
I need the COBOL program to properly format the data. Multiple
end-users
will use this, sometimes daily, with data that changes daily,
so
I
have
been
requested to make it completely automatic. Another subsequent
program
shows
them the choices of reports and automatically starts the one
they
select.

=========================================================

Maybe you could drop the requirement that the COBOL program do
it.

Set up a workbook with two sheets (instructions for the user
and
the
actual
data).

Record a macro that does all the work in the second sheet.

The plop a button from the Forms toolbar onto the instruction
sheet
that
calls
that macro.

You only have to rename the worksheet if you're doing File|Open
(or
the
equivalent in code). If you use the import external data
stuff,
you
can
leave
it named .csv.

=========================================================

Paul H wrote:

Thanks Dave,

I used your "import text" method and it does succeed in
importing
my
.CSV
file into my empty .XLS file, in about 2 seconds. That is,
after
I
manually:

1. Open empty XLS file.
2. Data, import external data, import data.
3. "Select Data Source" screen comes up.
4. Type in my xxxx.TXT file name.
5. Import wizard step 1 - select delimited, then next.
6. Import wizard step 2 - select only Comma, then finish.
7. Import data to existing worksheet.
8. It imports the entire file in about 2 seconds, formatted
correctly,
with
columns as described in my empty .XLS file.

Now I need to figure out how to accomplish these steps
automatically
by
my
COBOL program.

Thanks again,
Paul

=========================================================
If you rename the .csv file to .txt, you may be able to
import
the
file
quicker
by setting each field the way you want.

Then you could format the numeric fields as percentages or
whatever
you
needed.

If you have fields that have implicit decimals, you could
import
them
as
Generals and then put a factor of 10 (10, 100, 1000, ...) in
an
empty
cell.
Edit|copy, edit|paste special|Values and divide (all in code)
to
convert
that
field.
=========================================================
Paul H wrote:

I have an empty, formatted spreadsheet, that I created by writing a
few
rows
into it from my COBOL program, field by field, then deleting all of
the
rows.

The process of creating the entire XLS or XLSX runs much too slow
(100
records per minute or less), and uses memory up, so can never allow
me
to
finish converting some of my large CSV files into formatted XLS
(Excel
2003)
or XLSX (Excel 2007) files. Someone told me to "turn off continual
re-calculating". How?

I wonder if I can somehow import my large .CSV file into my .XLS
empty
formatted file? I cannot find a way. Any help would be
appreciated.

TIA, Paul

 

[Dave Peterson]

Untested:

Option Explicit
Sub Loader1()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & myFileName, Destination:=Range("A1"))
.Name = "CreditData-021809dater"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Range("A1:AO1").Font.Bold = True
End Sub

=========================================================
Dave,
I have no idea what I'm doing - is there book on this subject. I clicked
"step into" to get into the VB editor process, then pasted your stuff in.
Should your stuff go 1st?
Paul

Sub Loader1()
'
' Loader1 Macro
' Macro recorded 6/30/2009 by Paul Hoberg
'
' Keyboard Shortcut: Ctrl+m
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\aaa\CreditData-021809dater.csv", Destination:=Range("A1"))
.Name = "CreditData-021809dater"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim Wkbk As Workbook

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV",
_
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

set wkbk = Workbooks.Open(Filename:=Filename:=myFileName)

Range("A1").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
Range("A1:AO1").Select
Selection.Font.Bold = True
End Sub

=========================================================

You can use as many workbooks as you want.

You can have 20 different workbooks or a single workbook. Or even a
few--where
you put the macros that generate similar reports in those few workbooks.

You could use something like this to ask for the filename:

tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant
Dim Wkbk as workbook

myFileName = Application.GetOpenFilename(filefilter:="CSV Files, *.CSV",
_
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

set wkbk = Workbooks.Open(Filename:=Filename:=myFileName)

'....rest of recorded code here!

End Sub

=========================================================
I prefer to have each report stand alone. All the .CSV files for one
report
will be in one folder. Other reports will have their own folders. So for
each of the reports, I need to have the macro let the user browse the
folder
for his preferred file. Is this possible? Each spreadsheet will only
need
one "import" button on the left end of the toolbar. Can each .XLS have
it's
own macro embedded it? Or must the user have one workbook project, that
contains the macros? This will be harder for me to maintain than if they
stand alone.
=========================================================

You could make a toolbar that has the 15-20 different macros on it. But I
think
I'd use multiple buttons from the Forms toolbar placed directly on a
worksheet.

And if some of those reports are always run at the same time as others,
then
I'd
have one button that ran those reports.

Caption one of the buttons "Click me to run reports 1-6"

And assign a "combined" macro to that button:

Option Explicit
Sub DoReports_1_6()
call DoReport1
call DoReport2
call DoReport3
call DoReport4
call DoReport5
call DoReport6
End Sub

Where those doReport# procedures are in that same workbook's project.

====
By using buttons from the Forms toolbar placed directly on the worksheet,
you
can have as many notes as you like near that button.

=========================================================

Dave,
One user will be using these Excel reports.
Each having many or few rows and columns.
I will be developing 15 or 20 different reports for this user.
Some will be run multiple times per day as batches are processed.
Others may only be run once a week or once a month.
The .CSV files will be in the same folder.
The user is used to pointing to the correct file for other processes.
So, the process can not require my participation.
I like putting the "import" button at the left edge of the toolbar.
Please comment...
Thanks,
Paul

=========================================================

You can distribute the two (*.xls and *.csv) files, but that sounds kind
of
weird to me.

If you only have a single *.csv file, then you should do the importing,
save
it
as a .xls file and distribute that.

If you have a *.csv file that's updated lots of times, then distribute
the
single *.xls file and then redistribute (as often as it's updated) the
*.csv
files.

If you put the procedure in its own module, you can remove the module.
Debra
Dalgleish shows how:
http://contextures.com/xlfaqMac.html#NoMacros

If you used a single module and have lots of procedures, then you'll
just
select
the procedure that you want to remove and hit the delete key -- just
like
deleting a word/phrase/paragraph in Word or clearing contents in an
excel
range.

Be careful, though. Make sure you have a backup (just in case). You
can
do
that by copying the .xls file to a safe location.

And yep, your users will have to let macros run when they open your
workbook.

Paul H wrote:

=========================================================

Will the macro stay with the .XLS file? Can I distribute just the 2
files -
the .CSV file to be read, and the empty .XLS file with the name I want
it
to
have?

And what do I un-hide so I can remove a macro I want to do over or get
rid
of? I have set security low beause I trust my anto-virus. Will my
users
have a security problem, running my macro?

=========================================================

Macros live in workbooks. So your macro (probably???) lives in that
.xls
file
in the same folder as the .csv. But I don't have a real guess at
where
you
stored this macro--could it be in your personal.xl* workbook?

Personally, I don't like running this kind of macro by a shortcut or
by
using
auto_open. I have to remember the shortcut and make sure that anyone
who
runs
this when I'm not there (vacation???) can remember it, too.

And I wouldn't use auto_open. If I open the workbook for some other
purpose
later in the day, I don't want to have it run automatically.

Instead, I just plop a button from the Forms toolbar onto a worksheet
and
assign
the macro to that button. Add some instructions (for both me and
other
users)
to a separate worksheet (or below the button) and I'm set.

This will mean that I don't have to check to see if the macro has been
run
earlier in the day in my code.

But the choice is yours (obviously).

=========================================================

Paul H wrote:

Dave,

I tried the macro solution - it works! I can read the entire 5005
row
x
42
column file in about 3 seconds. Now I have confusion about the
process.
Where is the macro stored? I created a folder with 2 files - the
.CSV
file
to be read and the empty .XLS file with the name I want it to have.
I
invoke it by pressing ctl-m. I'll change that to Autpen so it
will
run
when I open the .XLS file. When I move the folder with the 2 files
to
another computer, the macro doesn't work. So the macro didn't come
with
the
.XLS file.

I don't know what you mean about the "You could plop the date into a
cell
in
a hidden sheet so that it only runs once. And add a save at the
end".
Also, I want to delete a macro so I can re-record it, but it talks
about
un-hiding something. What?

Thanks for your help.
Paul
=========================================================


the macro recorder will show you the very basic VBA code. you'll
want
to
tidy up
but its a great way to start


I tried it using Excel 2003 and it works there also. I'll try the
entire
process tomorrow (Monday). Wish me luck, and thanks.
Paul

=========================================================

In Excel 2007 you will need to be able to see the [Developer] tab.
If
it
is
not visible now, click the Office Button, then the [Excel Options]
button
near the lower right of the window that opens. In the "Popular"
group,
enable the "Show Developer tab in the ribbon" option.
To record a macro, choose the Developer tab and click the "Record
Macro" -
give it a name and procede with the steps you wish to record. The
"Record
Macro" option will have changed to "Stop Recording". Click it
when
you
have
finished recording the steps you need to repeat later.

:


=========================================================

How do I record a macro? Do you mean I can create a macro that
will
do
my
steps 1 thru 8, below?

=========================================================
It's too difficult to push a button?

If that's true, then name your macro Autpen. It'll run the
first
time
someone opens the workbook. You could plop the date into a cell
in
a
hidden
sheet so that it only runs once. And add a save at the end.

If that doesn't work, good luck with the automation.

Paul H wrote:

=========================================================
I need the COBOL program to properly format the data. Multiple
end-users
will use this, sometimes daily, with data that changes daily,
so
I
have
been
requested to make it completely automatic. Another subsequent
program
shows
them the choices of reports and automatically starts the one
they
select.

=========================================================

Maybe you could drop the requirement that the COBOL program do
it.

Set up a workbook with two sheets (instructions for the user
and
the
actual
data).

Record a macro that does all the work in the second sheet.

The plop a button from the Forms toolbar onto the instruction
sheet
that
calls
that macro.

You only have to rename the worksheet if you're doing File|Open
(or
the
equivalent in code). If you use the import external data
stuff,
you
can
leave
it named .csv.

=========================================================

Paul H wrote:

Thanks Dave,

I used your "import text" method and it does succeed in
importing
my
.CSV
file into my empty .XLS file, in about 2 seconds. That is,
after
I
manually:

1. Open empty XLS file.
2. Data, import external data, import data.
3. "Select Data Source" screen comes up.
4. Type in my xxxx.TXT file name.
5. Import wizard step 1 - select delimited, then next.
6. Import wizard step 2 - select only Comma, then finish.
7. Import data to existing worksheet.
8. It imports the entire file in about 2 seconds, formatted
correctly,
with
columns as described in my empty .XLS file.

Now I need to figure out how to accomplish these steps
automatically
by
my
COBOL program.

Thanks again,
Paul

=========================================================
If you rename the .csv file to .txt, you may be able to
import
the
file
quicker
by setting each field the way you want.

Then you could format the numeric fields as percentages or
whatever
you
needed.

If you have fields that have implicit decimals, you could
import
them
as
Generals and then put a factor of 10 (10, 100, 1000, ...) in
an
empty
cell.
Edit|copy, edit|paste special|Values and divide (all in code)
to
convert
that
field.
=========================================================
Paul H wrote:

I have an empty, formatted spreadsheet, that I created by writing a
few
rows
into it from my COBOL program, field by field, then deleting all of
the
rows.

The process of creating the entire XLS or XLSX runs much too slow
(100
records per minute or less), and uses memory up, so can never allow
me
to
finish converting some of my large CSV files into formatted XLS
(Excel
2003)
or XLSX (Excel 2007) files. Someone told me to "turn off continual
re-calculating". How?

I wonder if I can somehow import my large .CSV file into my .XLS
empty
formatted file? I cannot find a way. Any help would be
appreciated.

TIA, Paul