Link to home
Start Free TrialLog in
Avatar of shieldsco
shieldscoFlag for United States of America

asked on

Access variable not defined error

I'm using the code below and get an error in "Set objApp = CreateObject("Excel.Application")

  Dim xls     As Excel.Application
  Dim wkb     As Excel.Workbook
  Dim wks     As Excel.WorksheetUser generated image  Dim wb As Workbook


Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String

  'On Error GoTo Err_Handler
 
strBrowseMsg = "Select the folder that contains the Data Call EXCEL files:"

strPath = BrowseFolder(strBrowseMsg)

If strPath = "" Then
       MsgBox "No folder was selected.", vbOK, "No Selection"
       Exit Sub
 End If

 strFile = Dir(strPath & "\*.xls*")
 Do While Len(strFile) > 0
       strPathFile = strPath & "\" & strFile


  Set objApp = CreateObject("Excel.Application")
        objApp.Visible = False
 

  Set wb = ActiveWorkbook
'Cells Not To Lock
   wb.Sheets("Data Call").Range("H:H,I:I,J:J").Locked = False
  ActiveSheet.Protect Password:="123"
strFile = Dir()
Loop
Avatar of omgang
omgang
Flag of United States of America image

I don't see that you have objApp declared anywhere.  Do you have an Option Explicit statement at the top of your code module?

Try
Dim objApp = Excel.Application
before the set statement.

OM Gang
You declare xls but not objApp
OM Gang
No points, please.

SET xls = CreateObject("Excel.Application")
Lots of unhappiness in there waiting to bite you at the inopportune moment.
Either go with late binding
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")

and then all the other subsidiary items as objects
Dim xlBook As Object
Dim xlSheet As Object
Set xlBook = xlApp.Workbooks.Open("SomeValidPath)
Set xlSheet = xlBook.Sheets("someSheetName")


or go with early binding
'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)


But you are playing mix and match, which will trip you eventually.
You want a chain of objects, declared and then set, each one off another

But
Dim xls     As Excel.Application
   Dim wkb     As Excel.Workbook
   Dim wks     As Excel.Worksheet

That's early binding

Dim wb As Workbook
'This is early binding, but not to Excel.Workbook, leaving possible ambiguity


 Dim strPathFile As String, strFile As String, strPath As String
 Dim strTable As String, strBrowseMsg As String

   'On Error GoTo Err_Handler
 
 strBrowseMsg = "Select the folder that contains the Data Call EXCEL files:"

 strPath = BrowseFolder(strBrowseMsg)

 If strPath = "" Then
        MsgBox "No folder was selected.", vbOK, "No Selection"
        Exit Sub
  End If

  strFile = Dir(strPath & "\*.xls*")
  Do While Len(strFile) > 0
        strPathFile = strPath & "\" & strFile


'Now this is late bound
 Set objApp = CreateObject("Excel.Application")
         objApp.Visible = False
 
'And this is Excel code, and won't work error-free without referencing the top level object
'Set wb = objApp.ActiveWorkbook
   Set wb = ActiveWorkbook
 'Cells Not To Lock
    wb.Sheets("Data Call").Range("H:H,I:I,J:J").Locked = False
'Here again you have Excel code that won't work well without the top-level object
objApp.ActiveSheet.Protect Password:="123"
   ActiveSheet.Protect Password:="123"
 strFile = Dir()
 Loop


You can copy Excel VBA and paste it into Access, but you need to keep reminding Access that you are doing stuff to Excel and not itself.

Because you actually CAN use Excel functions to do stuff to Access when you want/need to.  All those fine things in formulas can get used in Access if you set it up right.

You are on the right track, but just not rigorous enough, yet :)
Avatar of shieldsco

ASKER

Error in line  wb.Sheets("Data Call").Range("H:H,I:I,J:J").Locked = False
User generated image
New Code:
  Dim xls     As Excel.Application
  Dim wkb     As Excel.Workbook
  Dim wks     As Excel.Worksheet
  Dim wb As Workbook

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String

  'On Error GoTo Err_Handler
 
strBrowseMsg = "Select the folder that contains the Data Call EXCEL files:"

strPath = BrowseFolder(strBrowseMsg)

If strPath = "" Then
       MsgBox "No folder was selected.", vbOK, "No Selection"
       Exit Sub
 End If

 strFile = Dir(strPath & "\*.xls*")
 Do While Len(strFile) > 0
       strPathFile = strPath & "\" & strFile
Set xls = CreateObject("Excel.Application")
  'Set objApp = CreateObject("Excel.Application")
        'objApp.Visible = False
 

  Set wb = ActiveWorkbook
'Cells Not To Lock
   wb.Sheets("Data Call").Range("H:H,I:I,J:J").Locked = False
  ActiveSheet.Protect Password:="123"
