Macro to insert values from a file and save another sheet as a .txt file

F

Frank

Hello All,

Thanks in advance for reading.

I have two input files: MainInput.xls and ParCombos.txt.

ParCombos.txt is a tab delimited text file that has 4 columns.
Each row in this document contains a combination of 4 parameters.

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

I desperately need to write a macro to do the steps below, but I
don't know where to start. The steps are:

1. Open ParCombos.txt, and copy the values in the 1st row.
2. paste the value from the 1st column of ParCombos.txt
into A1 of Sheet 1 of MainInput.xls

paste the value from the 2nd column of ParCombos.txt
into A2 of Sheet 1 of MainInput.xls

paste the value from the 3rd column of ParCombos.txt
into A3 of Sheet 1 of MainInput.xls

paste the value from the 4th column of ParCombos.txt
into A4 of Sheet 1 of MainInput.xls

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

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

5. Repeat steps 1-5 for the values of the 2nd row of ParCombos.txt.
Keep repeating steps 1-5 until we run out of rows in ParCombos.txt

Thus, if ParCombos.txt has X rows, then the output of this macro would
be X tab delimited files named Maininput1.txt, MainInput2.txt, ...,
MainInputx.txt

Any help would be appreciated.

Many Thanks


Frank
 
F

Frank

Hello Trevor

Thank you very much for your suggestion!
I apologize for not realizing that I should do this
earlier.
And then you might need to come back to the group for some more advice ...
but you'll have some code to refine rather than everyone second guessing and
starting from scratch.
Click to expand...

I had recorded the following macro when I had the 4
columns of parameters data [each row gives a different
combination of parameters] on Sheet 3.

The combination of parameters that I had on Sheet 3 is

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

This tells us that the second combination of parameters is
5, 6, 7, 8.

The macro 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\MainInput1.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("MainInput1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
Filename:="C:\source\MainInput2.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("MainInput2").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
Filename:="C:\source\MainInput3.txt", FileFormat:= _
xlText, CreateBackup:=False
End Sub


I need to generate hundreds of these maininput.txt
files. I would truly appreciate any help with this
problem.

Sincerely


Frank


Hello All,

Thanks in advance for reading.

I have two input files: MainInput.xls and ParCombos.txt.

ParCombos.txt is a tab delimited text file that has 4 columns.
Each row in this document contains a combination of 4 parameters.

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

I desperately need to write a macro to do the steps below, but I
don't know where to start. The steps are:

1. Open ParCombos.txt, and copy the values in the 1st row.
2. paste the value from the 1st column of ParCombos.txt
into A1 of Sheet 1 of MainInput.xls

paste the value from the 2nd column of ParCombos.txt
into A2 of Sheet 1 of MainInput.xls

paste the value from the 3rd column of ParCombos.txt
into A3 of Sheet 1 of MainInput.xls

paste the value from the 4th column of ParCombos.txt
into A4 of Sheet 1 of MainInput.xls

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

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

5. Repeat steps 1-5 for the values of the 2nd row of ParCombos.txt.
Keep repeating steps 1-5 until we run out of rows in ParCombos.txt

Thus, if ParCombos.txt has X rows, then the output of this macro would
be X tab delimited files named Maininput1.txt, MainInput2.txt, ...,
MainInputx.txt

Any help would be appreciated.

Many Thanks


Frank
Click to expand...


.
Click to expand...
 
F

Frank

P.S.

Thus we save MainInput2.txt, which consists of
how Sheet2 looks when in Sheet1 we have
cell A1=5, cell A2=6, A3=7, A4=8
 

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

Macro to generate txt files using Excel 4
macro to copy columns from one sheet to another sheet 4
Count the columns from one sheet and paste it in another sheet 0
Auto Update a sheet through Macro 4
Vba code cannot select text pasted from .txt file 6
Cell values to an array, then array transpose to another workbook 8
Creating several smaller worksheets from a 'master sheet' 3
Auto Copy Data from one worksheet to another 1

Top