I have created a macro to copy from another workbook, the thing is that I am
trying to get it copy from a CSV workbook into an Excel workbook and for some
reason when it does copy, it only says #REF, which would mean it is copying
using a formula, but I'm not using a formula on the sheet, the macro can be
seen below, its basically a two part module:


Module 1:


Sub File_In_Local_Folder()
Application.ScreenUpdating = False
On Error Resume Next

'Call the macro GetRange
GetRange "path to file", "name of file in CSV format", "Sheet1",
"A1:Q1204", _
Sheets("Sheet3").Range("H3")

On Error GoTo 0
Application.ScreenUpdating = True
End Sub


Module 2:

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, DestRange As Range)

Dim Start

'Go to the destination range
Application.Goto DestRange

'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)

'Add formula links to the closed file
With DestRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange

'Wait
Start = Timer
Do While Timer < Start + 1
DoEvents
Loop

'Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(4).Select
Application.CutCopyMode = False
End With
End Sub


In addition is there anyway I can make it so that when it searches within
the folder, it would search for the latest csv file within the folder, the
most recently modified,


Thanks

Have a look here

http://www.rondebruin.nl/copy7.htm

"drinese18" wrote:

I have created a macro to copy from another workbook, the thing is that I am
trying to get it copy from a CSV workbook into an Excel workbook and for some
reason when it does copy, it only says #REF, which would mean it is copying
using a formula, but I'm not using a formula on the sheet, the macro can be
seen below, its basically a two part module:


Module 1:


Sub File_In_Local_Folder()
Application.ScreenUpdating = False
On Error Resume Next

'Call the macro GetRange
GetRange "path to file", "name of file in CSV format", "Sheet1",
"A1:Q1204", _
Sheets("Sheet3").Range("H3")

On Error GoTo 0
Application.ScreenUpdating = True
End Sub


Module 2:

Sub GetRange(FilePath As String, FileName As String, SheetName As String, _
SourceRange As String, DestRange As Range)

Dim Start

'Go to the destination range
Application.Goto DestRange

'Resize the DestRange to the same size as the SourceRange
Set DestRange = DestRange.Resize(Range(SourceRange).Rows.Count, _
Range(SourceRange).Columns.Count)

'Add formula links to the closed file
With DestRange
.FormulaArray = "='" & FilePath & "/[" & FileName & "]" & SheetName _
& "'!" & SourceRange

'Wait
Start = Timer
Do While Timer < Start + 1
DoEvents
Loop

'Make values from the formulas
.Copy
.PasteSpecial xlPasteValues
.Cells(4).Select
Application.CutCopyMode = False
End With
End Sub


In addition is there anyway I can make it so that when it searches within
the folder, it would search for the latest csv file within the folder, the
most recently modified,


Thanks