Object varaible or With block variable not set?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rick S. - 25 Sep 2007 20:36 GMT

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
[/code]

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
[/code]

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

What is failing?

Reply to this Message

Chip Pearson - 25 Sep 2007 22:28 GMT

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")

Signature

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

>I have this code (Thank you Bob Phillips):
> [code]
[quoted text clipped - 61 lines]
>
> What is failing?

Reply to this Message

Rick S. - 25 Sep 2007 23:00 GMT

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:

> From a quick look at your code, it seems you have commented out the line
>
[quoted text clipped - 72 lines]
> >
> > What is failing?

Reply to this Message

Rick S. - 25 Sep 2007 23:42 GMT

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
[/code]

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:

Reply to this Message

Rick S. - 26 Sep 2007 16:40 GMT

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"!?

> Seems to be another underlying issue. The code stops running after
> "Cells.Copy 'copy all cells"
[quoted text clipped - 48 lines]
> P.S.
> I forgot to mention I am using Excel 2007 (hate it so far). :shrug:

Reply to this Message

Rick S. - 26 Sep 2007 18:48 GMT

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

Reply to this Message

Rick S. - 26 Sep 2007 19:20 GMT

Password issue resolved, thanks to Gary Kermidas.
[quote]
try this

Workbooks.Open Filename:="I:\Aging\Month End\CMT Monthly Aging 07.xls", _
Password:="atervse", WriteResPassword:="atervse"
[/quote]

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

> Moving forward...
> So far I found that the code:
[quoted text clipped - 19 lines]
>
> Any help other than my own would be aprreciated LOL

Reply to this Message