?
Solved

Declaration problem controlling Excel from Access

Posted on 2005-03-03
5
Medium Priority
?
310 Views
Last Modified: 2012-06-27
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
Comment
Question by:Jeremyw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 1

Accepted Solution

by:
btaplin earned 2000 total points
ID: 13452372
I believe it should be     Set WB1 = xlApp.ActiveWorkbook   as you are referencing an object
0
 
LVL 1

Expert Comment

by:btaplin
ID: 13452397
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
 
LVL 3

Author Comment

by:Jeremyw
ID: 13452412
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
 
LVL 1

Expert Comment

by:btaplin
ID: 13452471
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
 
LVL 3

Author Comment

by:Jeremyw
ID: 13452503
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question