Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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

   
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
gnome42
Asked:
gnome42
  • 5
  • 5
1 Solution
 
sanjaykattimaniCommented:
You can hide the excel object in order to stop it from flashing with following property.

objXls.Visible = False
0
 
xSinbadCommented:
Use this;
Application.ScreenUpdating = False

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

Cheers
Marcus
0
 
xSinbadCommented:
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
gnome42Author Commented:
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
 
gnome42Author Commented:
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
 
gnome42Author Commented:
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
 
xSinbadCommented:
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
 
gnome42Author Commented:
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
 
xSinbadCommented:
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
 
xSinbadCommented:
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
 
gnome42Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now