Macro to generate txt files using Excel

F

Frank

Hello

May I please ask for your kind help?

Input.xls is an Excel file that has 3 worksheets. On
Sheet1 there are columns with data and formulas. All of
the values being calculated on this sheet, depend on 4
cells: A1, A2, A3, A4. Sheet 2 has many columns with
formulas. These formulas depend on the values that were
calculated on Sheet 1.

Sheet 3 has 4 columns and X rows. Each row profides a
combination of 4 parameters.

The steps that thus far I have been doing manually are:

1. Open Sheet3, and copy the values in the 1st row.
2. paste the value from the 1st column [the value in cell
A1] of Sheet1 into A1 of Sheet 1

paste the value from the 2nd column [value in B1]of Sheet3
into cell A2 of Sheet 1

paste the value from the 3rd column[value in C1]of Sheet 3
into A3 of Sheet 1 of Sheet 1

paste the value from the 4th column [value inD1]of Sheet 3
into A4 of Sheet 1 of MainInput.xls

3. Wait for the values in both sheets 1 and 2
to be recalculated [This Input.xls file is a 40Mb file].

4. Go to Sheet 2 and Save it [only Sheet 2]
as Input1.txt [in some/any directory]. Save it as a Tab
delimited text file.

5. Repeat steps 1-4 for the values of the 2nd row of
Sheet3.

Keep repeating steps 1-4 until we run out of rows on Sheet3

Thus, if Sheet 3 has X rows, then the output of this macro
would be X tab delimited files named Input1.txt,
Input2.txt, ..., InputX.txt

Any help or suggestion would be greatly appreciated.

===========================
An example of what I am trying to do:

This example is for the case when we have
text below on Sheet 3

1 2 3 4
5 6 7 8
9 10 11 12

Thus, if the second row of Sheet3 is
5 6 7 8
we create Input2.txt which is identical to
how Sheet2 looks when in Sheet1 we have
cell A1=5, cell A2=6, A3=7, A4=8

I need to generate thousands of these input?.txt
files. I simply can't do this manually.
I would truly appreciate any help with this problem.

Sincerely


Frank

P.S.
When I clicked on Macro/Record New Macro,
the output that I got is:

Sub Macro2()

