Macro to copy from another workbook
 

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

Macro to copy from another workbook
 

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