Open excel file into worksheet of open workbk
Solved/Closed
hedles
Fetzel - Jul 23, 2010 at 03:47 PM
- Posts
- 2
- Registration date
- Saturday April 11, 2009
- Status
- Member
- Last seen
- April 12, 2009
Fetzel - Jul 23, 2010 at 03:47 PM
Related:
- Open excel file into worksheet of open workbk
- Create multiple excel files from one worksheet ✓ - Forum - Excel
- Excel vba save worksheet as new file values only ✓ - Forum - Excel
- Generate excel workbooks based on excel list ✓ - Forum - Excel
- Spell number in excel file download - Forum - Excel
- Copy excel file to another excel file - Guide
3 replies
Excelguru
Apr 12, 2009 at 06:34 AM
- Posts
- 261
- Registration date
- Saturday April 11, 2009
- Status
- Member
- Last seen
- June 21, 2011
Apr 12, 2009 at 06:34 AM
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
sheets(sheet2copy).activate
Sheets(sheetname).copy after:=Workbooks(target workbookname).Sheets(1)
workbooks(sourceworkbook).close
Apr 14, 2009 at 05:35 AM
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?