| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object varaible or With block variable not set?
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:
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
"Object varaible or With block variable not set" What is failing? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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: |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Object varaible or With block variable not set?
Password issue resolved, thanks to Gary Kermidas.
Quote:
"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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with Error 'object variable or with block variable not set' | Excel Programming | |||
| Run-Time error '91': Object variable of With block variable not set | Excel Programming | |||
| Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
| Cells.Find error Object variable or With block variable not set | Excel Programming | |||
| Pivot Table - Object variable or with block variable not set? | Excel Programming | |||
Linear Mode
