Save tab as new file.

Lost Paul
StarLounger
Posts: 93
Joined: 10 Oct 2019, 09:43

Save tab as new file.

Post by Lost Paul »

Currently working on a template file that has to produce a few different outputs.
Each of these needs to be its own file, for uploading to the Customers' website.

I've put together the necessary for the first, but now need help with creating / exporting (VBA) this 'tab' to a new *.xlsx file.

Any pointers / code / etc would be very welcom.

TIA

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Save tab as new file.

Post by HansV »

Here is a starting point:

Code: Select all

Sub ExportSheet()
    Dim wsh As Worksheet
    Dim wbk As Workbook
    Dim strPath As String
    Dim strFile As String
    stPath = ActiveWorkbook.Path ' or specify a fixed path
    Set wsh = ActiveSheet ' or Worksheets("...")
    strFile = wsh.Name & ".xlsx"
    wsh.Copy
    Set wbk = ActiveWorkbook
    wbk.SaveAs Filename:=strPath & Application.PathSeparator & strFile, FileFormat:=xlOpenXMLWorkbook
    wbk.Close SaveChanges:=False
End Sub
Best wishes,
Hans

Lost Paul
StarLounger
Posts: 93
Joined: 10 Oct 2019, 09:43

Re: Save tab as new file.

Post by Lost Paul »

Cool - that is helpful.
Unfortunately, we are having to work with Sharepoint :aflame: (in a protected environment), which only sometimes allows saving directly to there.

Would it be possible to default to 'My Documents' (any user), or haveing the save pause at the Save / Save As dialogue box?

Lost Paul
StarLounger
Posts: 93
Joined: 10 Oct 2019, 09:43

Re: Save tab as new file.

Post by Lost Paul »

Sorry, on the file name, how could the current date be added Please?
e.g. today would be 270421

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Save tab as new file.

Post by HansV »

How about

Code: Select all

Sub ExportSheet()
    Dim wsh As Worksheet
    Dim wbk As Workbook
    Dim strPath As String
    Dim strFile As String
    stPath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments")
    Set wsh = ActiveSheet ' or Worksheets("...")
    strFile = wsh.Name & Format(Date, "ddmmyy") & ".xlsx"
    wsh.Copy
    Set wbk = ActiveWorkbook
    wbk.SaveAs Filename:=strPath & Application.PathSeparator & strFile, FileFormat:=xlOpenXMLWorkbook
    wbk.Close SaveChanges:=False
End Sub
Best wishes,
Hans

Lost Paul
StarLounger
Posts: 93
Joined: 10 Oct 2019, 09:43

Re: Save tab as new file.

Post by Lost Paul »

Looks like just what I need to progress.
Cheers :cheers: