Closing workbook

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

This is such an easy question but it just wont work for
me. I have the following code but cant seem to close the
workbook fname after i have copied the contents of a
specific sheet?

Sub Import_Crystal()

Dim fname As String


ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas Payments
Public\Ed"
fname = Application.GetOpenFilename()

Workbooks.Open (fname)
Sheets("TEST11").Select
Range("A1:AQ100").Select
Selection.Copy


Windows("Remittance Procedure.xls").Activate
Sheets("Crystal_Table").Select
ActiveSheet.Paste
Workbooks(fname).Close



End Sub
 
Ed,

I don't think you can reference an active workbook via it's full path and
name, which is what GetOpenFileName returns. Try this mod

Sub Import_Crystal()
Dim fname As String
Dim oWb As Workbook

ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas Payments Public\Ed"
fname = Application.GetOpenFilename()

If fName <> "" Then
Set oWb = Workbooks.Open (fname)
Sheets("TEST11").Select
Range("A1:AQ100").Copy

Windows("Remittance Procedure.xls").Activate
Sheets("Crystal_Table").Select
ActiveSheet.Paste
oWb.Close

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Ed
try replacing Workbooks(fname).Close with

Workbooks(fname).Activat
ActiveWorkbook.Close
 
No quite sure what is happening here but I have amended
the code and instead of copying the data in fname to sheet
Crystal_Table it is now copying the three command buttons
i have on the sheet that i run the macro for.

Sub Import_Crystal1()
Dim fname As String
Dim oWb As Workbook

ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas
Payments Public\Ed"
fname = Application.GetOpenFilename()

If fname <> "" Then

Set oWb = Workbooks.Open(fname)
Sheets("TEST11").Select
Range("A1:AQ100").Copy

Windows("Remittance Procedure.xls").Activate
Sheets("Crystal_Table").Select
ActiveSheet.Paste


Else
MsgBox ("Please select a Valid File")

End If
oWb.Close

End Sub
 
Ed,

Probably because that never now becomes an active workbook. Try this instead

Sub Import_Crystal1()
Dim fname As String
Dim oWb As Workbook

ChDrive "S"
ChDir "S:\Kingston\FA\Overseas Payments\Overseas
Payments Public\Ed"
fname = Application.GetOpenFilename()

If fname <> "" Then

Set oWb = Workbooks.Open(fname)
oWb.Sheets("TEST11").Range("A1:AQ100").Copy

Windows("Remittance Procedure.xls").Activate
Sheets("Crystal_Table").Select
ActiveSheet.Paste

Else
MsgBox ("Please select a Valid File")

End If
oWb.Close

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top