Run Access Macro to open and update excel sheet

[rama4672]

Hi
Can any one help me, I need a macro that will look for a CSV file, open the file, insert a new sheet, copy existing data from first sheet, rename tabs, then save as an Excel Workbook, With a different name,I can open the workbook, but when I try to do the rest of the macro it then falls over, I have inserted below the code that I have, it gets to where the text is in red

Sub OpenSpecific_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String
Dim excel As Object
Dim workbooks As Object


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = CurrentProject.Path & "\Data\Wigan E Desk Output BKUP.csv"


' Open it
With oXL
.Visible = True
.workbooks.Open (sFullPath)
End With

rows("1:1").select
Selection.Insert Shift:=xlDown
Range("A1").select
ActiveCell.FormulaR1C1 = "Date"
Range("B1").select
ActiveCell.FormulaR1C1 = "Drop"
Range("C1").select
ActiveCell.FormulaR1C1 = "Load ID"
Range("D1").select
ActiveCell.FormulaR1C1 = "Wave"
Range("E1").select
ActiveCell.FormulaR1C1 = "Store"
Range("F1").select
ActiveCell.FormulaR1C1 = "Del Time"
Range("G1").select
ActiveCell.FormulaR1C1 = "Sch Dep"
Range("H1").select
ActiveCell.FormulaR1C1 = "Sch Ret"
Range("I1").select
ActiveCell.FormulaR1C1 = "StoreNbr"
Range("I2").select
Sheets("Wigan E Desk Output BKUP").select
Sheets("Wigan E Desk Output BKUP").Name = "Transport"
Sheets("Transport").select
Sheets.Add
Sheets("Sheet1").select
Sheets("Sheet1").Name = "Stores"
Sheets("Transport").select
Columns("I:I").select
Selection.Cut
Sheets("Stores").select
Columns("A:A").select
ActiveSheet.Paste
Sheets("Transport").select
Columns("B:C").select
Selection.Copy
Sheets("Stores").select
Columns("B:B").select
ActiveSheet.Paste
Range("C1").select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "LoadIDNew"
Range("C2").select
ChDir "K:\Transport\Database\Data"
ActiveWorkbook.SaveAs FileName:= _
"K:\Transport\Database\Data\TRANSPORT PLAN.xls", FileFormat:=xlExcel9795, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub


Thanks In Advance