| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to generate txt files using Excel
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 a 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to generate txt files using Excel
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 a 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 . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to generate txt files using Excel
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to generate txt files using Excel
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" wrote in message ... 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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to generate txt files using Excel
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| macro to generate next number | Excel Discussion (Misc queries) | |||
| How to create a macro in excel so that it can generate a list ofunique records using all permutations and combinations of the data in eachrow ad column | Excel Discussion (Misc queries) | |||
| How to generate a text file from Excel using a macro or script? | Excel Discussion (Misc queries) | |||
| How to generate a text file from Excel using a macro or script? | Excel Discussion (Misc queries) | |||
| Macro to generate a file from another | Excel Worksheet Functions | |||
![Frank[_17_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
