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
|