File name a variable
 

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!

File name a variable
 

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 wrote in message
...
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!

File name a variable
 

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!

File name a variable
 

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!

File name a variable
 

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 wrote in message
...
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!

File name a variable
 

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

File name a variable
 

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 wrote in message
...
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!