Menu
Ask a question Report

Open excel file into worksheet of open workbk [Solved/Closed]

Posts
2
Registration date
Saturday April 11, 2009
Last seen
April 12, 2009
 - - Latest reply:  Fetzel - Jul 23, 2010 at 03:47 PM
Excel Ask a question Report
Hello,

MS Excel 2000, VBA

I'd like to be able to open a file directly into a new worksheet of an open workbook, thus adding the new sheet in the process in a VBA macro.

I can open the file (which happens to be an html file, with that extension) fine into a new workbook, using the code:

Workbooks.Open Filename:="xxxxx.html"

However, I then have to copy and paste the contents of the worksheet in the newly opened workbook into the a new worksheet in the existing workbook.

If there is a way to open a file directly into a new worksheet of an open workbook it would be more efficient and save complications.
See more 
Related:

3 replies

Best answer
Report Respond to Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Last seen
June 21, 2011
9
Thank you
The following code will copy the sheet2copy to the target workbook (as a new sheet)
sheets(sheet2copy).activate
Sheets(sheetname).copy after:=Workbooks(target workbookname).Sheets(1)
workbooks(sourceworkbook).close

Thank you, Excelguru 9

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2576 users this month

Report
-
Thanks Excelguru,

However, you don't appear to have read my question carefully. I wrote:

"I'd like to be able to open a file directly into a new worksheet of an open workbook".

I already know how to copy a worksheet from an open workbook to another workbook. I want to avoid this step altogether by opening the saved file directly into a sheet in the target workbook. This new sheet might need to be created separately first, or might be created by the same code that reads the external file into it. Something like,

Activeworkbook.worksheets(1).open filename:= "xxxx.html"

might do the job if it were available - however, the worksheet object does not have an 'open' method.

Serendipitously, only because the file I am loading from is an html file, I have now found a way to do this using

ActiveWorkbook.HTMLProject.HTMLProjectItems.Item("xxx").LoadFromFile ("yyy.html")

The negatives of this method are:
1. there is no 'add' method for the collection HTMLProjectItems, so I can only create more than one HTMLProjectItem (which I require) by a roundabout method and
2. I have not yet been able to find a way to prevent the annoying "refresh/don't refresh" dialogue from appearing every time I use the LoadFromFile method. Switching off alerts with,

Application.DisplayAlerts = False

does not work. Nor does switching off interactive mode with,

Application.Interactive = False

Any thoughts?
Report Respond to Fetzel
7
Thank you
I just figured this out! I copied my Template into 3 different directories (i.e. Drive\Program Files\Microsoft Office\Templates\1033 and 2 other that had pre-installed templates). Then I right-clicked on the sheet tab and selected "insert" and my template was there!! Hope this helps you...
Report Respond to Halid
6
Thank you
Private Sub Workbook_Open()
' put the sheet copy code here

End Sub
Ask a question