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
Save tab as new file.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Save tab as new file.
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
Hans
-
- StarLounger
- Posts: 93
- Joined: 10 Oct 2019, 09:43
Re: Save tab as new file.
Cool - that is helpful.
Unfortunately, we are having to work with Sharepoint (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?
Unfortunately, we are having to work with Sharepoint (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?
-
- StarLounger
- Posts: 93
- Joined: 10 Oct 2019, 09:43
Re: Save tab as new file.
Sorry, on the file name, how could the current date be added Please?
e.g. today would be 270421
e.g. today would be 270421
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Save tab as new file.
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
Hans
-
- StarLounger
- Posts: 93
- Joined: 10 Oct 2019, 09:43
Re: Save tab as new file.
Looks like just what I need to progress.
Cheers
Cheers