Object varaible or With block variable not set?
 

I have this code (Thank you Bob Phillips):
Modified as so:

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

What is failing?

Object varaible or With block variable not set?
 

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." wrote in message
...
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?

Object varaible or With block variable not set?
 

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

"Rick S." wrote:

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:

Object varaible or With block variable not set?
 

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

Object varaible or With block variable not set?
 

Password issue resolved, thanks to Gary Kermidas.
The key seems to be passing both password arguments at the same time.

"Rick S." wrote:

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