Solved

Saving a worksheet in excel (office97) without having to copy it to a new book first

Posted on 2002-07-09
11
221 Views
Last Modified: 2010-05-02
   
I am using excel VBA in office 97.

I have a multiple sheet workbook ,and just need to save a single sheet to a file.
The only way I can seem to do it is as follows

Sheets("test1").Select  'select the sheet
Sheets("test1").Copy  'this opens a new workbook and places the selected sheet into it.
ActiveWorkbook.SaveAs FileName:="C:\Book2.xls"    'save this new workbook
ActiveWindow.Close  'close the workbook, and return to the original workbook

This however creates a new workbook, with the selected sheet in it, then saves this workbook and then closes, thus returning me to my original workbook. It works, but involves lots of screen flashing and is time consuming.

What I want to do is just somehow "save the selected worksheet to a file", without having to copy it to a new workbook first. (i do not want to lose any formatting, so it needs to be saved as an .xls)

any help would be appreciated.

Thanks
0
Comment
Question by:gnome42
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 3

Expert Comment

by:sanjaykattimani
ID: 7142572
You can hide the excel object in order to stop it from flashing with following property.

objXls.Visible = False
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 7142581
Use this;
Application.ScreenUpdating = False

But dont foget to set it back to true when you are done.

Cheers
Marcus
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 7142582
From MS help;



ScreenUpdating Property Example

This example demonstrates how turning off screen updating can make your code run faster. The example hides every other column on Sheet1, while keeping track of the time it takes to do so. The first time the example hides the columns, screen updating is turned on; the second time, screen updating is turned off. When you run this example, you can compare the respective running times, which are displayed in the message box.

Dim elapsedTime(2)
Application.ScreenUpdating = True
For i = 1 To 2
    If i = 2 Then Application.ScreenUpdating = False
    startTime = Time
    Worksheets("Sheet1").Activate
    For Each c In ActiveSheet.Columns
        If c.Column Mod 2 = 0 Then
            c.Hidden = True
        End If
    Next c
    stopTime = Time
    elapsedTime(i) = (stopTime - startTime) * 24 * 60 * 60
Next i
Application.ScreenUpdating = True
MsgBox "Elapsed time, screen updating on: " & elapsedTime(1) & _
        " sec." & Chr(13) & _
        "Elapsed time, screen updating off: " & elapsedTime(2) & _
        " sec."
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gnome42
ID: 7142603
Sanjaykattimani
This does not appear to work in Excel97 VBA, but I do not want to hide the excel object I want to stop haveing to create this new workbook and then saving it, and then closing it.
But thanks for the suggestion.


Xsinbad
I am familiar with teh screenupdating property. But my initial problem is trying NOT to create this 2nd workbook. I just need to save a sheet to a file. I can import a sheet in 1 step  into the current workbook using

'Sheets.Add Type:="pathname\file"

but how do I save it without creating a 2nd workbook.

Thanks for the suggestion
0
 

Author Comment

by:gnome42
ID: 7142606
Sanjaykattimani
This does not appear to work in Excel97 VBA, but I do not want to hide the excel object I want to stop haveing to create this new workbook and then saving it, and then closing it.
But thanks for the suggestion.


Xsinbad
I am familiar with teh screenupdating property. But my initial problem is trying NOT to create this 2nd workbook. I just need to save a sheet to a file. I can import a sheet in 1 step  into the current workbook using

'Sheets.Add Type:="pathname\file"

but how do I save it without creating a 2nd workbook.

Thanks for the suggestion
0
 

Author Comment

by:gnome42
ID: 7142620
Sanjaykattimani
This does not appear to work in Excel97 VBA, but I do not want to hide the excel object I want to stop haveing to create this new workbook and then saving it, and then closing it.
But thanks for the suggestion.


Xsinbad
I am familiar with teh screenupdating property. But my initial problem is trying NOT to create this 2nd workbook. I just need to save a sheet to a file. I can import a sheet in 1 step  into the current workbook using

'Sheets.Add Type:="pathname\file"

but how do I save it without creating a 2nd workbook.

Thanks for the suggestion
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 7144864
Sorry about that I did not read the question properly. Now about the real problem, I dont think there is a way to do what you want without creating a new sheet. But you should be able to do this using OLE so that it all happens in memory and is not seen. I dont have time to do this right now but here is an example of OLE.


Sub test()
Dim objXL As Excel.Application
Dim i As Integer
 'first create the excel instance
 Set objXL = CreateObject("Excel.Application")
 objXL.Visible = True
 'open your workbook
 objXL.Workbooks.Open ("c:\my documents\book1.xls")
 'get sheet count
 i = objXL.Workbooks(1).Sheets.Count
 'copy the sheet you want to after the last sheet in the collection
 objXL.Workbooks(1).Sheets("Sheet2").Select
 objXL.Workbooks(1).ActiveSheet.Copy After:=objXL.Workbooks(1).Sheets(i)
 'rename the new sheet
 objXL.Workbooks(1).ActiveSheet.Name = "mysheet"
End Sub

 
0
 

Author Comment

by:gnome42
ID: 7145310
Xsinbad,
I am starting to think that what you are saying is correct, in respect to " I dont think there is a way to do what you want without creating a new sheet."  

OLE, or just the way I am doing it looks like the only way. Either way another instance of excel is created.   I wil leave this open until Monday next week, if no one else can come up with a way of doing it without creating this 2nd excel instance, then the points are all yours.  

Can't be done is not quite what I was looking for but then again, sometimes that is the only answer that is correct.

Thanks
0
 
LVL 6

Expert Comment

by:xSinbad
ID: 7147522
I dont believe in 'Cant be done' we just have to be clever enough to find a work around.
If you create another instance of excel with OLE you can keep it hidden from the user so they cant see it all happening.

When I get a few minutes free I will do some more work on it for you.

Cheers
Marcus
0
 
LVL 6

Accepted Solution

by:
xSinbad earned 300 total points
ID: 7147587
This may do the trick, it opens the workbook and saves a particular worksheet to another xls file as I think you are after.


Dim sExportPath As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Book3.xls") ' Book to get sheet from
Set xlSheet = xlBook.Worksheets(1)

sExportPath = "C:\Book2.xls" ' New book to save sheet to
xlSheet.SaveAs sExportPath
 
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing




Cheers
Marcus
0
 

Author Comment

by:gnome42
ID: 7153574
Xsinbad,

One would think that you are a salesman. You have talked me into it !!!
As you said "never say Can't  be done".  This solution will do nicely, and it will keep it all hidden from the user as you suggested.

Thanks for your time and input, it is most apreciated.

Regards

0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question