Sheets("Sheet3").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("B1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("C1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A3").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("D1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A4").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
Filename:="C:\source\Input1.txt", FileFormat:= _
xlText, CreateBackup:=False
Sheets("Sheet3").Select
Range("A2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A3").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A4").Select
ActiveSheet.Paste
Sheets("Input1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
Filename:="C:\source\Input2.txt", FileFormat:= _
xlText, CreateBackup:=False
Sheets("Sheet3").Select
Range("A3").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A3").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("D3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A4").Select
ActiveSheet.Paste
Sheets("Input2").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
Filename:="C:\source\Input3.txt", FileFormat:= _
xlText, CreateBackup:=False
End Sub
 
G

Graham Yetton

Frank
This might help...
Sub test()
Sheets("Input2").Select
For y = 1 To LastRow
Application.Calculation = xlManual
For x = 1 To 4
Sheets("Sheet1").Cells(x, y) = Sheets
("Sheet3").Cells(y, x)
Next
Application.Calculation = xlAutomatic
Filename = "C:\source\Input" & y & ".txt"
ActiveWorkbook.SaveAs _
Filename:=Filename, _
FileFormat:=xlText, CreateBackup:=False
Next
End Sub

Change LastRow for the number of your last ow of data.
HTH
Graham Yetton
-----Original Message-----
Hello

May I please ask for your kind help?

Input.xls is an Excel file that has 3 worksheets. On
Sheet1 there are columns with data and formulas. All of
the values being calculated on this sheet, depend on 4
cells: A1, A2, A3, A4. Sheet 2 has many columns with
formulas. These formulas depend on the values that were
calculated on Sheet 1.

Sheet 3 has 4 columns and X rows. Each row profides a
combination of 4 parameters.

The steps that thus far I have been doing manually are:

1. Open Sheet3, and copy the values in the 1st row.
2. paste the value from the 1st column [the value in cell
A1] of Sheet1 into A1 of Sheet 1

paste the value from the 2nd column [value in B1]of Sheet3
into cell A2 of Sheet 1

paste the value from the 3rd column[value in C1]of Sheet 3
into A3 of Sheet 1 of Sheet 1

paste the value from the 4th column [value inD1]of Sheet 3
into A4 of Sheet 1 of MainInput.xls

3. Wait for the values in both sheets 1 and 2
to be recalculated [This Input.xls file is a 40Mb file].

4. Go to Sheet 2 and Save it [only Sheet 2]
as Input1.txt [in some/any directory]. Save it as a Tab
delimited text file.

5. Repeat steps 1-4 for the values of the 2nd row of
Sheet3.

Keep repeating steps 1-4 until we run out of rows on Sheet3

Thus, if Sheet 3 has X rows, then the output of this macro
would be X tab delimited files named Input1.txt,
Input2.txt, ..., InputX.txt

Any help or suggestion would be greatly appreciated.

===========================
An example of what I am trying to do:

This example is for the case when we have
text below on Sheet 3

1 2 3 4
5 6 7 8
9 10 11 12

Thus, if the second row of Sheet3 is
5 6 7 8
we create Input2.txt which is identical to
how Sheet2 looks when in Sheet1 we have
cell A1=5, cell A2=6, A3=7, A4=8

I need to generate thousands of these input?.txt
files. I simply can't do this manually.
I would truly appreciate any help with this problem.

Sincerely


Frank

P.S.
When I clicked on Macro/Record New Macro,
the output that I got is:

Sub Macro2()

Sheets("Sheet3").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("B1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("C1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A3").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("D1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A4").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
Filename:="C:\source\Input1.txt", FileFormat:= _
xlText, CreateBackup:=False
Sheets("Sheet3").Select
Range("A2").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A3").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A4").Select
ActiveSheet.Paste
Sheets("Input1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
Filename:="C:\source\Input2.txt", FileFormat:= _
xlText, CreateBackup:=False
Sheets("Sheet3").Select
Range("A3").Select
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("C3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A3").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("D3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A4").Select
ActiveSheet.Paste
Sheets("Input2").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
Filename:="C:\source\Input3.txt", FileFormat:= _
xlText, CreateBackup:=False
End Sub
.
Click to expand...
 
F

frank

Mr. Yetton

Thank you so much for your reply!
I cordially appreciate your help.

There was a small problem.

The code that I am attaching below compiles
[I replaced the line
"Sheets("Input2").Select" by
"Sheets("Sheet2").Select"]

It works - it creates new files that are based on
Sheet2. However, these files are evaluated at the
values of all 4 parameters = 0.

It seems that the code below takes a value in say
cell C2 of Sheet3 and pastes it into cell C2 of
Sheet1. However the Excel formulas on Sheet2 all
depend on cells A1:A4. The values in Sheet2 would
get recalculated if we were to paste
cell C2 from Sheet3 Into cell A3 [i.e., we need to
transpose each row [that has 4 columns] on Sheet3 and
paste these 4 values into cells A1:A4.

Mr. Yetton, may I please ask for your help again?

Best Regards

Frank

P.S.

The code that compiles:

Sub test()
Sheets("Sheet2").Select
For y = 1 To 3
Application.Calculation = xlManual
For x = 1 To 4
Sheets("Sheet1").Cells(x, y) = Sheets
("Sheet3").Cells(y, x)
Next
Application.Calculation = xlAutomatic
Filename = "C:\source\" & y & ".txt"
ActiveWorkbook.SaveAs _
Filename:=Filename, _
FileFormat:=xlText, CreateBackup:=False
Next
End Sub
 
G

Graham Yetton

Frank - Sorry about that slip up
Tom - Thanks for the correction
Regards
Graham Yetton
-----Original Message-----
in
For x = 1 To 4
Sheets("Sheet1").Cells(x, y) = Sheets ("Sheet3").Cells(y, x)
Next
change to

For x = 1 To 4
Sheets("Sheet1").Cells(x, 1) = Sheets ("Sheet3").Cells(y, x)
Next


changing the first y to a 1 will put the values in column A.

--
Regards,
Tom Ogilvy



frank said:
Mr. Yetton

Thank you so much for your reply!
I cordially appreciate your help.

There was a small problem.

The code that I am attaching below compiles
[I replaced the line
"Sheets("Input2").Select" by
"Sheets("Sheet2").Select"]

It works - it creates new files that are based on
Sheet2. However, these files are evaluated at the
values of all 4 parameters = 0.

It seems that the code below takes a value in say
cell C2 of Sheet3 and pastes it into cell C2 of
Sheet1. However the Excel formulas on Sheet2 all
depend on cells A1:A4. The values in Sheet2 would
get recalculated if we were to paste
cell C2 from Sheet3 Into cell A3 [i.e., we need to
transpose each row [that has 4 columns] on Sheet3 and
paste these 4 values into cells A1:A4.

Mr. Yetton, may I please ask for your help again?

Best Regards

Frank

P.S.

The code that compiles:

Sub test()
Sheets("Sheet2").Select
For y = 1 To 3
Application.Calculation = xlManual
For x = 1 To 4
Sheets("Sheet1").Cells(x, y) = Sheets
("Sheet3").Cells(y, x)
Next
Application.Calculation = xlAutomatic
Filename = "C:\source\" & y & ".txt"
ActiveWorkbook.SaveAs _
Filename:=Filename, _
FileFormat:=xlText, CreateBackup:=False
Next
End Sub
Click to expand...


.
Click to expand...
 
F

Frank

Thank you so much, Graham and Tom!

Thanks to you, I will be able to totally transform project
that I have been working on for several years now.

I am very grateful.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto fill Sheets 1
Copy Code and a sort problem 1
= Today()+1 into Sheet1, Sheet2, and Sheet3 2
conso macro 0
Keep a record of entries in cells 9
Excel help with Macro code in excel 1
Different Results from the Same Macro 2
How to copy from each sheet and paste to a summary sheet 2

Top