Closing Files Opened Using the FileToOpen
 

Hi Again

I have opened a file successfully (user defined and stored as FileToOpen)
and copied it's content into the active worksheet.
However, since it opens a new instance of Excel, I can't work out how to
close the FileToOpen file, and where the command belongs in the code below.

-----------------------------------------------------------------------------

Worksheets("Imported Data").Visible = True

With Application.FileDialog(msoFileDialogOpen)
FileToOpen = Application _
.GetOpenFilename("Comma Separated Values (*.csv),*.csv,Microsoft
Excel (*.xls),*.xls,All Files (*.*),*.*")
Workbooks.Open FileToOpen
Range("A1").Activate
Application.Run ("'Report Generator v1.xls'!Select_Data")
Selection.Copy
Workbooks("'Report Generator v1.xls").Activate
Sheets("Imported Data").Activate
Range("A1").Select
Selection.Insert Shift:=xlDown

***I assume here I need to switch to the other workbook
(FileToOpen), close it, then switch back to ("Report Generator v1.xls")***

End With
Worksheets("Imported Data").Visible = False
Sheets("Main").Activate

Closing Files Opened Using the FileToOpen
 

dim oWB
set oWB = Workbooks.Open(FileToOpen)

' do stuff with oWB

oWB.Close


Tim




"D Zandveld" wrote in message
...
Hi Again

I have opened a file successfully (user defined and stored as FileToOpen)
and copied it's content into the active worksheet.
However, since it opens a new instance of Excel, I can't work out how to
close the FileToOpen file, and where the command belongs in the code
below.

-----------------------------------------------------------------------------

Worksheets("Imported Data").Visible = True

With Application.FileDialog(msoFileDialogOpen)
FileToOpen = Application _
.GetOpenFilename("Comma Separated Values
(*.csv),*.csv,Microsoft
Excel (*.xls),*.xls,All Files (*.*),*.*")
Workbooks.Open FileToOpen
Range("A1").Activate
Application.Run ("'Report Generator v1.xls'!Select_Data")
Selection.Copy
Workbooks("'Report Generator v1.xls").Activate
Sheets("Imported Data").Activate
Range("A1").Select
Selection.Insert Shift:=xlDown

***I assume here I need to switch to the other workbook
(FileToOpen), close it, then switch back to ("Report Generator v1.xls")***

End With
Worksheets("Imported Data").Visible = False
Sheets("Main").Activate

Closing Files Opened Using the FileToOpen
 

Great Stuff, Thanks Tim.

"Tim Williams" wrote:


dim oWB
set oWB = Workbooks.Open(FileToOpen)

' do stuff with oWB

oWB.Close


Tim




"D Zandveld" wrote in message
...
Hi Again

I have opened a file successfully (user defined and stored as FileToOpen)
and copied it's content into the active worksheet.
However, since it opens a new instance of Excel, I can't work out how to
close the FileToOpen file, and where the command belongs in the code
below.

-----------------------------------------------------------------------------

Worksheets("Imported Data").Visible = True

With Application.FileDialog(msoFileDialogOpen)
FileToOpen = Application _
.GetOpenFilename("Comma Separated Values
(*.csv),*.csv,Microsoft
Excel (*.xls),*.xls,All Files (*.*),*.*")
Workbooks.Open FileToOpen
Range("A1").Activate
Application.Run ("'Report Generator v1.xls'!Select_Data")
Selection.Copy
Workbooks("'Report Generator v1.xls").Activate
Sheets("Imported Data").Activate
Range("A1").Select
Selection.Insert Shift:=xlDown

***I assume here I need to switch to the other workbook
(FileToOpen), close it, then switch back to ("Report Generator v1.xls")***

End With
Worksheets("Imported Data").Visible = False
Sheets("Main").Activate