PUBLIC VARIABLE for SELECTEDFILE

[Baine]

I have one sub that opens the file selection dialog. Then I want to copy
some cells from the Excel file that was just opened to the MasterData.XLS
file where the Module resides. I don't know what the file name might be but
I need to refer back to that file on several occations to copy all of the
cells. The data needed in not allways in the same row or column. I know
how to do the coding to locate the data and copy it. How do I refer to the
file that was opened in the first sub?

Option Explicit
Public selectedFile As String
' Public selectedFile As Workbook
' Brings up dialog to select invoice file to
' copy data to Master file.

Sub openInvoiceFile()
Dim selectedFile As String
Dim wbToOpen As Integer, wsCount As Integer
Dim objWsQBRfreeB As String
For wbToOpen = 1 To 1
'display dialog asking user to select a file
selectedFile = Application.GetOpenFilename("Files (*.xls),*.xls", , "Select
Invoice", , False)
'check if cancel selected then open
If selectedFile = "False" Then
MsgBox "You choose to interrupt loading files."
Exit For
Else
Workbooks.OpenText Filename:=selectedFile
End If
' Workbooks(2).Activate
Next
End Sub
--------------------------------------------------------------------------------
Sub CopyDATAtoMaster()
'
' CopyDATAtoMaster Macro
' Macro recorded 10/6/2006 by Baine-64
'
Range("B2").Select
Selection.Copy
Windows("S2 Master1.xls").Activate
Range("C5").Select
ActiveSheet.Paste
' Windows(selectedFile).Activate
' Set selectedFile = ActiveWorkbook
' selectedFile.Activate
End Sub

 

[Tom Ogilvy]

Option Explicit
Public selectedFile As String
' Public selectedFile As Workbook
' Brings up dialog to select invoice file to
' copy data to Master file.

Sub openInvoiceFile()
Dim selectedFileFullName As String
Dim wbToOpen As Integer, wsCount As Integer
Dim objWsQBRfreeB As String
For wbToOpen = 1 To 1
'display dialog asking user to select a file
selectedFileFullName = Application.GetOpenFilename("Files (*.xls),*.xls", ,
"Select
Invoice", , False)
'check if cancel selected then open
If selectedFile = "False" Then
MsgBox "You choose to interrupt loading files."
Exit For
Else
Workbooks.OpenText Filename:=selectedFileFullName
SelectedFile = ActiveWorkbook.Name
End If
' Workbooks(2).Activate
ThisWorkbook.Activate
Next
End Sub

 

[Baine]

Thanks for the help Tom. I tried your code and was unable to do any better
on the main question I had. How do I refer to the file that was opened in
the first sub when I use the second sub in the same module?

 

[Tom Ogilvy]

I change you second declarationof SelectFile in OpenInvoice to a different
variable name so it didn't block you public variable. And added code to
assign the name of the newly opened workbook to the public variable for
future use. I didn't alter your second procedure since it wasn't clear what
you wanted to do there. As written, it does nothing explicitely with the
public variable. I did miss changing one line in the corrected version of
your first routine. Here is a revision

Option Explicit
Public selectedFile As String
Public bk as Workbook

Sub openInvoiceFile()
Dim selectedFileFullName As String
Set Bk = ActiveWorkbook

selectedFileFullName = Application.GetOpenFilename( _
"Files (*.xls),*.xls", , "Select Invoice", , False)

If selectedFileFullName = "False" Then
MsgBox "You choose to interrupt loading files."
Exit For
Else
Workbooks.OpenText Filename:=selectedFileFullName
SelectedFile = ActiveWorkbook.Name
End If

ThisWorkbook.Activate
End Sub

Here is an example of how to use the variable.

Sub CopyDATAtoMaster()
workbooks(SelectedFile).Activate
worksheets(1).Activate
Range("B2").Select
Selection.Copy
Windows("S2 Master1.xls").Activate
Range("C5").Select
ActiveSheet.Paste
workbooks("SelectedFile).Activate
End Sub


or
Sub CopyDataToMaster1()
Workbooks(Selectedfile).Worksheets(1).Range("B2").Copy _
Destination:=Workbooks("S2 Master1.xls").Worksheets(1).Range("C5")
End Sub

of course I don't know where your code is located or how many workbooks are
open or which sheet in the workbook you want to use, so this is only a
guess.

Code could contain typos, but should give you a general idea of how to
proceed.

If you want to consolidate information from multiple workbooks and have
little knowledge of coding, perhaps you should look here:

http://www.rondebruin.nl/copy3.htm

 

[Baine]

Now that's working for me. Thanks! What is Bk used for in the code?
Public bk as Workbook
Set Bk = ActiveWorkbook

 

[Tom Ogilvy]

I didn't actually use it, but I could have <g>

instead of
Windows("S2 Master1.xls").Activate
I though I could do
bk.activate
but then I would have assumed that S2 Master1.xls was the activeworkbook
when you opened the new workbook and that would have assumed too much. So I
thought better of it, but didn't remove it from the code or the public
declaration.

 

[Baine]

Thanks! That does it.

I didn't actually use it, but I could have <g>

instead of
Windows("S2 Master1.xls").Activate
I though I could do
bk.activate
but then I would have assumed that S2 Master1.xls was the activeworkbook
when you opened the new workbook and that would have assumed too much. So
I
thought better of it, but didn't remove it from the code or the public
declaration.