Open file(2) from file(1) and then close file(1)

  • Thread starter Thread starter Alberto Ast
  • Start date Start date
A

Alberto Ast

I need to open a file(2) when I open file(1) and then close file(1)
File(1) name can change so I need variables.

sequence will be
Open File(1)
Open File(2)... I do it by the following macro:

Public LaunchFile As String 'in this public variable I want to keep
file(1) name
Private Sub Workbook_Open()
Dim lDir, lFile, lFileMtr As String
lDir = Sheets("launch").Range("B2") & "\" 'direction of my file to open
lFile = Sheets("launch").Range("B3") & ".xls" 'file name to openm
lFileMtr = lDir & lFile 'complete string of file(2)
LaunchFile = ThisWorkbook.Name 'filename file(1)
Workbooks.Open Filename:=lFileMtr, ReadOnly:=True
End Sub

then on file(2) I have
Private Sub Workbook_Open()
msgbox LaunchFile 'I did it to be sure value stays but it is blank so
next line fail.
workbooks(LaunchFile).close false
End Sub

Any idea where am I failing?
 
Barb was partially right/ Here is the statement from VBA help file.

Variables declared using the Public statement are available to all
procedures in all modules in all applications unless Option Private Module
is in effect; in which case, the variables are public only within the
project in which they reside.

Caution The Public statement can't be used in a class module to declare a
fixed-length string variable.
 
I got the same results. The variable was empty when the second workbook
opened. So, if you go to the VBA help file for "Option Private Module", the
very last sentence in the last parragraph says you cannot reference across
projects in Visual Basic. Now that confuses me, the way it is written into
that paragraph. There is no menu selection for the Option Private Module,
it is VBA access only.
 
Thanks you all for your inputs, K_Macd made me laugh...
I was able to get away with what I needed... actually was a series of three
files. I did the following and it worked fine

Public ElectronicFile, LaunchFile As String
Public wElectronicFile, wLaunchFile As Workbook

Private Sub Workbook_Open()
Dim DevDir, DevFile, DevFileMtr As String
Dim lDir, lFile, lFileMtr As String
Application.ScreenUpdating = False

ElectronicFile = ThisWorkbook.Name

lDir = Sheets("Electronic").Range("B2") & "\"
lFile = Sheets("Electronic").Range("B3") & ".xls"
lFileMtr = lDir & lFile

Workbooks.Open Filename:=lFileMtr, ReadOnly:=True
Set wLaunchFile = Workbooks(lFile)

DevDir = wLaunchFile.Sheets("Launch").Range("B2") & "\"
DevFile = wLaunchFile.Sheets("Launch").Range("B3") & ".xls"
DevFileMtr = DevDir & DevFile

Workbooks.Open Filename:=DevFileMtr, ReadOnly:=True

wLaunchFile.Close False
Workbooks(ElectronicFile).Close False
Application.ScreenUpdating = True

End Sub
 
Hi Alberto, Thanks for posting back with your solution. That helps others
with similar problems.



Alberto Ast said:
Thanks you all for your inputs, K_Macd made me laugh...
I was able to get away with what I needed... actually was a series of
three
files. I did the following and it worked fine

Public ElectronicFile, LaunchFile As String
Public wElectronicFile, wLaunchFile As Workbook

Private Sub Workbook_Open()
Dim DevDir, DevFile, DevFileMtr As String
Dim lDir, lFile, lFileMtr As String
Application.ScreenUpdating = False

ElectronicFile = ThisWorkbook.Name

lDir = Sheets("Electronic").Range("B2") & "\"
lFile = Sheets("Electronic").Range("B3") & ".xls"
lFileMtr = lDir & lFile

Workbooks.Open Filename:=lFileMtr, ReadOnly:=True
Set wLaunchFile = Workbooks(lFile)

DevDir = wLaunchFile.Sheets("Launch").Range("B2") & "\"
DevFile = wLaunchFile.Sheets("Launch").Range("B3") & ".xls"
DevFileMtr = DevDir & DevFile

Workbooks.Open Filename:=DevFileMtr, ReadOnly:=True

wLaunchFile.Close False
Workbooks(ElectronicFile).Close False
Application.ScreenUpdating = True

End Sub


JLGWhiz said:
I got the same results. The variable was empty when the second workbook
opened. So, if you go to the VBA help file for "Option Private Module",
the
very last sentence in the last parragraph says you cannot reference
across
projects in Visual Basic. Now that confuses me, the way it is written
into
that paragraph. There is no menu selection for the Option Private
Module,
it is VBA access only.


