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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
You must log in or register to reply here.

Similar threads

D
  • Solved
Merge files and keep source formatting.
Replies
9
Views
323
Alex Blakenburg
A
B
  • Question Question
Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date
Replies
1
Views
150
sijpie
sijpie
T
  • Solved
Open all files in a folder and run macro
Replies
3
Views
635
Trebor76
T
J
  • Question Question
Append data to master file
Replies
6
Views
335
Alex Blakenburg
A
F
  • Solved
VBA to open all files in a folder, put the file's name in a cell and copy it to another opened workbook
Replies
5
Views
1K
feni1388
F

Forum statistics

Threads
1,223,714
Messages
6,174,051
Members
452,542
Latest member
Bricklin

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