Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

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
0
Jeremyw
Asked:
Jeremyw
  • 3
  • 2
1 Solution
 
btaplinCommented:
I believe it should be     Set WB1 = xlApp.ActiveWorkbook   as you are referencing an object
0
 
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now