Object varaible or With block variable not set?

R

Rick S.

I have this code (Thank you Bob Phillips):
Code: 
Sub LoopFolders()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("c:\MyTest")

For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path
'<<<<< run macro here on Activeworkbook
Activeworkbook.Close SaveChanges:=False
End If
Next file
Set oFSO = Nothing

End Sub

Modified as so:

Code: 
Sub OpenWorkbooks()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object

'Modified macro from Bob Phillips
'Application.ScreenUpdating = False 'temporarily disabled

ThisWorkbook.Worksheets("INSTRUCTIONS").Activate 'select cells from
"INSTRUCTIONS" workbook
Cells.Copy 'copy all cells

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("U:\VBA\Test Folder")

'For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then  'ERROR IS AT THIS LINE
Workbooks.Open Filename:=file.Path
'<<<<< run macro here on Activeworkbook

Worksheets("INSTRUCTIONS").Activate 'select worksheet
"INSTRUCTIONS"
ActiveSheet.Paste 'paste cells from original worksheet

ActiveWorkbook.Close SaveChanges:=True
End If
'Next file
Set oFSO = Nothing

End Sub

Neither one will work and I get the follwoing error:
"Object varaible or With block variable not set"

What is failing?
 
C

Chip Pearson

From a quick look at your code, it seems you have commented out the line

'For Each file In Folder.Files

Since it is this line of code that sets the 'file' variable to a specific
File object, you will get an "object not set" error because the variable
'file' hasn't been set to any File in particular. Somehow, you need to set
'file' to a File object. Perhaps

Set file = oFSO.GetFile("C:\Whatever\Test.xls")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
R

Rick S.

Unbelievable!
I looked at this for an hour! lol
I also noticed the same issue at the "Next File" line.

Chip Pearson for President!!!
:beer:

Chip Pearson said:
From a quick look at your code, it seems you have commented out the line

'For Each file In Folder.Files

Since it is this line of code that sets the 'file' variable to a specific
File object, you will get an "object not set" error because the variable
'file' hasn't been set to any File in particular. Somehow, you need to set
'file' to a File object. Perhaps

Set file = oFSO.GetFile("C:\Whatever\Test.xls")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

Rick S. said:
I have this code (Thank you Bob Phillips):
Code: 
Sub LoopFolders()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("c:\MyTest")

For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path
'<<<<< run macro here on Activeworkbook
Activeworkbook.Close SaveChanges:=False
End If
Next file
Set oFSO = Nothing

End Sub

Modified as so:

Code: 
Sub OpenWorkbooks()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object

'Modified macro from Bob Phillips
'Application.ScreenUpdating = False 'temporarily disabled

ThisWorkbook.Worksheets("INSTRUCTIONS").Activate 'select cells from
"INSTRUCTIONS" workbook
Cells.Copy 'copy all cells

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("U:\VBA\Test Folder")

'For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then  'ERROR IS AT THIS LINE
Workbooks.Open Filename:=file.Path
'<<<<< run macro here on Activeworkbook

Worksheets("INSTRUCTIONS").Activate 'select worksheet
"INSTRUCTIONS"
ActiveSheet.Paste 'paste cells from original worksheet

ActiveWorkbook.Close SaveChanges:=True
End If
'Next file
Set oFSO = Nothing

End Sub

Neither one will work and I get the follwoing error:
"Object varaible or With block variable not set"

What is failing?
Click to expand...
Click to expand...
 
R

Rick S.

Seems to be another underlying issue. The code stops running after
"Cells.Copy 'copy all cells"

I added two "MsgBox" to show the true or false status of the "cells.copy"
event and for after a new file is opened. The first message box prompts with
true and then the code exits, at least it appears to exit.

Code: 
Sub OpenWorkbooks()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim NewSel As Variant

'Modified macro from Bob Phillips
'Application.ScreenUpdating = False 'temporarily disabled

ThisWorkbook.Worksheets("INSTRUCTIONS").Activate 'select cells from
"INSTRUCTIONS" workbook
Cells.Copy 'copy all cells
NewSel = Cells.Copy
MsgBox NewSel & " 1st Test" 'for testing (true or false)

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder("U:\VBA\Test Folder")

For Each file In Folder.Files
If file.Type Like "*Microsoft Excel*" Then
Workbooks.Open Filename:=file.Path, WriteResPassword:=“xxxxâ€

'Worksheets("INSTRUCTIONS").Activate 'select worksheet
"INSTRUCTIONS"
'ActiveSheet.Paste 'paste cells from original worksheet
MsgBox NewSel & " 2nd Test" 'for testing (true or false)
ActiveWorkbook.Close SaveChanges:=True
End If
Next file
Set oFSO = Nothing

End Sub

I should see a second message box with either True or false and the text
"2nd Test" while the new file is open (or thru all excel files in the path).

Is there Network drive name issues with Excel 2007!? Do I have to type the
actual network name?
Example: "Server\MyNetwork\VBA\Test Folder"

P.S.
I forgot to mention I am using Excel 2007 (hate it so far). :shrug:
 
R

Rick S.

I believe I have narrowed this down to the following line of code:
If file.Type Like "*Microsoft Excel*" Then

I think that excel is not recognizing my files as "Microsoft Excel"!?
 
R

Rick S.

Moving forward...
So far I found that the code:
"If file.Type Like "*Microsoft Excel*" Then" is not functioning as planned
and now my new code functions and recognizes the files in the folder.
:
"For Each file In Folder.Files
sFileName = file
If Right(sFileName, 3) = "xls" Then"

Butt, now I can't seem to pass the password to the file that will be opened
without user intervention, I am prompted with a dialog box to enter a
password, select Read Only or select Cancel.
"Workbooks.Open FileName:=file.Path, Password:=“xxxxâ€"
The above simply opens the file with the dialog box for a password.

I have also tried "Workbooks.Open FileName:=file.Path,
WriteResPassword:=“xxxxâ€" which fails to recognize the password.

It appears I only need to resolve the password issue, I have thousands of
files to iterate thru and retyping the password would be something I would
whine about for generations to come.

Any help other than my own would be aprreciated LOL
 
R

Rick S.

Password issue resolved, thanks to Gary Kermidas.
try this

Workbooks.Open Filename:="I:\Aging\Month End\CMT Monthly Aging 07.xls", _
Password:="atervse", WriteResPassword:="atervse"
Click to expand...

The key seems to be passing both password arguments at the same time.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

prompt for path to loop through files 5
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Working outlook via vba code with xlsx - error 0
copy content from Multiple file to one file 3
Word macro that can call cells out of Excel for a find and replace 0
Error 58, file already exists - why? 7
Runtime error 91: Object variable or With block variable not set 8
Path & File Name 3

Top