strFile = Dir()
Loop
There are problems here
 Set wb = ActiveWorkbook
 'Cells Not To Lock
    wb.Sheets("Data Call").Range("H:H,I:I,J:J").Locked = False
   ActiveSheet.Protect Password:="123"
 strFile = Dir()


1. This is wrong for Access
Set wb = ActiveWorkbook
You can't get away from needing to tell Access that this is an Excel object
Try this in certain situations
Set wb = xls.ActiveWorkbook

But if Excel is not visible and .UserControl = true, you won't necessarily HAVE an ActiveSheet!

Next, while you can work a chain of subsidiary objects, it doesn't play nice.
You have a sheet object
 Dim wks     As Excel.Worksheet
Set it appropriately
 set wks = xls.Worksheets("Data Call")
then use it
with wks
    .Range("H:H,I:I,J:J").Locked = False
    .Protect Password:="123"
End with


Start an Excel App
Open/Add a book
Go to a sheet of that book
do stuff with .Range, .Cells and other fun things
You have to keep the chain of objects linked and intact.
Trying to work
xls.Workbooks("some file").Sheets("someSheet").Range("SomeRange") is ALWAYS trouble.
Work with the lowest possible object (Sheet) whenever you can.
You are not working in Excel, so the nice syntactic sugar that lets you skip expressly identifying each object won't work.
Error in line : Set wks = xls.Worksheets("Data Call")User generated image
New Code:
 Dim xls     As Excel.Application
  Dim wkb     As Excel.Workbook
  Dim wks     As Excel.Worksheet
  Dim wb As Workbook

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String

  'On Error GoTo Err_Handler
 
strBrowseMsg = "Select the folder that contains the Data Call EXCEL files:"

strPath = BrowseFolder(strBrowseMsg)

If strPath = "" Then
       MsgBox "No folder was selected.", vbOK, "No Selection"
       Exit Sub
 End If

 strFile = Dir(strPath & "\*.xls*")
 Do While Len(strFile) > 0
       strPathFile = strPath & "\" & strFile
Set xls = CreateObject("Excel.Application")
  'Set objApp = CreateObject("Excel.Application")
        'objApp.Visible = False
 
Set wb = xls.ActiveWorkbook
  'Set wb = ActiveWorkbook
'Cells Not To Lock
Set wks = xls.Worksheets("Data Call")

With wks
     .Range("H:H,I:I,J:J").Locked = False
     .Protect Password:="123"
 End With




  ' wb.Sheets("Data Call").Range("H:H,I:I,J:J").Locked = False
 ' ActiveSheet.Protect Password:="123"
strFile = Dir()
Loop
I usually encounter that if there are orphan Excel instances hanging around.
Close all Excel instances.
Fire up the task manager
Are there Excel.exe instances STILL running.
Kill them.
Then try again.

When automating Excel, make it visible and usercontrol = true until you have ALL the bugs out!
No Excel instances running
Avatar of [ fanpages ]
[ fanpages ]

Set wks = xls.Worksheets("Data Call")

Should read

Set wks = wb .Worksheets("Data Call")

(Assuming the opened workbook contains a worksheet named [Data Call]).
Error in line:

Set wks = wb.Worksheets("Data Call")User generated image
I can see from what was posted above that these two lines need revising:

Set wb = xls.ActiveWorkbook
  'Set wb = ActiveWorkbook


After selecting a folder, no workbook is opened within the created "Excel.Application" object instance, so the object wb will not be initialised.

This is why the "Object variable or With block variable not set" error is encountered.

I think it would help (us all) if you post exactly what code you are (now) using with all the changes made, & what your code is supposed to achieve.

I am confused why the "Excel.Application" object is (re)created within the loop that sequentially returns each file in the folder selected.

In the meantime, this sample code may help you further:

Option Explicit
Public Sub Open_Workbook_Demonstration()

  Dim objExcel_Application                              As Object
  Dim objWorkbook                                       As Object
  
  Set objExcel_Application = CreateObject("Excel.Application")
  
  objExcel_Application.Visible = True
  
  Set objWorkbook = objExcel_Application.Workbooks.Open("d:\test.xlsx")
  
  MsgBox "Cell [A1] of [Data Call] worksheet in opened workbook: " & _
         vbCrLf & vbLf & _
         objWorkbook.Worksheets("Data Call").Cells(1&, "A").Value, _
         vbInformation Or vbOKOnly, _
         objWorkbook.Name
         
  objWorkbook.Close
  
  Set objWorkbook = Nothing
  
  objExcel_Application.Quit
  
  Set objExcel_Application = Nothing
  
End Sub

Open in new window


If testing this code, please change the "d:\test.xlsx" filename to match any of your own workbook files that contain a worksheet named [Data Call].
I'm trying to open the browse window (works) and select the folder were the xls files are located and then protect certain cells.

Current Code:

Private Sub NavigationButton18_Click()

  Dim xls     As Excel.Application
  Dim wks     As Excel.Worksheet
  Dim wb      As Excel.Workbook
 
  Dim objExcel_Application                              As Object
  Dim objWorkbook                                       As Object
 
 
 
 
 

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String

  'On Error GoTo Err_Handler
 
