File name a variable

[Kate T]

Hello,

I need a macro that will prompt me to open a file (of my choosing from
anywhere in my browser), copy data from different sheets within that File
(The sheets will always be named the same, but the file name will change),
and then close the file without saving.

Thanks!

[Chip Pearson]

Kate,

Try something like

Dim FName As Variant
Dim WB As Workbook
FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If FName = False Then
Exit Sub
End If
Set WB = Workbooks.Open(FName)
' copy your data from WB to destination
WB.Close savechanges:=False


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Kate T" <Kate [email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I need a macro that will prompt me to open a file (of my
> choosing from
> anywhere in my browser), copy data from different sheets within
> that File
> (The sheets will always be named the same, but the file name
> will change),
> and then close the file without saving.
>
> Thanks!

[sebastienm]

Hi Kate,

'---------------------------------
Sub Process1Book()
Dim f As String 'file path & name
Dim wkb As Workbook 'open book

'choose a book to open
f = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If f = "False" Then
MsgBox "Cancelled by user"
Exit Sub
End If

'open the book
Set wkb = Workbooks.Open(f)

'Add Process code here

'Close the book without saving
wkb.Close False

End Sub
'-------------------------------

Regards,
Sebastien

"Kate T" wrote:

> Hello,
>
> I need a macro that will prompt me to open a file (of my choosing from
> anywhere in my browser), copy data from different sheets within that File
> (The sheets will always be named the same, but the file name will change),
> and then close the file without saving.
>
> Thanks!

[Kate T]

Thank you for your quick reply!

There's one step that I'm missing. In my process code, i have to toggle
back and forth between two files. How do i reference the "f" variable in a
Window.Activate command?

Specifically, i need to autofilter from the newly opened workbook (variable
f), based upon a range from my original source workbook. Then I paste into
the source workbook, and go back to the newly opened workbook. How can i
code for this?

Thanks!!

"sebastienm" wrote:

> Hi Kate,
>
> '---------------------------------
> Sub Process1Book()
> Dim f As String 'file path & name
> Dim wkb As Workbook 'open book
>
> 'choose a book to open
> f = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
> If f = "False" Then
> MsgBox "Cancelled by user"
> Exit Sub
> End If
>
> 'open the book
> Set wkb = Workbooks.Open(f)
>
> 'Add Process code here
>
> 'Close the book without saving
> wkb.Close False
>
> End Sub
> '-------------------------------
>
> Regards,
> Sebastien
>
> "Kate T" wrote:
>
> > Hello,
> >
> > I need a macro that will prompt me to open a file (of my choosing from
> > anywhere in my browser), copy data from different sheets within that File
> > (The sheets will always be named the same, but the file name will change),
> > and then close the file without saving.
> >
> > Thanks!

[sebastienm]

In this macro, the newly opened book is now referenced by the variable Wkb.
Say you have your source book referenced by WkbS (another open book):
Set WkbS = Workbooks("my source book.xls")

So you can directly work with the 2 above workbook object variables.
Eg1:
Wkb.Activate or WkbS.Activate
Eg2:
Copying value of A10 from sheet 'my sheet source' or WkbS (source) to A1
of sheet 'New Sheet' of the newly opend book Wkb:
Wkb.Worksheets("New Sheet").Range("A1").Value = _
WkbS.Worksheets("My Sheet Source").Range("A10")

Note: To work on a book, it does not need to be activated, it can be in the
background, therefore here, to copy the value, there is no need to Activate
the book back and forth.

Concerning the filtering i would need to know more though.

Sebastien

"Chip Pearson" wrote:

> Kate,
>
> Try something like
>
> Dim FName As Variant
> Dim WB As Workbook
> FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
> If FName = False Then
> Exit Sub
> End If
> Set WB = Workbooks.Open(FName)
> ' copy your data from WB to destination
> WB.Close savechanges:=False
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "Kate T" <Kate [email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > I need a macro that will prompt me to open a file (of my
> > choosing from
> > anywhere in my browser), copy data from different sheets within
> > that File
> > (The sheets will always be named the same, but the file name
> > will change),
> > and then close the file without saving.
> >
> > Thanks!
>
>
>

[sebastienm]

Sorry Chip, i meant to reply to Kate's post.
Regards,
Sebastien

[Kate T]

Great. Thanks for the great advice Sebastien and Chip!

"sebastienm" wrote:

> In this macro, the newly opened book is now referenced by the variable Wkb.
> Say you have your source book referenced by WkbS (another open book):
> Set WkbS = Workbooks("my source book.xls")
>
> So you can directly work with the 2 above workbook object variables.
> Eg1:
> Wkb.Activate or WkbS.Activate
> Eg2:
> Copying value of A10 from sheet 'my sheet source' or WkbS (source) to A1
> of sheet 'New Sheet' of the newly opend book Wkb:
> Wkb.Worksheets("New Sheet").Range("A1").Value = _
> WkbS.Worksheets("My Sheet Source").Range("A10")
>
> Note: To work on a book, it does not need to be activated, it can be in the
> background, therefore here, to copy the value, there is no need to Activate
> the book back and forth.
>
> Concerning the filtering i would need to know more though.
>
> Sebastien
>
> "Chip Pearson" wrote:
>
> > Kate,
> >
> > Try something like
> >
> > Dim FName As Variant
> > Dim WB As Workbook
> > FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
> > If FName = False Then
> > Exit Sub
> > End If
> > Set WB = Workbooks.Open(FName)
> > ' copy your data from WB to destination
> > WB.Close savechanges:=False
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> >
> >
> > "Kate T" <Kate [email protected]> wrote in message
> > news:[email protected]...
> > > Hello,
> > >
> > > I need a macro that will prompt me to open a file (of my
> > > choosing from
> > > anywhere in my browser), copy data from different sheets within
> > > that File
> > > (The sheets will always be named the same, but the file name
> > > will change),
> > > and then close the file without saving.
> > >
> > > Thanks!
> >
> >
> >