| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set cell formulas in another workbook.
I have written a macro in excel that opens a text file, deletes a load of
lines and then does text to columns, and deletes a load of rubbish I don't need. What I now have is data in columns a thru c and i need to set formulas for columns d, e and f (possibly more) The macro sits in one excel file and then open the file in a new workbook. I need to reference the new workbook and set these required formulas for columns d, e etc. How do i do this? The only reference i have to the workbook is the variable myfile but this includes the full file path to the workbook not just its name. Anyway the code is below. Can anyone help? Sub Macro3() ' Declare Variables Dim a As Integer Dim myfile As Variant ' Open Dialog box to choose file myfile = Application.GetOpenFilename("Report Files (*.rpt), *.rpt") If myfile = False Then Exit Sub ' Open file selected in dialog box Workbooks.OpenText FileName:=myfile, Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _ Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _ Other:=False, FieldInfo:=Array(1, 1) ' Delete rubbish not required until end of file a = 1 While Cells(a + 1, 1) < "" Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete a = a + 1 Wend ' Do text to columns Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _ Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _ , 1), Array(8, 1)) ' Delete unwanted columns Columns("H:H").Delete Columns("F:F").Delete Columns("D:D").Delete Columns("B:B").Delete Columns("A:A").Delete ' Set formulas for cells in new worksheet. End Sub -- Chris Lewis |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Set cell formulas in another workbook.
Sub Macro3()
' Declare Variables Dim a As Integer Dim myfile As Variant Dim bk as Workbook ' Open Dialog box to choose file myfile = Application.GetOpenFilename("Report Files (*.rpt), *.rpt") If myfile = False Then Exit Sub ' Open file selected in dialog box Workbooks.OpenText FileName:=myfile, Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _ Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _ Other:=False, FieldInfo:=Array(1, 1) Set bk = ActiveWorkbook ' Delete rubbish not required until end of file a = 1 While Cells(a + 1, 1) < "" Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete a = a + 1 Wend ' Do text to columns Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _ Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _ , 1), Array(8, 1)) ' Delete unwanted columns Columns("H:H").Delete Columns("F:F").Delete Columns("D:D").Delete Columns("B:B").Delete Columns("A:A").Delete ' Set formulas for cells in new worksheet. MsgBox bk.name ' so use bk.name in building your formulas. End Sub "Chris Lewis" <no spam thanks wrote in message ... I have written a macro in excel that opens a text file, deletes a load of lines and then does text to columns, and deletes a load of rubbish I don't need. What I now have is data in columns a thru c and i need to set formulas for columns d, e and f (possibly more) The macro sits in one excel file and then open the file in a new workbook. I need to reference the new workbook and set these required formulas for columns d, e etc. How do i do this? The only reference i have to the workbook is the variable myfile but this includes the full file path to the workbook not just its name. Anyway the code is below. Can anyone help? Sub Macro3() ' Declare Variables Dim a As Integer Dim myfile As Variant ' Open Dialog box to choose file myfile = Application.GetOpenFilename("Report Files (*.rpt), *.rpt") If myfile = False Then Exit Sub ' Open file selected in dialog box Workbooks.OpenText FileName:=myfile, Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _ Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _ Other:=False, FieldInfo:=Array(1, 1) ' Delete rubbish not required until end of file a = 1 While Cells(a + 1, 1) < "" Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete a = a + 1 Wend ' Do text to columns Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _ Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _ , 1), Array(8, 1)) ' Delete unwanted columns Columns("H:H").Delete Columns("F:F").Delete Columns("D:D").Delete Columns("B:B").Delete Columns("A:A").Delete ' Set formulas for cells in new worksheet. End Sub -- Chris Lewis |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Set cell formulas in another workbook.
sub quiktest()
Dim r As Long ' -- you used a for the row r = 3 'simulate row processing at row 3 Cells(r, 4).Formula = "=" & Cells(r, 2).Address(0, 0) & " + " & Cells(r, 3).Address(0, 0) End Sub -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Chris Lewis" <no spam thanks wrote in message ... I have written a macro in excel that opens a text file, deletes a load of lines and then does text to columns, and deletes a load of rubbish I don't need. What I now have is data in columns a thru c and i need to set formulas for columns d, e and f (possibly more) The macro sits in one excel file and then open the file in a new workbook. I need to reference the new workbook and set these required formulas for columns d, e etc. How do i do this? The only reference i have to the workbook is the variable myfile but this includes the full file path to the workbook not just its name. Anyway the code is below. Can anyone help? Sub Macro3() ' Declare Variables Dim a As Integer Dim myfile As Variant ' Open Dialog box to choose file myfile = Application.GetOpenFilename("Report Files (*.rpt), *.rpt") If myfile = False Then Exit Sub ' Open file selected in dialog box Workbooks.OpenText FileName:=myfile, Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _ Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _ Other:=False, FieldInfo:=Array(1, 1) ' Delete rubbish not required until end of file a = 1 While Cells(a + 1, 1) < "" Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete a = a + 1 Wend ' Do text to columns Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _ Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _ , 1), Array(8, 1)) ' Delete unwanted columns Columns("H:H").Delete Columns("F:F").Delete Columns("D:D").Delete Columns("B:B").Delete Columns("A:A").Delete ' Set formulas for cells in new worksheet. End Sub -- Chris Lewis |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Set cell formulas in another workbook.
hi,
WorkBook.add with create a new workbook what are the formulas? -----Original Message----- I have written a macro in excel that opens a text file, deletes a load of lines and then does text to columns, and deletes a load of rubbish I don't need. What I now have is data in columns a thru c and i need to set formulas for columns d, e and f (possibly more) The macro sits in one excel file and then open the file in a new workbook. I need to reference the new workbook and set these required formulas for columns d, e etc. How do i do this? The only reference i have to the workbook is the variable myfile but this includes the full file path to the workbook not just its name. Anyway the code is below. Can anyone help? Sub Macro3() ' Declare Variables Dim a As Integer Dim myfile As Variant ' Open Dialog box to choose file myfile = Application.GetOpenFilename("Report Files (*.rpt), *.rpt") If myfile = False Then Exit Sub ' Open file selected in dialog box Workbooks.OpenText FileName:=myfile, Origin:=xlWindows, _ StartRow:=1, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, _ Tab:=False, Semicolon:=False, Comma:=False, Space:=False, _ Other:=False, FieldInfo:=Array(1, 1) ' Delete rubbish not required until end of file a = 1 While Cells(a + 1, 1) < "" Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete Rows(a).EntireRow.Delete a = a + 1 Wend ' Do text to columns Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _ Array(Array(1, 1), Array(2, 1), Array(3, 1), Array (4, 1), Array(5, 1), Array(6, 1), Array(7 _ , 1), Array(8, 1)) ' Delete unwanted columns Columns("H:H").Delete Columns("F:F").Delete Columns("D:D").Delete Columns("B:B").Delete Columns("A:A").Delete ' Set formulas for cells in new worksheet. End Sub -- Chris Lewis . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| linked workbook cell returns 0 is source workbook cell is blank? | Excel Worksheet Functions | |||
| Formulas in my Workbook | Excel Worksheet Functions | |||
| COpy formulas from one workbook to another workbook | Excel Discussion (Misc queries) | |||
| Tracing cell formulas to other spreadsheets on the same workbook | Excel Discussion (Misc queries) | |||
| Copying Formulas from Workbook to Workbook | Excel Discussion (Misc queries) | |||
Linear Mode
