|
Closing workbook
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 |
Closing workbook
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" wrote in message ... 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 |
Closing workbook
Ed
try replacing Workbooks(fname).Close with Workbooks(fname).Activat ActiveWorkbook.Close |
Closing workbook
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 -----Original Message----- 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" wrote in message ... 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 . |
Closing workbook
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) "Ed" wrote in message ... 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 -----Original Message----- 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" wrote in message ... 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 . |
| All times are GMT +1. The time now is 12:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com