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

dim oWB
set oWB = Workbooks.Open(FileToOpen)

' do stuff with oWB

oWB.Close


Tim




"D Zandveld" <(E-Mail Removed)> wrote in message
news:2350E516-AD80-4A90-8CBA-(E-Mail Removed)...
> 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

Great Stuff, Thanks Tim.

"Tim Williams" wrote:

>
> dim oWB
> set oWB = Workbooks.Open(FileToOpen)
>
> ' do stuff with oWB
>
> oWB.Close
>
>
> Tim
>
>
>
>
> "D Zandveld" <(E-Mail Removed)> wrote in message
> news:2350E516-AD80-4A90-8CBA-(E-Mail Removed)...
> > 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
>
>
>