Exporting sheet values as a new excel file

[eHanSolo]

Hi ppl,

Can this be done at all?? I'd like to be able to export the cell contents of sheet1 as a new workbook where i can specify its name and location to be stored. Any hints or tips is greatly appreciated!

 

[Molby]

Have you tried the ActiveWorkbook.SaveAs method?

 

[eHanSolo]

I have looked at that...the thing is, I only want to save the data and formatting and not the macros etc etc...

 

[PHV]

Have you tried ActiveSheet.Copy without argument ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Molby]

You can also remove the modules

With ActiveWorkbook.VBProject
  .VBComponents.Remove   .VBComponents("Module1")
End With

 

[xlbo]

This is all in my FAQ
If you'd bothered looking, you wouldn't need to post

Just use

Activesheet.copy
Activeworkbook.saveas Filename:=Full Path & Name go here

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[kenguru]

Hi!

My question would be: i want to save my excel file and i tried the following code:

Private app As Excel.Application
Private wbk As Excel.Workbook

Private Sub cmdsave_Click()
Set app = New Excel.Application
Let app.Visible = True
Set wbk = app.Workbooks.Add
wbk.SaveAs "C:\Text.xls"
End Sub

but only opens a new document and nothing is in it.
Please help me!
Thanks'
Kenguru

 

[PHV]

Please help me!
To do what ?
In which application is supposed to run your code ?
Where is the sheet you mention in the subject coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[xlbo]

Please do not piggy back other threads - start your own thread and please provide more explanation as to what you are trying to achieve

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[eHanSolo]

thanks for all your help guys!!!!

 

[eHanSolo]

hey xlbo...

your code doesn't prompt me with the save as dialog box... or am i doing something wrong?

 

[xlbo]

why would it prompt you ?? the code is telling excel where to save the file to - ergo, there is no dialog box needed

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[eHanSolo]

Oh right... guess i was a bit too hazy with what i wanted to do.

I basically want some user freedom when saving.

 

[xlbo]

Well - in your initial post, you stated:

where i can specify its name and location to be stored

This flies in the face of what you are now asking which, I am guessing is that you want a new file to be created and the user to be prompted where to save it ???

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[eHanSolo]

yeh... admittedly, I was being unclear and misleading! My apologies!

 

[xlbo]

Is what I have assumed correct ??
Do you still want the user to determine the name and location of the new file ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[eHanSolo]

Yes thanks.

I've since used the macro recorder... doesn't quite do what i want it to do.

Basically, I just want the cell values of the current sheet to be 'exported' as a new workbook. (so i don't want to exclude any macros/buttons etc etc).

 

[xlbo]

ok - that is covered by
Sheets("sheetname").copy

that creates a new workbook with no code (although if you have code linked to the sheet, it may export with it....)

To then pop up the saveas dialog box, just use

Application.Dialogs(xlDialogSaveAs).Show


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[eHanSolo]

...good stuff! But it copies my buttons with all the macros etc etc... any more ideas??

 

[xlbo]

ah - you have buttons and code on that sheet - in that case, you're gonna have to get a LOT more involved. You'll have to loop through the shapes collection and dlete the buttons. You'll then have to access the VBProject object to delete the modules

Similar thread has this reference for VBA deletion - provided by KenWright

http://cpearson.com/excel/vbe.htm

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question