How can I keep my macro from using a hard reference to a particular run of a
csv file that the macro exports into another worksheet? Each time I run the
csv file (e.g. to tweak the macro, or just to test the macro to see if it's
working), the
computer will give it a new version number...which causes the macro to fail.
Is there a way of altering the code so it does not matter how many times I
run the csv file? Or am I just spinning my wheels?

Here's the code:

Sub OinkBoink()
'
' OinkBoink Macro
' Macro recorded 8/21/2007 by Richard Champlin
'

'
Windows("IC270[1].csv").Activate
Columns("A:C").Select
Selection.Delete Shift:=xlToLeft
Columns("B:H").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("G:H").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=2
Columns("K:K").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlToLeft
Columns("A:J").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:J").EntireColumn.AutoFit
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut
Windows("0807 Summary.xls").Activate
ActiveSheet.Paste
Selection.End(xlToRight).Select
Range("K5").Select
Windows("IC270[1].csv").Activate
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range("I2:J21").Select
Selection.Cut
Windows("0807 Summary.xls").Activate
ActiveSheet.Paste
Range("J5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("J5").Select
Selection.AutoFill Destination:=Range("J5:J24"), Type:=xlFillDefault
Range("J5:J24").Select
Range("N5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-6]"
Range("N5").Select
Selection.AutoFill Destination:=Range("N5:N24"), Type:=xlFillDefault
Range("N5:N24").Select
Range("O5").Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-5]"
Range("O5").Select
Selection.AutoFill Destination:=Range("O5:O24")
Range("O5:O24").Select
Selection.End(xlDown).Select
Range("N25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
Range("N25").Select
Selection.AutoFill Destination:=Range("N25:O25"), Type:=xlFillDefault
Range("N25:O25").Select
Selection.Copy
Range("N27:N28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
Range("N25:O25").Select
Selection.ClearContents
End Sub

Richard Champlin

Try using the GetOpenFileName to retrieve your text file each time you run
the macro. It will open the file with a single worksheet, and then you can
record some code to move the single worksheet into the workbook that your
macro is running from. Your source file will automatically close due to its
not having any worksheets and the original file remains unaltered.
--
Kevin Backmann


"Richard Champlin" wrote:

How can I keep my macro from using a hard reference to a particular run of a
csv file that the macro exports into another worksheet? Each time I run the
csv file (e.g. to tweak the macro, or just to test the macro to see if it's
working), the
computer will give it a new version number...which causes the macro to fail.
Is there a way of altering the code so it does not matter how many times I
run the csv file? Or am I just spinning my wheels?

Here's the code:

Sub OinkBoink()
'
' OinkBoink Macro
' Macro recorded 8/21/2007 by Richard Champlin
'

'
Windows("IC270[1].csv").Activate
Columns("A:C").Select
Selection.Delete Shift:=xlToLeft
Columns("B:H").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
Columns("G:H").Select
Selection.Insert Shift:=xlToRight
ActiveWindow.SmallScroll ToRight:=2
Columns("K:K").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlToLeft
Columns("A:J").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("A:J").EntireColumn.AutoFit
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut
Windows("0807 Summary.xls").Activate
ActiveSheet.Paste
Selection.End(xlToRight).Select
Range("K5").Select
Windows("IC270[1].csv").Activate
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Range("I2:J21").Select
Selection.Cut
Windows("0807 Summary.xls").Activate
ActiveSheet.Paste
Range("J5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("J5").Select
Selection.AutoFill Destination:=Range("J5:J24"), Type:=xlFillDefault
Range("J5:J24").Select
Range("N5").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-6]"
Range("N5").Select
Selection.AutoFill Destination:=Range("N5:N24"), Type:=xlFillDefault
Range("N5:N24").Select
Range("O5").Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-5]"
Range("O5").Select
Selection.AutoFill Destination:=Range("O5:O24")
Range("O5:O24").Select
Selection.End(xlDown).Select
Range("N25").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
Range("N25").Select
Selection.AutoFill Destination:=Range("N25:O25"), Type:=xlFillDefault
Range("N25:O25").Select
Selection.Copy
Range("N27:N28").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
Range("N25:O25").Select
Selection.ClearContents
End Sub

Richard Champlin