close a file ater opening with GetOpenFileName
 

Hi

I open a file with code using GetOpenFileName then copy a sheet to an
already open file.
I have tried altering the code to close the file after the sheet has been
copied but have had no success.

Could someone tell me how to do it?

Many Thanks

Kenny
XP Pro
Office 2003

The code I am currently using is below

Sub OpenAFile()

' thanks MrExcel.com



Dim vFilename As Variant



vFilename = Application.GetOpenFilename("Microsoft Excel
Workbooks,*.xls")



If vFilename = False Then Exit Sub 'User pressed Cancel



Workbooks.Open vFilename




Sheets("Data").Select



Sheets("Data").Copy Befo=Workbooks( _

"Copy of Defects Audit V4.01getopenfilename.xls").Sheets(1)






MyAudit



Sheets("Data").Delete

Sheets("Report").Select

End Sub

close a file ater opening with GetOpenFileName
 

See this tester

I use
Set wb = Workbooks.Open(FName)

Now you can use this after you do the copy
wb.Close False


Sub test()
Dim FName As Variant
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = ThisWorkbook.Path
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
If FName < False Then
Set wb = Workbooks.Open(FName)

' do your stuff

wb.Close False
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Forum Freak" wrote in message ...
Hi

I open a file with code using GetOpenFileName then copy a sheet to an
already open file.
I have tried altering the code to close the file after the sheet has been
copied but have had no success.

Could someone tell me how to do it?

Many Thanks

Kenny
XP Pro
Office 2003

The code I am currently using is below

Sub OpenAFile()

' thanks MrExcel.com



Dim vFilename As Variant



vFilename = Application.GetOpenFilename("Microsoft Excel
Workbooks,*.xls")



If vFilename = False Then Exit Sub 'User pressed Cancel



Workbooks.Open vFilename




Sheets("Data").Select



Sheets("Data").Copy Befo=Workbooks( _

"Copy of Defects Audit V4.01getopenfilename.xls").Sheets(1)






MyAudit



Sheets("Data").Delete

Sheets("Report").Select

End Sub

close a file ater opening with GetOpenFileName
 

Many thanks Ron. You have come to my rescue yet again. It worked a treat.
As usual I will credit you and your website in the macro for future
reference.

Regards
Kenny


"Ron de Bruin" wrote in message
...
See this tester

I use
Set wb = Workbooks.Open(FName)

Now you can use this after you do the copy
wb.Close False


Sub test()
Dim FName As Variant
Dim wb As Workbook
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = ThisWorkbook.Path
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls")
If FName < False Then
Set wb = Workbooks.Open(FName)
' do your stuff
wb.Close False
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Forum Freak" wrote in message
...
Hi

I open a file with code using GetOpenFileName then copy a sheet to an
already open file.
I have tried altering the code to close the file after the sheet has been
copied but have had no success.

Could someone tell me how to do it?

Many Thanks

Kenny
XP Pro
Office 2003

The code I am currently using is below

Sub OpenAFile()

' thanks MrExcel.com



Dim vFilename As Variant



vFilename = Application.GetOpenFilename("Microsoft Excel
Workbooks,*.xls")



If vFilename = False Then Exit Sub 'User pressed Cancel



Workbooks.Open vFilename




Sheets("Data").Select



Sheets("Data").Copy Befo=Workbooks( _

"Copy of Defects Audit V4.01getopenfilename.xls").Sheets(1)






MyAudit



Sheets("Data").Delete

Sheets("Report").Select

End Sub