Closing workbook

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
 
B

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)
 
S

StanJ

Ed
try replacing Workbooks(fname).Close with

Workbooks(fname).Activat
ActiveWorkbook.Close
 
E

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
 
B

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subscript out of range 1
Importing data 3
Import Wizard 1
Import Worksheets and "Overright" 9
Copy Sheets 0
I need help with a "Run-Time Error 13: Type Mismatch" 2
copy worksheets 6
Copy - Paste 4

Top