Help with Workbooks.Open

A

arkbdc

New Member
Joined
Oct 5, 2010
Messages
16
Hi -

I have a laptop that runs both Excel 2003 and Excel 2007. I wrote all code in 2003. My client is using 2003 but his code seems to stop after a certain point and I can get it to work fine on my computer....???!

The code stops for him at...
Workbooks. Open FileName:=
"S:\Sales Commissions\Tim_Abrams.xls", Password:="suckit"


HERE IS THE FULL CODE:

Code: 
Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range
Dim LastRow As Long
 
'Clear contents on spreadsheet
Rows("2:65536").Select
Selection.ClearContents
 
' Create a new workbook and set a variable to the first sheet.
Set SummarySheet = ActiveWorkbook.Sheets("Data")
 
' Modify this folder path to point to the files you want to use.
FolderPath = "S:\STATEMENTS\Current CoreNet Statements\"
 
' NRow keeps track of where to insert new rows in the destination
workbook.
NRow = 2
 
' Call Dir the first time, pointing it to all Excel files in the folder
path.
FileName = Dir(FolderPath & "FY*.xl*")
 
' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
'
 
Set WorkBk = Workbooks.Open(FolderPath & FileName,
WriteResPassword:="pw101")
 
 
' Set the cell in column A to be the file name.
SummarySheet.Range("A" & NRow).Value = FileName
 
' Set the source range to be A9 through C9.
' Modify this range for your workbooks.
' It can span multiple rows.
 
'Find last row in statement spreadsheet
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
 
 
Set SourceRange = WorkBk.Worksheets("Filters").Range("A9:AL" &
LastRow)
 
' Set the destination range to start at column B and
' be the same size as the source range.
Set DestRange = SummarySheet.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)
 
' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value
 
' Increase NRow so that we know where to copy data next.
NRow = NRow + DestRange.Rows.Count
 
' Close the source workbook without saving changes.
'
 
WorkBk.Close SaveChanges:=False
 
' Use Dir to get the next file name.
FileName = Dir()
Loop
 
 
 
 
' Modify this folder path to point to the files you want to use.
FolderPath = "S:\STATEMENTS\IREM\"
 
' NRow keeps track of where to insert new rows in the destination
workbook.
NRow = 2
 
' Call Dir the first time, pointing it to all Excel files in the folder
path.
FileName = Dir(FolderPath & "FY*.xl*")
 
' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
'
Set WorkBk = Workbooks.Open(FolderPath & FileName,
WriteResPassword:="pw101")
 
 
' Set the cell in column A to be the file name.
SummarySheet.Range("A" & NRow).Value = FileName
 
' Set the source range to be A9 through C9.
' Modify this range for your workbooks.
' It can span multiple rows.
 
'Find last row in statement spreadsheet
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
 
 
Set SourceRange = WorkBk.Worksheets("Filters").Range("A9:AL" &
LastRow)
 
' Set the destination range to start at column B and
' be the same size as the source range.
Set DestRange = SummarySheet.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)
 
' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value
 
' Increase NRow so that we know where to copy data next.
NRow = NRow + DestRange.Rows.Count
 
' Close the source workbook without saving changes.
' WorkBk.Protect Password:="pw101"
 
WorkBk.Close SaveChanges:=False
 
' Use Dir to get the next file name.
FileName = Dir()
Loop
 
 
 
 
 
 
 
' Modify this folder path to point to the files you want to use.
FolderPath = "S:\Alzheimer's Association Orange County\AAOC Shared
Folder\"
 
' NRow keeps track of where to insert new rows in the destination
workbook.
NRow = 2
 
' Call Dir the first time, pointing it to all Excel files in the folder
path.
FileName = Dir(FolderPath & "FY*.xl*")
 
' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
' WorkBk.Unprotect Password:="pw101"
 
Set WorkBk = Workbooks.Open(FolderPath & FileName,
WriteResPassword:="pw101")
 
 
' Set the cell in column A to be the file name.
SummarySheet.Range("A" & NRow).Value = FileName
 
' Set the source range to be A9 through C9.
' Modify this range for your workbooks.
' It can span multiple rows.
 
'Find last row in statement spreadsheet
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
 
 
Set SourceRange = WorkBk.Worksheets("Filters").Range("A9:AL" &
LastRow)
 
' Set the destination range to start at column B and
' be the same size as the source range.
Set DestRange = SummarySheet.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)
 
' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value
 
' Increase NRow so that we know where to copy data next.
NRow = NRow + DestRange.Rows.Count
 
' Close the source workbook without saving changes.
' WorkBk.Protect Password:="pw101"
 
WorkBk.Close SaveChanges:=False
 
' Use Dir to get the next file name.
FileName = Dir()
Loop
 
 
 
 
 
 
 
 
 
 
' Call AutoFit on the destination sheet so that all
' data is readable.
' SummarySheet.Columns.AutoFit
'
'Fill in blanks for Source column
 
Columns("A:A").Select
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
 
 
Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
 
'Delete row 2
' Rows("2:2").Select
' Selection.Delete Shift:=xlUp
 
'Filter on Tim Abrams - c/p
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
 
Selection.AutoFilter Field:=16, Criteria1:="TA"
Cells.Select
Range("H1").Activate
Selection.Copy
 
Workbooks.Open FileName:= _
"S:\Sales Commissions\Tim_Abrams.xls", Password:="suckit"
Sheets("Data").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
 
Workbooks("Tim_Abrams").Close SaveChanges:=True
 
Workbooks("MASTER").Activate
'
Application.CutCopyMode = False
 
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter
'
''Filter on Mike Mooney - c/p
'
Selection.AutoFilter Field:=16, Criteria1:="MM"
Cells.Select
Range("H1").Activate
Selection.Copy
'
Workbooks.Open FileName:= _
"S:\Sales Commissions\Mike_Mooney.xls", Password:="mm1552"
Sheets("Data").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
 
Workbooks("Mike_Mooney").Close SaveChanges:=True
Workbooks("MASTER").Activate
 
Application.CutCopyMode = False
 
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter
'
'' Filter on Anne Hardy - c/p
Selection.AutoFilter Field:=16, Criteria1:="AH"
Cells.Select
Range("H1").Activate
Selection.Copy
'
Workbooks.Open FileName:= _
"S:\Sales Commissions\Anne_Hardy.xls", Password:="111588"
Sheets("Data").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
 
Workbooks("Anne_Hardy").Close SaveChanges:=True
Workbooks("MASTER").Activate
 
Application.CutCopyMode = False
 
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter
'
 
'Filter on Matt Dirks - c/p
Selection.AutoFilter Field:=16, Criteria1:="MD"
Cells.Select
Range("H1").Activate
Selection.Copy
'
Workbooks.Open FileName:= _
"S:\Sales Commissions\Matt_Dirks.xls", Password:="janseN01!"
Sheets("Data").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
 
Workbooks("Matt_Dirks").Close SaveChanges:=True
Workbooks("MASTER").Activate
 
Application.CutCopyMode = False
 
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter
 
' Filter on Kim Wentik - c/p
Selection.AutoFilter Field:=16, Criteria1:="KW"
Cells.Select
Range("H1").Activate
Selection.Copy
'
Workbooks.Open FileName:= _
"S:\Sales Commissions\Kim_Wentik.xls", Password:="Matthew96"
Sheets("Data").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
 
Workbooks("Kim_Wentik").Close SaveChanges:=True
Workbooks("MASTER").Activate
 
Application.CutCopyMode = False
 
Rows("1:1").Select
Selection.AutoFilter
' Selection.AutoFilter
 
 ActiveWorkbook.Save
  
Range("A1").Select
End Sub
 
Last edited by a moderator:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Hello arkbdc,

The first thing I would check on the client's computer is the file path. I see no checks for this file path in the code. Does the path exist?
 
Upvote 0
I did check that and everything looks good.

Any other ideas? Thanks very much for the help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Power Query - Data Refresh Error in Macro
Replies
3
Views
178
bobsan42
bobsan42
C
  • Solved
Macro works for one file, does not work for all files in the directory
2
Replies
13
Views
554
Alex Blakenburg
A
J
  • Question
VBA Script - Can't figure out one error...
2
Replies
15
Views
284
Peter_SSs
Peter_SSs
G
  • Question
Open workbooks in folder and run Macro in each book
Replies
7
Views
403
glynn1969
G
G
  • Solved
Change Macro to import files from hyperlinks
2
Replies
12
Views
464
GarnesGambit
G

Forum statistics

Threads
1,215,453
Messages
6,124,918
Members
449,195
Latest member
Stevenciu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top