strBrowseMsg = "Select the folder that contains the Data Call EXCEL files:"

strPath = BrowseFolder(strBrowseMsg)

If strPath = "" Then
       MsgBox "No folder was selected.", vbOK, "No Selection"
       Exit Sub
 End If

 strFile = Dir(strPath & "\*.xls*")
 Do While Len(strFile) > 0
       strPathFile = strPath & "\" & strFile
       
  Set objExcel_Application = CreateObject("Excel.Application")
   objExcel_Application.Visible = True

  Set wb = ActiveWorkbook

Set wks = xls.Worksheets("Data Call")
'Cells Not To Lock
With wks
     .Range("H:H,I:I,J:J").Locked = False
     .Protect Password:="123"
 End With

  strFile = Dir()
Loop


End Sub
Ok fine.
Tested and working

Private Sub NavigationButton18_Click()

Dim xls As Object
Dim wb As Object
Dim wks As Object

Dim strPathFile As String
Dim strFile As String
Dim strPath As String
Dim strTable As String
Dim strBrowseMsg As String
Dim x As Integer

  'On Error GoTo Err_Handler
 
strBrowseMsg = "Select the folder that contains the Data Call EXCEL files:"
strPath = BrowseFolder(strBrowseMsg)

If strPath = "" Then
    MsgBox "No folder was selected.", vbOK, "No Selection"
    Exit Sub
End If

strFile = Dir(strPath & "\*.xls*")
Do While Len(strFile) > 0
    strPathFile = strPath & "\" & strFile

    Set xls = CreateObject("Excel.Application")
    xls.Visible = True
    xls.UserControl = True
    Set wb = xls.Workbooks.Open(strPathFile)
    For x = 1 To 3
        If wb.Worksheets(x).Name = "Call Data" Then
            Set wks = wb.Worksheets(x)
        End If
    Next x
    
    'Cells Not To Lock
    With wks
        .Range("H:H,I:I,J:J").Locked = False
        .Protect Password:="123"
    End With
strFile = Dir()
Loop

wb.Save
wb.Close
xls.Quit

MsgBox "done!"

End Sub

Open in new window


All late bound.
No mix and match
All objects daisy chained.
One oddness
Set wks = wb.Worksheets("Call Data") should work but doesn't.
Fine.  we'll find the index number of a sheet with that name

   For x = 1 To 3
        If wb.Worksheets(x).Name = "Call Data" Then
            Set wks = wb.Worksheets(x)
        End If
    Next x


And then it works.

Nick67
Error in line : If wb.Worksheets(x).Name = "Data Call" ThenUser generated image
My bad in assuming that there'd be three sheets.
Your bad in assuming they aren't already locked.

Try this

Private Sub NavigationButton18_Click()

Dim xls As Object
Dim wb As Object
Dim wks As Object

Dim strPathFile As String
Dim strFile As String
Dim strPath As String
Dim strTable As String
Dim strBrowseMsg As String
Dim x As Integer

  'On Error GoTo Err_Handler
 
strBrowseMsg = "Select the folder that contains the Data Call EXCEL files:"
strPath = BrowseFolder(strBrowseMsg)

If strPath = "" Then
    MsgBox "No folder was selected.", vbOK, "No Selection"
    Exit Sub
End If

strFile = Dir(strPath & "\*.xls*")
Do While Len(strFile) > 0
    strPathFile = strPath & "\" & strFile

    Set xls = CreateObject("Excel.Application")
    xls.Visible = True
    xls.UserControl = True
    Set wb = xls.Workbooks.Open(strPathFile)
    For x = 1 To wb.Worksheets.Count
        MsgBox x & " " & wb.Worksheets(x).Name
        If wb.Worksheets(x).Name = "Call Data" Then
            Set wks = wb.Worksheets(x)
        End If
    Next x
    
    'Cells Not To Lock
    With wks
        With .Range("H:H,I:I,J:J")
            If .Locked = True Then
                .Locked = False
            End If
        End With
        .Protect Password:="123"
    End With
strFile = Dir()
Loop

wb.Save
wb.Close

Open in new window

Error in line:  If wb.Worksheets(x).Name = "Data Call" Then

User generated imageUser generated image
Nothing is ever allowed to be easy.
Nothing.

Sample attached
shieldsco.mdb
Error in line:  If wb.Worksheets(x).Name = "Data Call" Then

User generated imageUser generated image
For reference I tried the .mdb on both 2010 and 2013 Access versions with the same error.
ASKER CERTIFIED SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent
Nick67 I going to open up a new question on how to unprotect using the same code
wks.unprotect should do it
https://msdn.microsoft.com/en-us/library/office/ff841143.aspx
you could reverse locking to  .Locked = true
but locking is only effective if protection is on.