It isn't called Book_Open, it is called Workbook_Open, but it doesn't have
any arguments, it is an event procedure that runs automatically when you
open the workbook. To open a workbook, you just run
Workbooks.Open Filename:="some file.xls"
--
__________________________________
HTH
Bob
Okay I changed the name to just Book_Open. It still doesn't work. I
want to pass two variables to this Book_Open procedure....what am I
doing wrong?- Hide quoted text -
- Show quoted text -
Okay sorry...I should have given more details...
I have a separate procedure that is creating a filesystemobject and
then running through every file in a folder and checking to see if
there is a file with a specific name. If there is not, it creates it,
copies data into it, and then closes it. If there is, then it opens
it, appends data to the end of the file, and closes it, saving
changes. I have two arguments I want to pass to this procedure. One
is the file name to check for. The second argument I want to pass to
it is the RangeToCopy...Here is my code...
Sorry, I didn't mean to make you think I was trying to use the
Workbook_Open Procedure...I have never used that so I didn't recognize
the fact that it is already designated for something else...I am
simply trying to modify a custom procedure that opens/creates a
specific workbook based on two variables I pass to it...
Private Sub Sheet_Open(ByVal FileName As String, ByVal CutRange As
Range)
Dim RTA As Range
Dim RTC As Range
Dim Directory As String
Dim D As String
Dim FolderPath As String
Dim ObjFSO As Object
Dim ObjectFolder As Object
Dim ColFiles As Object
Dim ObjFile As Object
Dim T As Integer
Application.DisplayAlerts = False
D = Date
D = Application.WorksheetFunction.Substitute(D, "/", ".")
T = 0
FolderPath = "Q:\Operations\Spread Data\"
Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set objfolder = ObjFSO.getfolder(FolderPath)
Set ColFiles = objfolder.Files
FileName = FileName & " " & D & ".xlsx"
For Each ObjFile In ColFiles
Select Case ObjFile.Name
Case Is = FileName
Workbooks.Open FileName:=FolderPath & FileName
Set RangeToAppend =
Workbooks(FileName).Sheets(1).Range("B2").End(xlDown).Offset(1, 0)
Workbooks("DDE-Sample.xlsm").Activate
Set RangeToCopy = Range(CutRange, CutRange.End(xlDown))
Set RangeToCopy = Range(RangeToCopy, RangeToCopy.Offset(0,
4))
RangeToCopy.Cut
Workbooks(FileName).Activate
RangeToAppend.Select
ActiveSheet.Paste
Workbooks(FileName).Close savechanges:=True,
FileName:=FolderPath & FileName
T = 1
End Select
Next
Select Case T
Case Is = 0
Workbooks.Add
ActiveWorkbook.SaveAs FileName:=FolderPath & FileName
Set RangeToAppend = Workbooks(FileName).Sheets(1).Range("B2")
Workbooks("DDE-Sample.xlsm").Activate
Set RangeToCopy = Range(CutRange, CutRange.End(xlDown))
Set RangeToCopy = Range(RangeToCopy, RangeToCopy.Offset(0, 4))
RangeToCopy.Cut
Workbooks(FileName).Activate
RangeToAppend.Select
ActiveSheet.Paste
Workbooks(FileName).Sheets(1).Columns("B:F").Select
Workbooks(FileName).Sheets(1).Columns("B:F").EntireColumn.AutoFit
Workbooks(FileName).Sheets(1).Cells.Font.Size = 10
Workbooks(FileName).Close savechanges:=True,
FileName:=FolderPath & FileName
End Select
Application.DisplayAlerts = True
End Sub