Link to home
Create AccountLog in
Avatar of cbetter
cbetter

asked on

URGENT -- using Access 2003 (but be 2000 compatible) I need to run a macro created in an XLS file then import data into Access

I've tried using the example from "MillerDS on 10/21/2003 ".  I created a macro in my Excel file and copied it into my Event Procedure on my cmd button, but I keep getting an error.  I'm not really proficient in Access and this is URGENT that I get this working.  Can anyone see what's wrong with my code?  I receive a compile error, Method or data member not found @ line "xlApp.Workbooks.Open strFileName" and the file does exist in the "c:\" directory.  (Sorry about the size of the code)

Private Sub ImprtMnthlySprdsht_DblClick(Cancel As Integer)
   
    msg = "File c:\cad.xls is now importing......"
    Dim strFileName As String
    Dim xlApp As Application
    strFileName = ("C:\cad.xls")
    Set xlApp = New Application
    xlApp.Visible = True
    xlApp.Workbooks.Open strFileName
    xlApp.Cells.Select
    xlApp.Cells.EntireColumn.AutoFit
    With xlApp.Selection
       .HorizontalAlignment = xlCenter
       .VerticalAlignment = xlBottom
       .WrapText = False
       .Orientation = 0
       .AddIndent = False
       .ShrinkToFit = False
       .MergeCells = False
       End With
    xlApp.Rows("1:3").Select
    xlApp.Selection.Delete Shift:=xlUp
    xlApp.Range("A1").Select
    xlApp.Windows("Book1").Activate
    xlApp.Range("A3").Select
    xlApp.ActiveCell.FormulaR1C1 = "sales"
    xlApp.Range("A4").Select
    xlApp.ActiveCell.FormulaR1C1 = "order"
    xlApp.Range("A2").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("A2").Select
    xlApp.Range("A2").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Range("B5").Select
    xlApp.Application.CutCopyMode = False
    xlApp.Range("A3:A4").Select
    xlApp.Selection.ClearContents
    xlApp.Range("B6").Select
    xlApp.Windows("sfrt003_nawcad.xls").Activate
    xlApp.Range("A1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("A1").Select
    xlApp.Selection.Copy
    xlApp.ActiveSheet.Paste
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("A2:A3").Select
    xlApp.Selection.ClearContents
    xlApp.Range("A12:A34").Select
    xlApp.ActiveWindow.SmallScroll ToRight:=1
    xlApp.ActiveWindow.SmallScroll Down:=-1
    xlApp.Range("B2").Select
    xlApp.ActiveCell.FormulaR1C1 = "PURCHASE-ORDER"
    xlApp.Range("B3").Select
    xlApp.ActiveCell.FormulaR1C1 = "FUND-DOC"
    xlApp.Range("B1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("B1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("B2:B3").Select
    xlApp.Selection.ClearContents
    xlApp.Range("C2").Select
    xlApp.ActiveCell.FormulaR1C1 = "FOB-PO "
    xlApp.Range("C1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("C1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("C2:C3").Select
    xlApp.Selection.ClearContents
    xlApp.Range("D2").Select
    xlApp.ActiveCell.FormulaR1C1 = "WORK-COMP "
    xlApp.Range("D1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("D1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Range("D6").Select
    xlApp.Application.CutCopyMode = False
    xlApp.Range("D2:D3").Select
    xlApp.Selection.ClearContents
    xlApp.Range("C1").Select
    xlApp.ActiveCell.FormulaR1C1 = "FOB-PO-DATE"
    xlApp.Range("D1").Select
    xlApp.ActiveCell.FormulaR1C1 = "WORK-COMP-DATE"
    xlApp.Range("E2").Select
    xlApp.ActiveCell.FormulaR1C1 = "BEG-"
    xlApp.Range("E1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("E1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("E2:E3").Select
    xlApp.Selection.ClearContents
    xlApp.xlApp.Columns("E:E").ColumnWidth = 9.57
    xlApp.Range("E1").Select
    xlApp.ActiveCell.FormulaR1C1 = "BEG-FY"
    xlApp.Range("F2").Select
    xlApp.ActiveCell.FormulaR1C1 = "END-"
    xlApp.Range("F1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("F1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("F2:F3").Select
    xlApp.Selection.ClearContents
    xlApp.Range("F1").Select
    xlApp.ActiveCell.FormulaR1C1 = "END-FY"
    xlApp.Range("G1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=R[2]C"
    xlApp.Range("G1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.ActiveSheet.Paste
    xlApp.Application.CutCopyMode = False
    xlApp.Range("G3").Select
    xlApp.Selection.ClearContents
    xlApp.Range("H1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=R[2]C"
    xlApp.Range("H1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("I1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=R[2]C"
    xlApp.Range("I1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("H3:I3").Select
    xlApp.Selection.ClearContents
    xlApp.Range("J2").Select
    xlApp.ActiveCell.FormulaR1C1 = "PROGRAM-"
    xlApp.Range("J1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,R[2]C)"
    xlApp.Range("J1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("J2:J3").Select
    xlApp.Selection.ClearContents
    xlApp.xlApp.Columns("J:J").EntireColumn.AutoFit
    xlApp.Range("K3").Select
    xlApp.ActiveCell.FormulaR1C1 = "CASE-NO"
    xlApp.Range("K1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("K1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("K2:K3").Select
    xlApp.Selection.ClearContents
    xlApp.ActiveWindow.SmallScroll ToRight:=9
    xlApp.Range("L3").Select
    xlApp.ActiveCell.FormulaR1C1 = "WBS-ELEMENT"
    xlApp.Range("L1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("L1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("L2:L3").Select
    xlApp.Selection.ClearContents
    xlApp.Range("M1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=R[2]C"
    xlApp.Range("M1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("M3").Select
    xlApp.Selection.ClearContents
    xlApp.Range("N1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=R[2]C"
    xlApp.Range("N1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("N3").Select
    xlApp.Selection.ClearContents
    xlApp.Range("O1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("O1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("O2:O3").Select
    xlApp.Selection.ClearContents
    xlApp.ActiveWindow.SmallScroll ToRight:=4
    xlApp.Range("O1").Select
    xlApp.Range("P1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("P1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("Q1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("S1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("P1:S1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("S1").Select
    xlApp.ActiveCell.FormulaR1C1 = "APPLICANT "
    xlApp.Range("R4").Select
    xlApp.xlApp.Columns("R:R").EntireColumn.AutoFit
    xlApp.xlApp.Columns("R:R").EntireColumn.AutoFit
    xlApp.xlApp.Columns("R:R").ColumnWidth = 9.71
    xlApp.xlApp.Columns("Q:Q").ColumnWidth = 14.86
    xlApp.xlApp.Columns("P:P").ColumnWidth = 13.86
    xlApp.Range("R1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("R1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("T1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("T1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("U1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("U1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.ActiveWindow.SmallScroll ToRight:=6
    xlApp.Range("V1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("V1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("W1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("W1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("X1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("X1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.ActiveSheet.Paste
    xlApp.Application.CutCopyMode = False
    xlApp.Range("Y1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("Y1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.ActiveCell.FormulaR1C1 = "TOTAL-COMMITMENTS"
    xlApp.Range("Z1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("Z1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.ActiveCell.FormulaR1C1 = "TOTAL-OBLIGATIONS"
    xlApp.Range("AA1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AA2").Select
    xlApp.ActiveCell.FormulaR1C1 = "TOTAL-ADJ "
    xlApp.Range("AA1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.ActiveCell.FormulaR1C1 = "TOTAL-ADJ-COSTS"
    xlApp.Range("AB2").Select
    xlApp.ActiveCell.FormulaR1C1 = "TOTAL-ADJ"
    xlApp.Range("AB1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AB1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AB1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AC1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AC1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Range("Y4").Select
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AC1").Select
    xlApp.ActiveCell.FormulaR1C1 = "AVAILABLE-BALANCE"
    xlApp.Range("AE1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AE1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AF1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AF1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AG1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AG1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AH2").Select
    xlApp.ActiveCell.FormulaR1C1 = "SALES-ORD"
    xlApp.Range("AH3").Select
    xlApp.ActiveCell.FormulaR1C1 = "AUTH"
    xlApp.Range("AH1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AH1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AI1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=R[2]C"
    xlApp.Range("AI1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AJ1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AJ1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AK1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=R[2]C"
    xlApp.Range("AK1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.ActiveWindow.SmallScroll ToRight:=9
    xlApp.Range("AL1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=R[2]C"
    xlApp.Range("AL1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AM3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN-FY04"
    xlApp.Range("AM1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AM1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AN3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN=FY04"
    xlApp.Range("AN1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AN3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN-FY04"
    xlApp.Range("AN1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AO2").Select
    xlApp.ActiveCell.FormulaR1C1 = "HRS-PERF"
    xlApp.Range("AO3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN-FY04"
    xlApp.Range("AO1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AO1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AP3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN-FY04"
    xlApp.Range("AP1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AP1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AQ3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN=FY05"
    xlApp.Range("AQ3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN-FY05"
    xlApp.Range("AQ1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AQ1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AR3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN-FY05"
    xlApp.Range("AR1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AR1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.ActiveWindow.SmallScroll ToRight:=7
    xlApp.Range("AS3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN-FY05"
    xlApp.Range("AS2").Select
    xlApp.ActiveCell.FormulaR1C1 = "HRS-PERF"
    xlApp.Range("AS1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AS1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AT3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN-FY05"
    xlApp.Range("AT1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AT1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AU3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN-FY06"
    xlApp.Range("AU1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AU1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AV3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN-FY06"
    xlApp.Range("AV1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AV1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AW1").Select
    xlApp.ActiveWindow.SmallScroll ToRight:=4
    xlApp.Range("AX3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN-FY06"
    xlApp.Range("AX1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AX1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AW3").Select
    xlApp.ActiveCell.FormulaR1C1 = "IN-FY06"
    xlApp.Range("AW2").Select
    xlApp.ActiveCell.FormulaR1C1 = "HRS-PERF"
    xlApp.Range("AW1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AW1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.xlApp.Columns("AW:AW").EntireColumn.AutoFit
    xlApp.xlApp.Columns("AX:AX").EntireColumn.AutoFit
    xlApp.Range("AW2:AW3").Select
    xlApp.Selection.ClearContents
    xlApp.Range("AX2:AX3").Select
    xlApp.Selection.ClearContents
    xlApp.Range("AY3").Select
    xlApp.ActiveCell.FormulaR1C1 = "DATE"
    xlApp.Range("AY2").Select
    xlApp.ActiveCell.FormulaR1C1 = "CANC"
    xlApp.Range("AY1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AY1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("AZ1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("AZ1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("BA1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=R[2]C"
    xlApp.Range("BA1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("BB1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("BB1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("BC2").Select
    xlApp.ActiveCell.FormulaR1C1 = "CURR-PLAN"
    xlApp.Range("BB12").Select
    xlApp.ActiveWindow.SmallScroll ToRight:=6
    xlApp.Range("BC1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C[-53])"
    xlApp.Range("BC1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("BC1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("BC1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("BD3").Select
    xlApp.ActiveCell.FormulaR1C1 = "PLAN-DUPES"
    xlApp.Range("BD1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("BD1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("BE2").Select
    xlApp.ActiveCell.FormulaR1C1 = "OUTYR-PLAN"
    xlApp.Range("BE1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("BE1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("BF2").Select
    xlApp.ActiveCell.FormulaR1C1 = "OUTYR-PLAN"
    xlApp.Range("BF1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("BF1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("BG1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("BG1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("BH1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("BH1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.ActiveWindow.SmallScroll ToRight:=3
    xlApp.Range("BI1").Select
    xlApp.ActiveCell.FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("BI1").Select
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Rows("2:3").Select
    xlApp.Range("BE2").Activate
    xlApp.Selection.Delete Shift:=xlUp
    xlApp.ActiveWindow.ScrollColumn = 56
    xlApp.ActiveWindow.ScrollColumn = 55
    xlApp.ActiveWindow.ScrollColumn = 54
    xlApp.ActiveWindow.ScrollColumn = 52
    xlApp.ActiveWindow.ScrollColumn = 51
    xlApp.ActiveWindow.ScrollColumn = 49
    xlApp.ActiveWindow.ScrollColumn = 47
    xlApp.ActiveWindow.ScrollColumn = 44
    xlApp.ActiveWindow.ScrollColumn = 42
    xlApp.ActiveWindow.ScrollColumn = 40
    xlApp.ActiveWindow.ScrollColumn = 37
    xlApp.ActiveWindow.ScrollColumn = 35
    xlApp.ActiveWindow.ScrollColumn = 33
    xlApp.ActiveWindow.ScrollColumn = 31
    xlApp.ActiveWindow.ScrollColumn = 29
    xlApp.ActiveWindow.ScrollColumn = 28
    xlApp.ActiveWindow.ScrollColumn = 27
    xlApp.ActiveWindow.ScrollColumn = 26
    xlApp.ActiveWindow.ScrollColumn = 25
    xlApp.ActiveWindow.ScrollColumn = 24
    xlApp.ActiveWindow.ScrollColumn = 23
    xlApp.ActiveWindow.ScrollColumn = 22
    xlApp.ActiveWindow.ScrollColumn = 21
    xlApp.ActiveWindow.ScrollColumn = 20
    xlApp.ActiveWindow.ScrollColumn = 19
    xlApp.ActiveWindow.ScrollColumn = 18
    xlApp.ActiveWindow.ScrollColumn = 17
    xlApp.ActiveWindow.ScrollColumn = 16
    xlApp.ActiveWindow.ScrollColumn = 15
    xlApp.ActiveWindow.ScrollColumn = 14
    xlApp.ActiveWindow.ScrollColumn = 13
    xlApp.ActiveWindow.ScrollColumn = 12
    xlApp.ActiveWindow.ScrollColumn = 11
    xlApp.ActiveWindow.ScrollColumn = 10
    xlApp.ActiveWindow.ScrollColumn = 9
    xlApp.ActiveWindow.ScrollColumn = 8
    xlApp.ActiveWindow.ScrollColumn = 7
    xlApp.ActiveWindow.ScrollColumn = 6
    xlApp.ActiveWindow.ScrollColumn = 5
    xlApp.ActiveWindow.ScrollColumn = 4
    xlApp.Range("K34").Select
    xlApp.ActiveWindow.ScrollColumn = 3
    xlApp.ActiveWindow.ScrollColumn = 2
    xlApp.ActiveWindow.ScrollColumn = 1
    Cells.Select
    xlApp.Selection.NumberFormat = "@"
    xlApp.Windows("Book1").Activate
    Workbooks.Open Filename:="C:\B9\TEST4_sfrt003_nawcad_WK7_fy06 23 Nov 05.xls"
    xlApp.ActiveWindow.LargeScroll Down:=-1
    xlApp.ActiveWindow.ScrollRow = 7062
    xlApp.ActiveWindow.ScrollRow = 7022
    xlApp.ActiveWindow.ScrollRow = 6970
    xlApp.ActiveWindow.ScrollRow = 6865
    xlApp.ActiveWindow.ScrollRow = 6761
    xlApp.ActiveWindow.ScrollRow = 6630
    xlApp.ActiveWindow.ScrollRow = 6447
    xlApp.ActiveWindow.ScrollRow = 6225
    xlApp.ActiveWindow.ScrollRow = 5950
    xlApp.ActiveWindow.ScrollRow = 5649
    xlApp.ActiveWindow.ScrollRow = 5362
    xlApp.ActiveWindow.ScrollRow = 5022
    xlApp.ActiveWindow.ScrollRow = 4669
    xlApp.ActiveWindow.ScrollRow = 4512
    xlApp.ActiveWindow.ScrollRow = 4185
    xlApp.ActiveWindow.ScrollRow = 3845
    xlApp.ActiveWindow.ScrollRow = 3688
    xlApp.ActiveWindow.ScrollRow = 3401
    xlApp.ActiveWindow.ScrollRow = 3100
    xlApp.ActiveWindow.ScrollRow = 2668
    xlApp.ActiveWindow.ScrollRow = 2381
    xlApp.ActiveWindow.ScrollRow = 2237
    xlApp.ActiveWindow.ScrollRow = 1988
    xlApp.ActiveWindow.ScrollRow = 1753
    xlApp.ActiveWindow.ScrollRow = 1557
    xlApp.ActiveWindow.ScrollRow = 1361
    xlApp.ActiveWindow.ScrollRow = 1178
    xlApp.ActiveWindow.ScrollRow = 1034
    xlApp.ActiveWindow.ScrollRow = 903
    xlApp.ActiveWindow.ScrollRow = 825
    xlApp.ActiveWindow.ScrollRow = 759
    xlApp.ActiveWindow.ScrollRow = 733
    xlApp.ActiveWindow.ScrollRow = 720
    xlApp.ActiveWindow.ScrollRow = 681
    xlApp.ActiveWindow.ScrollRow = 629
    xlApp.ActiveWindow.ScrollRow = 576
    xlApp.ActiveWindow.ScrollRow = 511
    xlApp.ActiveWindow.ScrollRow = 432
    xlApp.ActiveWindow.ScrollRow = 354
    xlApp.ActiveWindow.ScrollRow = 328
    xlApp.ActiveWindow.ScrollRow = 302
    xlApp.ActiveWindow.ScrollRow = 276
    xlApp.ActiveWindow.ScrollRow = 171
    xlApp.ActiveWindow.ScrollRow = 119
    xlApp.ActiveWindow.ScrollRow = 79
    xlApp.ActiveWindow.ScrollRow = 53
    xlApp.Windows("sfrt003_nawcad.xls").Activate
    xlApp.Selection.Replace What:="", Replacement:="""""", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    xlApp.ActiveWindow.ScrollRow = 40
    xlApp.ActiveWindow.ScrollRow = 119
    xlApp.ActiveWindow.ScrollRow = 236
    xlApp.ActiveWindow.ScrollRow = 406
    xlApp.ActiveWindow.ScrollRow = 668
    xlApp.ActiveWindow.ScrollRow = 969
    xlApp.ActiveWindow.ScrollRow = 1296
    xlApp.ActiveWindow.ScrollRow = 1584
    xlApp.ActiveWindow.ScrollRow = 1819
    xlApp.ActiveWindow.ScrollRow = 2028
    xlApp.ActiveWindow.ScrollRow = 2224
    xlApp.ActiveWindow.ScrollRow = 2421
    xlApp.ActiveWindow.ScrollRow = 2669
    xlApp.ActiveWindow.ScrollRow = 2996
    xlApp.ActiveWindow.ScrollRow = 3310
    xlApp.ActiveWindow.ScrollRow = 3572
    xlApp.ActiveWindow.ScrollRow = 3794
    xlApp.ActiveWindow.ScrollRow = 3977
    xlApp.ActiveWindow.ScrollRow = 4147
    xlApp.ActiveWindow.ScrollRow = 4343
    xlApp.ActiveWindow.ScrollRow = 4579
    xlApp.ActiveWindow.ScrollRow = 4827
    xlApp.ActiveWindow.ScrollRow = 5063
    xlApp.ActiveWindow.ScrollRow = 5311
    xlApp.ActiveWindow.ScrollRow = 5507
    xlApp.ActiveWindow.ScrollRow = 5625
    xlApp.ActiveWindow.ScrollRow = 5677
    xlApp.ActiveWindow.ScrollRow = 5730
    xlApp.ActiveWindow.ScrollRow = 5808
    xlApp.ActiveWindow.ScrollRow = 5873
    xlApp.ActiveWindow.ScrollRow = 5913
    xlApp.ActiveWindow.ScrollRow = 5952
    xlApp.ActiveWindow.ScrollRow = 5965
    xlApp.ActiveWindow.ScrollRow = 5991
    xlApp.ActiveWindow.ScrollRow = 6017
    xlApp.ActiveWindow.ScrollRow = 6030
    xlApp.ActiveWindow.ScrollRow = 6070
    xlApp.ActiveWindow.ScrollRow = 6122
    xlApp.ActiveWindow.ScrollRow = 6148
    xlApp.ActiveWindow.ScrollRow = 6213
    xlApp.ActiveWindow.ScrollRow = 6318
    xlApp.ActiveWindow.ScrollRow = 6397
    xlApp.ActiveWindow.ScrollRow = 6501
    xlApp.ActiveWindow.ScrollRow = 6593
    xlApp.ActiveWindow.ScrollRow = 6645
    xlApp.ActiveWindow.ScrollRow = 6763
    xlApp.ActiveWindow.ScrollRow = 6867
    xlApp.ActiveWindow.ScrollRow = 6946
    xlApp.ActiveWindow.ScrollRow = 7011
    xlApp.ActiveWindow.ScrollRow = 7064
    xlApp.ActiveWindow.ScrollRow = 7129
    xlApp.ActiveWindow.ScrollColumn = 2
    xlApp.ActiveWindow.ScrollColumn = 3
    xlApp.ActiveWindow.ScrollColumn = 4
    xlApp.ActiveWindow.ScrollColumn = 5
    xlApp.ActiveWindow.ScrollColumn = 6
    xlApp.ActiveWindow.ScrollColumn = 8
    xlApp.ActiveWindow.ScrollColumn = 10
    xlApp.ActiveWindow.ScrollColumn = 12
    xlApp.ActiveWindow.ScrollColumn = 14
    xlApp.ActiveWindow.ScrollColumn = 16
    xlApp.ActiveWindow.ScrollColumn = 19
    xlApp.ActiveWindow.ScrollColumn = 21
    xlApp.ActiveWindow.ScrollColumn = 23
    xlApp.ActiveWindow.ScrollColumn = 26
    xlApp.ActiveWindow.ScrollColumn = 28
    xlApp.ActiveWindow.ScrollColumn = 30
    xlApp.ActiveWindow.ScrollColumn = 32
    xlApp.ActiveWindow.ScrollColumn = 35
    xlApp.ActiveWindow.ScrollColumn = 37
    xlApp.ActiveWindow.ScrollColumn = 38
    xlApp.ActiveWindow.ScrollColumn = 39
    xlApp.ActiveWindow.ScrollColumn = 41
    xlApp.ActiveWindow.ScrollColumn = 43
    xlApp.ActiveWindow.ScrollColumn = 44
    xlApp.ActiveWindow.ScrollColumn = 46
    xlApp.ActiveWindow.ScrollColumn = 47
    xlApp.ActiveWindow.ScrollColumn = 49
    xlApp.ActiveWindow.ScrollColumn = 50
    xlApp.ActiveWindow.ScrollColumn = 51
    xlApp.ActiveWindow.ScrollColumn = 53
    xlApp.ActiveWindow.ScrollColumn = 54
    xlApp.ActiveWindow.ScrollColumn = 56
    xlApp.ActiveWindow.ScrollColumn = 57
    xlApp.ActiveWindow.ScrollColumn = 58
    xlApp.ActiveWindow.ScrollColumn = 59
    xlApp.ActiveWindow.ScrollColumn = 61
    xlApp.ActiveWindow.ScrollColumn = 62
    xlApp.ActiveWindow.ScrollColumn = 1
    xlApp.ActiveWindow.ScrollColumn = 62
    xlApp.Windows("TEST4_sfrt003_nawcad_WK7_fy06 23 Nov 05.xls").Activate
    xlApp.ActiveWindow.ScrollRow = 79
    xlApp.ActiveWindow.ScrollRow = 158
    xlApp.ActiveWindow.ScrollRow = 289
    xlApp.ActiveWindow.ScrollRow = 432
    xlApp.ActiveWindow.ScrollRow = 602
    xlApp.ActiveWindow.ScrollRow = 825
    xlApp.ActiveWindow.ScrollRow = 1099
    xlApp.ActiveWindow.ScrollRow = 1361
    xlApp.ActiveWindow.ScrollRow = 1583
    xlApp.ActiveWindow.ScrollRow = 1792
    xlApp.ActiveWindow.ScrollRow = 1988
    xlApp.ActiveWindow.ScrollRow = 2185
    xlApp.ActiveWindow.ScrollRow = 2341
    xlApp.ActiveWindow.ScrollRow = 2485
    xlApp.ActiveWindow.ScrollRow = 2603
    xlApp.ActiveWindow.ScrollRow = 2694
    xlApp.ActiveWindow.ScrollRow = 2786
    xlApp.ActiveWindow.ScrollRow = 2904
    xlApp.ActiveWindow.ScrollRow = 3034
    xlApp.ActiveWindow.ScrollRow = 3152
    xlApp.ActiveWindow.ScrollRow = 3270
    xlApp.ActiveWindow.ScrollRow = 3361
    xlApp.ActiveWindow.ScrollRow = 3453
    xlApp.ActiveWindow.ScrollRow = 3531
    xlApp.ActiveWindow.ScrollRow = 3597
    xlApp.ActiveWindow.ScrollRow = 3662
    xlApp.ActiveWindow.ScrollRow = 3727
    xlApp.ActiveWindow.ScrollRow = 3780
    xlApp.ActiveWindow.ScrollRow = 3806
    xlApp.ActiveWindow.ScrollRow = 3845
    xlApp.ActiveWindow.ScrollRow = 3858
    xlApp.ActiveWindow.ScrollRow = 3963
    xlApp.ActiveWindow.ScrollRow = 4316
    xlApp.ActiveWindow.ScrollRow = 4643
    xlApp.ActiveWindow.ScrollRow = 4996
    xlApp.ActiveWindow.ScrollRow = 5323
    xlApp.ActiveWindow.ScrollRow = 5636
    xlApp.ActiveWindow.ScrollRow = 5937
    xlApp.ActiveWindow.ScrollRow = 6264
    xlApp.ActiveWindow.ScrollRow = 6578
    xlApp.ActiveWindow.ScrollRow = 6852
    xlApp.ActiveWindow.ScrollRow = 7049
    xlApp.ActiveWindow.ScrollRow = 7127
    xlApp.ActiveWindow.ScrollRow = 53
    xlApp.ActiveWindow.ScrollRow = 7127
    xlApp.ActiveWindow.ScrollColumn = 2
    xlApp.ActiveWindow.ScrollColumn = 3
    xlApp.ActiveWindow.ScrollColumn = 4
    xlApp.ActiveWindow.ScrollColumn = 5
    xlApp.ActiveWindow.ScrollColumn = 6
    xlApp.ActiveWindow.ScrollColumn = 7
    xlApp.ActiveWindow.ScrollColumn = 9
    xlApp.ActiveWindow.ScrollColumn = 10
    xlApp.ActiveWindow.ScrollColumn = 11
    xlApp.ActiveWindow.ScrollColumn = 12
    xlApp.ActiveWindow.ScrollColumn = 14
    xlApp.ActiveWindow.ScrollColumn = 15
    xlApp.ActiveWindow.ScrollColumn = 16
    xlApp.ActiveWindow.ScrollColumn = 17
    xlApp.ActiveWindow.ScrollColumn = 18
    xlApp.ActiveWindow.ScrollColumn = 19
    xlApp.ActiveWindow.ScrollColumn = 20
    xlApp.ActiveWindow.ScrollColumn = 21
    xlApp.ActiveWindow.ScrollColumn = 22
    xlApp.ActiveWindow.ScrollColumn = 24
    xlApp.ActiveWindow.ScrollColumn = 25
    xlApp.ActiveWindow.ScrollColumn = 26
    xlApp.ActiveWindow.ScrollColumn = 28
    xlApp.ActiveWindow.ScrollColumn = 29
    xlApp.ActiveWindow.ScrollColumn = 31
    xlApp.ActiveWindow.ScrollColumn = 32
    xlApp.ActiveWindow.ScrollColumn = 34
    xlApp.ActiveWindow.ScrollColumn = 36
    xlApp.ActiveWindow.ScrollColumn = 37
    xlApp.ActiveWindow.ScrollColumn = 38
    xlApp.ActiveWindow.ScrollColumn = 39
    xlApp.ActiveWindow.ScrollColumn = 40
    xlApp.ActiveWindow.ScrollColumn = 41
    xlApp.ActiveWindow.ScrollColumn = 42
    xlApp.ActiveWindow.ScrollColumn = 43
    xlApp.ActiveWindow.ScrollColumn = 44
    xlApp.ActiveWindow.ScrollColumn = 45
    xlApp.ActiveWindow.ScrollColumn = 46
    xlApp.ActiveWindow.ScrollColumn = 47
    xlApp.ActiveWindow.ScrollColumn = 48
    xlApp.ActiveWindow.ScrollColumn = 49
    xlApp.ActiveWindow.ScrollColumn = 50
    xlApp.ActiveWindow.ScrollColumn = 51
    xlApp.ActiveWindow.ScrollColumn = 53
    xlApp.ActiveWindow.ScrollColumn = 54
    xlApp.ActiveWindow.ScrollColumn = 55
    xlApp.ActiveWindow.ScrollColumn = 56
    xlApp.ActiveWindow.ScrollColumn = 57
    xlApp.ActiveWindow.ScrollColumn = 58
    xlApp.ActiveWindow.ScrollColumn = 59
    xlApp.ActiveWindow.ScrollColumn = 60
    xlApp.ActiveWindow.ScrollColumn = 59
    xlApp.ActiveWindow.ScrollColumn = 58
    xlApp.ActiveWindow.ScrollColumn = 57
    xlApp.ActiveWindow.ScrollColumn = 56
    xlApp.ActiveWindow.ScrollColumn = 57
    xlApp.ActiveWindow.ScrollColumn = 58
    xlApp.ActiveWindow.ScrollColumn = 59
    xlApp.ActiveWindow.ScrollColumn = 60
    xlApp.ActiveWindow.ScrollColumn = 61
    xlApp.ActiveWindow.ScrollColumn = 62
    xlApp.ActiveWindow.ScrollColumn = 1
    xlApp.ActiveWindow.ScrollColumn = 62
    xlApp.ActiveWindow.ScrollColumn = 60
    xlApp.ActiveWindow.ScrollColumn = 59
    xlApp.ActiveWindow.ScrollColumn = 60
    xlApp.ActiveWindow.ScrollColumn = 61
    xlApp.ActiveWindow.ScrollColumn = 62
    xlApp.ActiveWindow.SmallScroll ToRight:=-1
    xlApp.ActiveWindow.ScrollRow = 7088
    xlApp.ActiveWindow.ScrollRow = 7062
    xlApp.ActiveWindow.ScrollRow = 7035
    xlApp.ActiveWindow.ScrollRow = 6996
    xlApp.ActiveWindow.ScrollRow = 6970
    xlApp.ActiveWindow.ScrollRow = 6944
    xlApp.ActiveWindow.ScrollRow = 6905
    xlApp.ActiveWindow.ScrollRow = 6879
    xlApp.ActiveWindow.ScrollRow = 6852
    xlApp.ActiveWindow.ScrollRow = 6813
    xlApp.ActiveWindow.ScrollRow = 6800
    xlApp.ActiveWindow.ScrollRow = 6787
    xlApp.ActiveWindow.ScrollRow = 6761
    xlApp.ActiveWindow.ScrollRow = 6748
    xlApp.ActiveWindow.ScrollRow = 6709
    xlApp.ActiveWindow.ScrollRow = 6669
    xlApp.ActiveWindow.ScrollRow = 6630
    xlApp.ActiveWindow.ScrollRow = 6591
    xlApp.ActiveWindow.ScrollRow = 6578
    xlApp.ActiveWindow.ScrollRow = 6512
    xlApp.ActiveWindow.ScrollRow = 6473
    xlApp.ActiveWindow.ScrollRow = 6421
    xlApp.ActiveWindow.ScrollRow = 6356
    xlApp.ActiveWindow.ScrollRow = 6303
    xlApp.ActiveWindow.ScrollRow = 6251
    xlApp.ActiveWindow.ScrollRow = 6225
    xlApp.ActiveWindow.ScrollRow = 6199
    xlApp.ActiveWindow.ScrollRow = 6186
    xlApp.ActiveWindow.ScrollRow = 6146
    xlApp.ActiveWindow.ScrollRow = 6120
    xlApp.ActiveWindow.ScrollRow = 6068
    xlApp.ActiveWindow.ScrollRow = 6055
    xlApp.ActiveWindow.ScrollRow = 6003
    xlApp.ActiveWindow.ScrollRow = 5950
    xlApp.ActiveWindow.ScrollRow = 5898
    xlApp.ActiveWindow.ScrollRow = 5833
    xlApp.ActiveWindow.ScrollRow = 5793
    xlApp.ActiveWindow.ScrollRow = 5741
    xlApp.ActiveWindow.ScrollRow = 5676
    xlApp.ActiveWindow.ScrollRow = 5610
    xlApp.ActiveWindow.ScrollRow = 5558
    xlApp.ActiveWindow.ScrollRow = 5493
    xlApp.ActiveWindow.ScrollRow = 5427
    xlApp.ActiveWindow.ScrollRow = 5362
    xlApp.ActiveWindow.ScrollRow = 5296
    xlApp.ActiveWindow.ScrollRow = 5231
    xlApp.ActiveWindow.ScrollRow = 5166
    xlApp.ActiveWindow.ScrollRow = 5113
    xlApp.ActiveWindow.ScrollRow = 5048
    xlApp.ActiveWindow.ScrollRow = 5009
    xlApp.ActiveWindow.ScrollRow = 4930
    xlApp.ActiveWindow.ScrollRow = 4839
    xlApp.ActiveWindow.ScrollRow = 4773
    xlApp.ActiveWindow.ScrollRow = 4695
    xlApp.ActiveWindow.ScrollRow = 4617
    xlApp.ActiveWindow.ScrollRow = 4538
    xlApp.ActiveWindow.ScrollRow = 4473
    xlApp.ActiveWindow.ScrollRow = 4381
    xlApp.ActiveWindow.ScrollRow = 4277
    xlApp.ActiveWindow.ScrollRow = 4159
    xlApp.ActiveWindow.ScrollRow = 4028
    xlApp.ActiveWindow.ScrollRow = 3845
    xlApp.ActiveWindow.ScrollRow = 3662
    xlApp.ActiveWindow.ScrollRow = 3492
    xlApp.ActiveWindow.ScrollRow = 3296
    xlApp.ActiveWindow.ScrollRow = 3126
    xlApp.ActiveWindow.ScrollRow = 2956
    xlApp.ActiveWindow.ScrollRow = 2812
    xlApp.ActiveWindow.ScrollRow = 2681
    xlApp.ActiveWindow.ScrollRow = 2551
    xlApp.ActiveWindow.ScrollRow = 2420
    xlApp.ActiveWindow.ScrollRow = 2289
    xlApp.ActiveWindow.ScrollRow = 2158
    xlApp.ActiveWindow.ScrollRow = 2054
    xlApp.ActiveWindow.ScrollRow = 1936
    xlApp.ActiveWindow.ScrollRow = 1832
    xlApp.ActiveWindow.ScrollRow = 1714
    xlApp.ActiveWindow.ScrollRow = 1635
    xlApp.ActiveWindow.ScrollRow = 1544
    xlApp.ActiveWindow.ScrollRow = 1465
    xlApp.ActiveWindow.ScrollRow = 1374
    xlApp.ActiveWindow.ScrollRow = 1295
    xlApp.ActiveWindow.ScrollRow = 1217
    xlApp.ActiveWindow.ScrollRow = 1152
    xlApp.ActiveWindow.ScrollRow = 1086
    xlApp.ActiveWindow.ScrollRow = 1034
    xlApp.ActiveWindow.ScrollRow = 982
    xlApp.ActiveWindow.ScrollRow = 942
    xlApp.ActiveWindow.ScrollRow = 890
    xlApp.ActiveWindow.ScrollRow = 851
    xlApp.ActiveWindow.ScrollRow = 812
    xlApp.ActiveWindow.ScrollRow = 772
    xlApp.ActiveWindow.ScrollRow = 733
    xlApp.ActiveWindow.ScrollRow = 681
    xlApp.ActiveWindow.ScrollRow = 655
    xlApp.ActiveWindow.ScrollRow = 629
    xlApp.ActiveWindow.ScrollRow = 589
    xlApp.ActiveWindow.ScrollRow = 550
    xlApp.ActiveWindow.ScrollRow = 524
    xlApp.ActiveWindow.ScrollRow = 472
    xlApp.ActiveWindow.ScrollRow = 432
    xlApp.ActiveWindow.ScrollRow = 406
    xlApp.ActiveWindow.ScrollRow = 354
    xlApp.ActiveWindow.ScrollRow = 315
    xlApp.ActiveWindow.ScrollRow = 302
    xlApp.ActiveWindow.ScrollRow = 263
    xlApp.ActiveWindow.ScrollRow = 236
    xlApp.ActiveWindow.ScrollRow = 210
    xlApp.ActiveWindow.ScrollRow = 197
    xlApp.ActiveWindow.ScrollRow = 184
    xlApp.ActiveWindow.ScrollRow = 171
    xlApp.ActiveWindow.ScrollRow = 158
    xlApp.ActiveWindow.ScrollRow = 119
    xlApp.ActiveWindow.ScrollRow = 106
    xlApp.ActiveWindow.ScrollRow = 93
    xlApp.ActiveWindow.ScrollRow = 79
    xlApp.ActiveWindow.ScrollRow = 66
    xlApp.ActiveWindow.ScrollRow = 40
    xlApp.ActiveWindow.ScrollRow = 27
    xlApp.ActiveWindow.ScrollRow = 14
    xlApp.ActiveWindow.ScrollRow = 1
    xlApp.Windows("sfrt003_nawcad.xls").Activate
    xlApp.ActiveWindow.ScrollColumn = 61
    xlApp.ActiveWindow.ScrollColumn = 59
    xlApp.ActiveWindow.ScrollColumn = 56
    xlApp.ActiveWindow.ScrollColumn = 52
    xlApp.ActiveWindow.ScrollColumn = 50
    xlApp.ActiveWindow.ScrollColumn = 47
    xlApp.ActiveWindow.ScrollColumn = 45
    xlApp.ActiveWindow.ScrollColumn = 42
    xlApp.ActiveWindow.ScrollColumn = 39
    xlApp.ActiveWindow.ScrollColumn = 36
    xlApp.ActiveWindow.ScrollColumn = 35
    xlApp.ActiveWindow.ScrollColumn = 34
    xlApp.ActiveWindow.ScrollColumn = 33
    xlApp.ActiveWindow.ScrollColumn = 32
    xlApp.ActiveWindow.ScrollColumn = 31
    xlApp.ActiveWindow.ScrollColumn = 30
    xlApp.ActiveWindow.ScrollColumn = 28
    xlApp.ActiveWindow.ScrollColumn = 25
    xlApp.ActiveWindow.ScrollColumn = 23
    xlApp.ActiveWindow.ScrollColumn = 21
    xlApp.ActiveWindow.ScrollColumn = 19
    xlApp.ActiveWindow.ScrollColumn = 17
    xlApp.ActiveWindow.ScrollColumn = 16
    xlApp.ActiveWindow.ScrollColumn = 14
    xlApp.ActiveWindow.ScrollColumn = 13
    xlApp.ActiveWindow.ScrollColumn = 11
    xlApp.ActiveWindow.ScrollColumn = 10
    xlApp.ActiveWindow.ScrollColumn = 9
    xlApp.ActiveWindow.ScrollColumn = 8
    xlApp.ActiveWindow.ScrollColumn = 7
    xlApp.ActiveWindow.ScrollColumn = 6
    xlApp.ActiveWindow.ScrollColumn = 5
    xlApp.ActiveWindow.ScrollColumn = 4
    xlApp.ActiveWindow.ScrollColumn = 2
    xlApp.ActiveWindow.ScrollColumn = 1

       
  ActiveWorkbook.Close

  Set xlws = Nothing
  Set xlwb = Nothing
  Set xlApp = Nothing


End Sub

 
SOLUTION
Avatar of jmantha709
jmantha709
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of jmantha709
jmantha709
If you're still getting an error, you're probably missing the Excel reference.

In your code module, choose menu Tools -> References

In the list find Microsoft Excel XX.X Object Library and check it.

Retry your function
Avatar of cbetter
cbetter

ASKER

Thanks but I've tried that and I get the compile error:  User-defined type not defined.  :-(    Maybe you may know of a better way to go about doing this.  The file that I need to import from Excel has a 2 title lines that I delete using the macro and then the column titles are split between two rows, which I merge the values into another cell and delete the originals.  This way the column headers are the same as they are in the database.  YIKES!
Avatar of stevbe
stevbe
besides the references chnages suggested above you are still going to have problems. While I did not read all of your code (please get rid of all the Scroll stuff) you reference another workbook that you code does not open ...

xlApp.Windows("sfrt003_nawcad.xls").Activate

Steve
Avatar of jmantha709
jmantha709
Did you look at my second post ?  The reference should solve your "User-defined type not defined" problem
Avatar of cbetter
cbetter

ASKER

Boy, you all are quick!  Let's see, I added the Reference to microsoft Excel.  Ummmm...I tried to not send up the real name of my file, but it actually is "C:\sfrt003_nawcad.xls".  How I came about creating this script was that I went into excel and created a macro then cut & pasted it into my event.  So, what you're telling me is that I can remove all of the lines that have ".ScrollColumn" also?  Sorry but I'm really an Oracle DBA and an Access novice!!!!!  I've done VBScripting and some other VB coding awhile ago, so I'm winging it here!
SOLUTION
Avatar of stevbe
stevbe
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of cbetter
cbetter

ASKER

Well, this would certainly make things more readable.  Give me a few minutes & I'll get back with results from the recommendations thus far!
Avatar of cbetter
cbetter

ASKER

You people ROCK!!!!  Thank you....thank you....thank you both!  Not only is my function working, my code is much more easy to follow!  The only thing is that I would like to add to the code to save the changes made to the .xls file then import the data from the .xls file into Access.  Correct me if I'm wrong, but by using the "xlapp.visible = false" command I won't be able to see the .xls file while I'm running my code against it (which is what I want).  I've put my new code into here, but I've eliminated the redundant lines.  

Private Sub ImprtMnthlySprdsht_DblClick(Cancel As Integer)
   
    msg = "File is now importing......"
    Dim strFileName As String
    Dim xlApp As Excel.Application
    strFileName = ("C:\B9\sfrt003_nawcad.xls")
    Set xlApp = New Excel.Application
    xlApp.Visible = False
    xlApp.Workbooks.Open strFileName
    xlApp.Cells.Select
    xlApp.Cells.EntireColumn.AutoFit
    With xlApp.Selection
       .HorizontalAlignment = xlCenter
       .VerticalAlignment = xlBottom
       .WrapText = False
       .Orientation = 0
       .AddIndent = False
       .ShrinkToFit = False
       .MergeCells = False
       End With

    xlApp.Rows("1:3").Select
    xlApp.Selection.Delete Shift:=xlUp
    xlApp.Range("A1").FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("A1").Copy
    xlApp.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("A2:A3").ClearContents
    xlApp.Range("B2").FormulaR1C1 = "PURCHASE-ORDER"
    xlApp.Range("B3").FormulaR1C1 = "FUND-DOC"
    xlApp.Range("B1").FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("B1").Copy
    xlApp.Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("B2:B3").ClearContents
    xlApp.Range("C2").FormulaR1C1 = "FOB-PO"
    xlApp.Range("C1").FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("C1").Copy
    xlApp.Range("C1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xlApp.Application.CutCopyMode = False
    xlApp.Range("C2:C3").ClearContents
    xlApp.Range("D2").FormulaR1C1 = "WORK-COMP"
    xlApp.Range("D1").FormulaR1C1 = "=CONCATENATE(R[1]C,""-"",R[2]C)"
    xlApp.Range("D1").Copy
    xlApp.Range("D1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
...............(lots of other code)..............................
    xlApp.Rows("2:3").Select
    xlApp.Selection.Delete Shift:=xlUp
    Cells.Select
    xlApp.Selection.Replace What:="", Replacement:="""""", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   ActiveWorkbook.Close

  Set xlws = Nothing
  Set xlwb = Nothing
  Set xlApp = Nothing

End Sub
Avatar of stevbe
stevbe
<add to the code to save the changes made to the .xls file>
'save the workbook
xlWB.Save

'you have to / should close it before importing
xlWB.Close

'cleanup
Set xlWS = Nothing
Set xlWB = Nothing
'make sure the instance of Excel is not still running in task mamanger !
xlApp.Quit
Set xlApp = Nothing


<import the data from the .xls file into Access>
Do you want to import the data into an existing table, create a new table or just link to the spreadsheet?

Steve
Avatar of cbetter
cbetter

ASKER

Thank you sir.  Would this also work to save & close:  
    xlApp.ActiveWorkbook.Save
   xlApp.ActiveWorkbook.Close

I want to import the data into an existing table and my spreadsheet does have column headings the coincide with the table fields.
ASKER CERTIFIED SOLUTION
Avatar of stevbe
stevbe
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of cbetter
cbetter

ASKER

I've been researching about creating links but I can't find a good example on how to do this & then create the append query.  Do you have any suggestions or can you point me in the right direction?
Avatar of cbetter
cbetter

ASKER

TAH~DAH!  I've got it to work!  

I added this line:
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "BUDGET_SPEND_PLAN", "C:\B9\sfrt003_nawcad.xls", True

Please assign 80% points to stevbe and 20% to jmanatha709.  This issue may be closed!