Closing workbook

[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

 

[Bob Phillips]

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)

 

[StanJ]

Ed
try replacing Workbooks(fname).Close with

Workbooks(fname).Activat
ActiveWorkbook.Close

 

[Ed]

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

 

[Bob Phillips]

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)