Hard reference in a macro

G

Guest

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
 
G

Guest

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 said:
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
Click to expand...
 

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 Loop 1
Macro - very slow run in 2003 3
moving all data to cell a1 4
Problem with Macro 7
Excel Macro to use cells as a autofilter 0
File Name changes with dates and Deleting Rows of Zeros 0
why does this macro delete the value in A1? 1
Replace a fixed number with a variable via popup 3

Top