Solved

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

Posted on 2002-07-09
11
218 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
  • 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

820 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