"K_Macd" <kmacdonald "A_T" activ8 ''D O T'' net [ST OP] au> wrote in
message
Then why the hell did the the good folk at Microsoft ever use the term
'public' ?
--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


:

IIRC, public variables are valid within a project, not from project to
project.
--
HTH,

Barb Reinhardt



:

I need to open a file(2) when I open file(1) and then close file(1)
File(1) name can change so I need variables.

sequence will be
Open File(1)
Open File(2)... I do it by the following macro:

Public LaunchFile As String 'in this public variable I want to
keep
file(1) name
Private Sub Workbook_Open()
Dim lDir, lFile, lFileMtr As String
lDir = Sheets("launch").Range("B2") & "\" 'direction of my
file
to open
lFile = Sheets("launch").Range("B3") & ".xls" 'file name to
openm
lFileMtr = lDir & lFile 'complete string of file(2)
LaunchFile = ThisWorkbook.Name 'filename file(1)
Workbooks.Open Filename:=lFileMtr, ReadOnly:=True
End Sub

then on file(2) I have
Private Sub Workbook_Open()
msgbox LaunchFile 'I did it to be sure value stays but it is
blank
so
next line fail.
workbooks(LaunchFile).close false
End Sub

Any idea where am I failing?
Click to expand...


.
Click to expand...
Click to expand...
 
I believe I can not rate my post my self so I will rate your post so other
can refer to it and go back to my post.

JLGWhiz said:
Hi Alberto, Thanks for posting back with your solution. That helps others
with similar problems.



Alberto Ast said:
Thanks you all for your inputs, K_Macd made me laugh...
I was able to get away with what I needed... actually was a series of
three
files. I did the following and it worked fine

Public ElectronicFile, LaunchFile As String
Public wElectronicFile, wLaunchFile As Workbook

Private Sub Workbook_Open()
Dim DevDir, DevFile, DevFileMtr As String
Dim lDir, lFile, lFileMtr As String
Application.ScreenUpdating = False

ElectronicFile = ThisWorkbook.Name

lDir = Sheets("Electronic").Range("B2") & "\"
lFile = Sheets("Electronic").Range("B3") & ".xls"
lFileMtr = lDir & lFile

Workbooks.Open Filename:=lFileMtr, ReadOnly:=True
Set wLaunchFile = Workbooks(lFile)

DevDir = wLaunchFile.Sheets("Launch").Range("B2") & "\"
DevFile = wLaunchFile.Sheets("Launch").Range("B3") & ".xls"
DevFileMtr = DevDir & DevFile

Workbooks.Open Filename:=DevFileMtr, ReadOnly:=True

wLaunchFile.Close False
Workbooks(ElectronicFile).Close False
Application.ScreenUpdating = True

End Sub


JLGWhiz said:
I got the same results. The variable was empty when the second workbook
opened. So, if you go to the VBA help file for "Option Private Module",
the
very last sentence in the last parragraph says you cannot reference
across
projects in Visual Basic. Now that confuses me, the way it is written
into
that paragraph. There is no menu selection for the Option Private
Module,
it is VBA access only.


"K_Macd" <kmacdonald "A_T" activ8 ''D O T'' net [ST OP] au> wrote in
message
Then why the hell did the the good folk at Microsoft ever use the term
'public' ?
--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


:

IIRC, public variables are valid within a project, not from project to
project.
--
HTH,

Barb Reinhardt



:

I need to open a file(2) when I open file(1) and then close file(1)
File(1) name can change so I need variables.

sequence will be
Open File(1)
Open File(2)... I do it by the following macro:

Public LaunchFile As String 'in this public variable I want to
keep
file(1) name
Private Sub Workbook_Open()
Dim lDir, lFile, lFileMtr As String
lDir = Sheets("launch").Range("B2") & "\" 'direction of my
file
to open
lFile = Sheets("launch").Range("B3") & ".xls" 'file name to
openm
lFileMtr = lDir & lFile 'complete string of file(2)
LaunchFile = ThisWorkbook.Name 'filename file(1)
Workbooks.Open Filename:=lFileMtr, ReadOnly:=True
End Sub

then on file(2) I have
Private Sub Workbook_Open()
msgbox LaunchFile 'I did it to be sure value stays but it is
blank
so
next line fail.
workbooks(LaunchFile).close false
End Sub

Any idea where am I failing?





.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top