Solved

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

Posted on 2002-07-09
11
211 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
 

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now