Yikes, think I answered my own question #2. If I change
Else
Set WB = Workbooks(strFileName)
to:
Else
Set WB = Workbooks(Mid(strFileName, InStrRev(strFileName, "\") + 1,
256))
it pulls from the open file and leaves it open.
"Paige" wrote:
> Thanks, Jim - works great! Couple of questions:
> 1) Set WB = Workbooks(Mid(strFileName, InStrRev(strFileName, "\") + 1,
> 256))...am not sure I understand what you're doing here...extracting the name
> only (versus the entire path) of the workbook?
> 2) If the file is already open, can I just extract the data and leave the
> file open? I don't want to automatically close the file because they may
> need to save it first or keep it open. Tried to modify it as follows, but
> get a subscript out of range:
>
> Dim WB As Workbook
> Dim strFileName As String
>
> strFileName = Application.GetOpenFilename(FileFilter:="All
> Files(*.*),*.xls,All Files (*.*),*.*")
>
> On Error Resume Next
> Set WB = Workbooks(Mid(strFileName, InStrRev(strFileName, "\") + 1, 256))
> On Error GoTo 0
>
> If WB Is Nothing Then
> Set WB = Workbooks.Open(strFileName, True, True)
> Application.ScreenUpdating = False
> With ThisWorkbook.Worksheets("Input and P&L")
> .Range("E9").Value = WB.Worksheets("Input and
> P&L").Range("e9").Value
> .Range("E10").Value = WB.Worksheets("Input and
> P&L").Range("e10").Value
> End With
> WB.Close False
> Set WB = Nothing
> Application.ScreenUpdating = True
> Else
> Set WB = Workbooks(strFileName)
> Application.ScreenUpdating = False
> With ThisWorkbook.Worksheets("Input and P&L")
> .Range("E9").Value = WB.Worksheets("Input and
> P&L").Range("e9").Value
> .Range("E10").Value = WB.Worksheets("Input and
> P&L").Range("e10").Value
> End With
> Set WB = Nothing
> Application.ScreenUpdating = True
> End If
>
>
> "Jim Thomlinson" wrote:
>
> > how about something like this perhaps (untested)
> >
> > Dim WB As Workbook
> > Dim strFileName As String
> >
> > strFileName = Application.GetOpenFilename(FileFilter:="All
> > Files(*.*),*.xls,All Files (*.*),*.*")
> >
> > On Error Resume Next
> > Set WB = Workbooks(Mid(strFileName, InStrRev(strFileName, "\") + 1, 256))
> > On Error GoTo 0
> >
> > If WB Is Nothing Then Set WB = Workbooks.Open(strFileName, True, True)
> >
> > Application.ScreenUpdating = False
> > With ThisWorkbook.Worksheets("Input and P&L")
> > .Range("E9").Value = WB.Worksheets("Input and P&L").Range("e9").Value
> > .Range("E10").Value = WB.Worksheets("Input and
> > P&L").Range("e10").Value
> > End With
> > WB.Close False
> > Set WB = Nothing
> > Application.ScreenUpdating = True
> > End Sub
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Paige" wrote:
> >
> > > Have sub below that is used to get data from closed workbook (i.e., the
> > > source file); it actually opens the file in read-only then closes again; it
> > > works great. However, I can’t seem to integrate into it code to check to see
> > > if the file which the user selects in the file dialog is already open or not.
> > > Have tried functions, etc., but I think my problem could be that it is
> > > opening the selected file prior to checking if it is open….just a guess
> > > anyway. Is it possible to have Excel check to see if the file selected by
> > > the user is open already, and if it is, then somehow allow the user to select
> > > the source file in the Excel window, at which time Excel will pull in the
> > > data that way? If it is not open, it would proceed as normal with the code
> > > below. If not, then how can I modify it to just check if the file is open?
> > >
> > > Dim WB As Workbook
> > > Set WB = Workbooks.Open(Application.GetOpenFilename(FileFilter:="All
> > > Files(*.*),*.xls,All Files (*.*),*.*"), True, True)
> > > Application.ScreenUpdating = False
> > > With ThisWorkbook.Worksheets("Input and P&L")
> > > .Range("E9").Value = WB.Worksheets("Input and P&L").Range("e9").Value
> > > .Range("E10").Value = WB.Worksheets("Input and
> > > P&L").Range("e10").Value
> > > End With
> > > WB.Close False
> > > Set WB = Nothing
> > > Application.ScreenUpdating = True
> > > End Sub
> > >
> > >
> > >
|
Similar Threads
