Search : in
By :

Open excel file into worksheet of open workbk

Last answer on Oct 17, 2009 6:00:28 am BST hedles, on Apr 12, 2009 9:22:51 am BST 
 Report this message to moderators

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.

Configuration: Windows XP
Chrome/Firefox/IE7 - in that order of preference
MS Office 2000

Best answers for « Open excel file into worksheet of open workbk » in :
How to convert Excel into PDF? ShowHow to convert Excel into PDF? Here is a small tips about how to convert your excel files into PDF for your presentation. Step 1 PDF995 is software that gets installed on your computer which enables you to print any sources of document to...
Converting your PDF file into an Image ShowConverting your PDF file into an Image Universal Document Converter PDF2IMAGE PDF TO IMAGE CONVERTER You could have encountered difficulties to modify PDF docs in the past. Nowadays this is no more trouble as there are several PDF...
Photoshop CS3 - How to open RAW files? ShowPhotoshop CS3 - How to open RAW files? Issue Solution Note Issue I have installed Photoshop CS3 and i'm using canon EOS 450D camera I am trying to edit RAW pictures using Photoshop CS3 but they can’t open And i also...
Download Excel File Repair Show
Download Excel Viewer Show
Download PDF SaM (Split and Merge) ShowPDFsam is a tool to merge and cut PDF files. It is an open source application to treat PDF files. It requires Virtual Java machine 1.4.2 or superior version. With a simple and intuitive interface, you can: - split documents PDF into chapters, into...
Spreadsheets - The Excel Interface ShowExcel Introduction Microsoft Excel is the spreadsheet in the Microsoft Office office suite. To start it, simply click on the appropriate icon in the Start menu (in Windows) or click on an Excel file (that has an .xls file extension). A Excel...
Ask your question Reply

1

Excelguru, on Apr 12, 2009 11:34:02 am BST
  • +2

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

   
Reply to Excelguru

2

hedles, on Apr 14, 2009 10:35:41 am BST
  • +1

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?

   
Reply to hedles

3

aliciadcosta, on Aug 4, 2009 6:30:29 am BST

Repair Excel File is the best software product in the market. It will repair MS Excel files within few minutes which have got lost. This is for all types of users.

http://www.repairexcelfile.org

   
Reply to aliciadcosta

4

 Halid, on Oct 17, 2009 6:00:28 am BST

Private Sub Workbook_Open()
' put the sheet copy code here

End Sub

   
Reply to Halid
Ask your question Reply
They need your help