Set cell formulas in another workbook.

[Chris Lewis]

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").Delete
Columns("B:B").Delete
Columns("A:A").Delete

' Set formulas for cells in new worksheet.


End Sub

 

[Tom Ogilvy]

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").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

 

[David McRitchie]

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


--

 

[Guest]

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,