Declaration problem controlling Excel from Access

I have this code that works if I run it just in Excel, but I need to be able to do all of this from my Access app.
The problem is it keeps crashing at WB1 = xlApp.ActiveWorkbook.  I'm getting the error "Object variable or With block variable not set".

I need to be able to store WB1 & WB2 so I can switch between the two files and copy data between the two.

WB1 is the Excel file the user selects to open and WB2 will always be "C:\batch template.xls"

What do I need to do so I can store both of the filenames as WB1 & WB2?

Here is all the code.

Public Function Kmartkewillbatch()

    Dim xlApp As Excel.Application
    Set xlApp = New Excel.Application
    Dim WB1 As Workbook, WB2 As Workbook
    Dim WS1 As Worksheet, WS2 As Worksheet
    xlApp.Visible = True

    xlApp.Application.Dialogs(xlDialogOpen).Show
    WB1 = xlApp.ActiveWorkbook
    WS1 = xlApp.ActiveSheet
    'Application.Dialogs(xlDialogOpen).Show
    xlApp.Workbooks.Open FileName:="C:\batch template.xls"
    WB2 = xlApp.ActiveWorkbook
    WS2 = xlApp.ActiveSheet
   
    WB1.Activate
    xlApp.Cells.Select
    xlApp.Selection.Sort Key1:=Range("R2"), Order1:=xlDescending, Key2:=Range("B2") _
        , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
   
    xlApp.Columns("C:F").Select
    xlApp.Selection.Insert Shift:=xlToRight
   
    'Range("C2:C" & Range("A65000").End(xlUp).Row).Value = InputBox("Please enter the value.")
   
    xlApp.Range("C2:C" & Range("A65000").End(xlUp).Row).Value = "K"
    xlApp.Range("D2:D" & Range("A65000").End(xlUp).Row).Value = "=RC[-1]&RC[-2]"
    xlApp.Range("E2:E" & Range("A65000").End(xlUp).Row).Value = "KMART#"
    xlApp.Range("F2:F" & Range("A65000").End(xlUp).Row).Value = "=RC[-1]&"" ""&RC[-4]"
   
    xlApp.Range("F1").Activate
    xlApp.Selection.Copy
    xlApp.Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    xlApp.Cells.EntireColumn.AutoFit
   
    CreateDupeLines
   
    xlApp.Columns("F:F").Select
    xlApp.ActiveWorkbook.Names.Add name:="SHIPTONAME", RefersToR1C1:= _
        "='STORE INFO'!C6"

    WS1.Range("SHIPTONAME").Copy WS2.Range("SHIPTONAME")
    WS2.Range("C1").Value = "Ship To Name"
   
    WS1.Range("ADDRESS1").Copy WS2.Range("ADDRESS1")
   
    WS1.Range("CITY").Copy xlApp.WS2.Range("CITY")
    WS1.Range("STATE").Copy xlApp.WS2.Range("STATE")
    WS1.Range("ZIP").Copy xlApp.WS2.Range("ZIP")
    WS1.Range("PURCHASEORDER").Copy xlApp.WS2.Range("PURCHASEORDER")
    WS1.Range("ORDERNUMBER").Copy xlApp.WS2.Range("ORDERNUMBER")
    WS2.Activate
   
    Dim rngCARRIER As Range
    Set rngCARRIER = xlApp.Range("B2:B" & Range("c65000").End(xlUp).Row)
    rngCARRIER.Value = InputBox("Please enter Carrier")
   
    Dim rngATTENTION As Range
    Set rngATTENTION = xlApp.Range("D2:D" & Range("C65000").End(xlUp).Row)
    rngATTENTION.Value = "Receiving"
   
    Dim rngWeight As Range
    Set rngWeight = xlApp.Range("L2:L" & Range("C65000").End(xlUp).Row)
    rngWeight.Value = InputBox("Please enter weight")
   
    Dim rngDEPARTMENT As Range
    Set rngDEPARTMENT = xlApp.Range("R2:R" & Range("C65000").End(xlUp).Row)
    rngDEPARTMENT = InputBox("Please enter Department")
   
    Dim rngPAYFLAG As Range
    Set rngPAYFLAG = xlApp.Range("M2:M" & Range("C65000").End(xlUp).Row)
    rngPAYFLAG = InputBox("Please enter Pay Flag")
   
    xlApp.Cells.Select
    xlApp.Cells.EntireColumn.AutoFit
    xlApp.Range("A1").Select
   
    Dim strFileName As String
    strFileName = "c:\KewillBatch.csv"
     If Len(Dir(strFileName)) > 0 Then
       Kill strFileName
    End If
   
   
    WB2.SaveAs FileName:="C:\KewillBatch.csv", FileFormat:=xlCSV _
      , CreateBackup:=False

    WB1.Activate
    WB1.Close (False)
    WB2.Close (False)
End Function


Thanks,

Jeremy
LVL 3
JeremywAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

btaplinCommented:
I believe it should be     Set WB1 = xlApp.ActiveWorkbook   as you are referencing an object
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
btaplinCommented:
You will find the same error will exist at:
WS1 = xlApp.ActiveSheet
WB2 = xlApp.ActiveWorkbook
WS2 = xlApp.ActiveSheet

Change to:
Set WS1 = xlApp.ActiveSheet
Set WB2 = xlApp.ActiveWorkbook
Set WS2 = xlApp.ActiveSheet
0
JeremywAuthor Commented:
That got it.  

This is strange, I originally had it like that and was having problems, that's why I tried taking it out.  

Oh well, it works now.    

Must be the I D 10 T error.  :)

Thanks,

Jeremy
0
btaplinCommented:
Good stuff.

This caused a lot of confusion for many people ("When do I use Set?", "When do I not use Set?").

Microsoft has remedied this in VB.Net (there is no longer a need for the Set keyword).

btaplin
0
JeremywAuthor Commented:
Yeah, I just started working with VB.Net about 2 weeks ago.  I'm going through the exciting Step by Step book.  :)

Cool stuff though.  I'm looking forward to getting deeper into it.

Thanks again.

Jeremy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.