shieldsco
asked on
Access variable not defined error
I'm using the code below and get an error in "Set objApp = CreateObject("Excel.Applic ation")
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 objApp = CreateObject("Excel.Applic ation")
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
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 objApp = CreateObject("Excel.Applic
objApp.Visible = False
Set wb = ActiveWorkbook
'Cells Not To Lock
wb.Sheets("Data Call").Range("H:H,I:I,J:J"
ActiveSheet.Protect Password:="123"
strFile = Dir()
Loop
You declare xls but not objApp
OM Gang
OM Gang
No points, please.
SET xls = CreateObject("Excel.Applic ation")
SET xls = CreateObject("Excel.Applic
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.Applic ation")
and then all the other subsidiary items as objects
Dim xlBook As Object
Dim xlSheet As Object
Set xlBook = xlApp.Workbooks.Open("Some ValidPath)
Set xlSheet = xlBook.Sheets("someSheetNa me")
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.Applic ation")
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 :)
Either go with late binding
Dim xlApp As Object
Set xlApp = CreateObject("Excel.Applic
and then all the other subsidiary items as objects
Dim xlBook As Object
Dim xlSheet As Object
Set xlBook = xlApp.Workbooks.Open("Some
Set xlSheet = xlBook.Sheets("someSheetNa
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.Applic
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"
'Here again you have Excel code that won't work well without the top-level object
objApp.ActiveSheet.Protect
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 :)
ASKER
Error in line wb.Sheets("Data Call").Range("H:H,I:I,J:J" ).Locked = False
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.Applic ation")
'Set objApp = CreateObject("Excel.Applic ation")
'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
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.Applic
'Set objApp = CreateObject("Excel.Applic
'objApp.Visible = False
Set wb = ActiveWorkbook
'Cells Not To Lock
wb.Sheets("Data Call").Range("H:H,I:I,J:J"
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").Lock ed = 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("So meRange") 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.
Set wb = ActiveWorkbook
'Cells Not To Lock
wb.Sheets("Data Call").Range("H:H,I:I,J:J"
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").Lock
.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")
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.
ASKER
Error in line : Set wks = xls.Worksheets("Data Call")
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.Applic ation")
'Set objApp = CreateObject("Excel.Applic ation")
'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").Lock ed = 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
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.Applic
'Set objApp = CreateObject("Excel.Applic
'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").Lock
.Protect Password:="123"
End With
' wb.Sheets("Data Call").Range("H:H,I:I,J:J"
' 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!
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!
ASKER
No Excel instances running
Set wks = xls.Worksheets("Data Call")
Should read
Set wks = wb .Worksheets("Data Call")
(Assuming the opened workbook contains a worksheet named [Data Call]).
Should read
Set wks = wb .Worksheets("Data Call")
(Assuming the opened workbook contains a worksheet named [Data Call]).
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:
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].
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
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].
ASKER
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.Applic ation")
objExcel_Application.Visib le = True
Set wb = ActiveWorkbook
Set wks = xls.Worksheets("Data Call")
'Cells Not To Lock
With wks
.Range("H:H,I:I,J:J").Lock ed = False
.Protect Password:="123"
End With
strFile = Dir()
Loop
End Sub
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.Applic
objExcel_Application.Visib
Set wb = ActiveWorkbook
Set wks = xls.Worksheets("Data Call")
'Cells Not To Lock
With wks
.Range("H:H,I:I,J:J").Lock
.Protect Password:="123"
End With
strFile = Dir()
Loop
End Sub
Ok fine.
Tested and working
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
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
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
My bad in assuming that there'd be three sheets.
Your bad in assuming they aren't already locked.
Try this
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
ASKER
For reference I tried the .mdb on both 2010 and 2013 Access versions with the same error.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent
ASKER
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.
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.
Try
Dim objApp = Excel.Application
before the set statement.
OM Gang