Create and Name new workbook

[melewie]

Hello All

I am having problems trying to write a peice of code that will create a new work book and name it. The problem im having is that the user will likely do this several times, what I would like to do is....

New WB = "Raw Data" if WB Raw Data already exists then new WB = "Raw Data 1" and so on.... I can do this by creating a neverending if statement, is there a better way to do this??

Any help would be great - Thanks

 

[GTO]

More details? Is the new workbook being created in a constant path?

 

[Trevor G]

Hello All

I am having problems trying to write a peice of code that will create a new work book and name it. The problem im having is that the user will likely do this several times, what I would like to do is....

New WB = "Raw Data" if WB Raw Data already exists then new WB = "Raw Data 1" and so on.... I can do this by creating a neverending if statement, is there a better way to do this??

Any help would be great - Thanks

Why not look to add the date at the end of the workbook name

Sub AddNew()
Workbooks.Add
ActiveWorkbook.SaveAs "M:\Access Files\Raw Deals" & Format(Date, "dd mm yyyy") & ".xls"
End Sub

 

[melewie]

More details? Is the new workbook being created in a constant path?

Hi GTO - not sure what you mean by constant path? I wont be saving the workbook in the code I will leave that up to the user to save where they want.

trying to do a simalar thing to what execl does automaticly if you open a new WB its called book1 open another book2 and so on. Only I would like them to be called Raw Data 1, 2, 3

Hope that helps

dont think the date will help it is likely that they will be several created on the same session, also I wont be saving this workbook in the code

 

[GTO]

Well, hopefully Trevor is better clued in than I am. If they are are saving to where they want, why are we worrying about a 'version number' so to speak?

If the code is to create a new/blank wb, or a wb based upon another - I would think about assigning the last val to a cell in the wb w/the code, add one to that, and of course update the val in the cell.

Does that help?

Mark

 

[Ruddles]

This function will check a folder for a filename: if it doesn't already exist in that folder, it returns the name you passed to it; if it does exist, it will try adding numbers to the filename until it finds one that doesn't exist, then it will return that. You have the option of telling it how many digits you want in the number and whether you want a separator in front of it.

Paste this code into a new general code module:-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]Public Function NextFilename( _[/SIZE][/FONT]
[SIZE=1][FONT=Courier New]    ByVal argFilename As String, _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]    ByVal argSuffixLength As Integer, _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]    ByVal argSeparator As String, _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]    ByVal argFolder As String) As String[/FONT][/SIZE]
 
[FONT=Courier New][SIZE=1]Dim iPtr As Integer[/SIZE][/FONT]
[SIZE=1][FONT=Courier New]Dim FileRoot As String[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Dim FileExt As String[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Dim sSuffix As String[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Dim sFound As String[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]iPtr = InStrRev(argFilename, ".")[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]If iPtr = 0 Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] FileRoot = argFilename[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] FileExt = ""[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Else[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] FileRoot = Left(argFilename, iPtr - 1)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] FileExt = Mid(argFilename, iPtr)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]End If[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]sSuffix = ""[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]For iPtr = Len(FileRoot) To 1 Step -1[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] If IsNumeric(Mid(FileRoot, iPtr, 1)) Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   sSuffix = Mid(FileRoot, iPtr, 1) & sSuffix[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] End If[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Next iPtr[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]If Right(argFolder, 1) <> "\" Then argFolder = argFolder & "\"[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]sFound = Dir(argFolder & argFilename)[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]Do Until Len(sFound) = 0[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] If Len(sSuffix) = 0 Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   sSuffix = String(argSuffixLength - 1, "0") & "1"   [/FONT][/SIZE][FONT=Courier New][SIZE=1][COLOR=green]' start numbering at "1" - change to "0" if required[/COLOR][/SIZE][/FONT]
[SIZE=1][FONT=Courier New] Else[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   sSuffix = CStr(CInt(sSuffix) + 1)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   sSuffix = Right(String(argSuffixLength, "0") & sSuffix, argSuffixLength)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] End If[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] sFound = Dir(argFolder & FileRoot & argSeparator & sSuffix & FileExt)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]Loop[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New]NextFilename = FileRoot & argSeparator & sSuffix & FileExt[/FONT][/SIZE]
 
[FONT=Courier New][SIZE=1]End Function[/SIZE][/FONT]

Call it as follows:-

[FONT=Courier New][SIZE=1]MyFilename = NextFilename("testfile.xls", 4, "c:\foldername\")[/SIZE][/FONT]

Call it like this:-

MyFilename = NextFilename("testfile.xls", 4, "_", "c:\folder\")/CODE]
 
If [B]testfile.xls[/B] doesn't exist in [B]c:\folder\[/B], [B]MyFilename [/B]will be set to [B]testfile.xls[/B]. If [B]testfile.xls[/B] does exist in [B]c:\foldername\[/B], [B]MyFilename[/B] will be set to [B]testfile_0001.xls[/B].
 
This call:-
[CODE]MyFilename = NextFilename("RAW DATA" & ".XLSX", 3, " ", "c:\folder\")

willl produce RAW DATA 001.xlsx. Obviously you can split the name at the dot if you need to, using InStr or InStrRev and Left/Right, or you can simply not return the file extension from the function by removing & FileExt from that one line right at the end of the code.

I'm sure you get the idea.

You can modify the code to start numbering from 0 rather than 1: I've placed a comment against the line in question.

 

[melewie]

it is part of a Dashborad the raw data is on severeal hidden sheets on the dashborad, the users have requested to be able to see variuos bits of raw data i.e big faliures, massive c**k ups etc....

What i want to do is create a new WB and transfer the relevent raw data from the dashbord to the newly created WB. within the code I will need to jump back and forth between the dashboard and the new WB to get the data across. I want to name the new WB so it looks better and I can refrence the WB as I go between the 2.

A user might decide to get data for X and then also get data for Y I need to be able to idtify which WB is which - So X would be 'Raw Data' and Y would be 'Raw Data 2'

The cell idea is a good one I will gove it a go - cheers

 

[melewie]

This function will check a folder for a filename: if it doesn't already exist in that folder, it returns the name you passed to it; if it does exist, it will try adding numbers to the filename until it finds one that doesn't exist, then it will return that. You have the option of telling it how many digits you want in the number and whether you want a separator in front of it.

Paste this code into a new general code module:-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]Public Function NextFilename( _[/SIZE][/FONT]
[SIZE=1][FONT=Courier New]      ByVal argFilename As String, _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]      ByVal argSuffixLength As Integer, _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]      ByVal argSeparator As String, _[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]      ByVal argFolder As String) As String[/FONT][/SIZE]
 
[FONT=Courier New][SIZE=1] Dim iPtr As Integer[/SIZE][/FONT]
[SIZE=1][FONT=Courier New] Dim FileRoot As String[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] Dim FileExt As String[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] Dim sSuffix As String[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] Dim sFound As String[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New] iPtr = InStrRev(argFilename, ".")[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] If iPtr = 0 Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   FileRoot = argFilename[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   FileExt = ""[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] Else[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   FileRoot = Left(argFilename, iPtr - 1)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   FileExt = Mid(argFilename, iPtr)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] End If[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New] sSuffix = ""[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] For iPtr = Len(FileRoot) To 1 Step -1[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   If IsNumeric(Mid(FileRoot, iPtr, 1)) Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]     sSuffix = Mid(FileRoot, iPtr, 1) & sSuffix[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   End If[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] Next iPtr[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New] If Right(argFolder, 1) <> "\" Then argFolder = argFolder & "\"[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New] sFound = Dir(argFolder & argFilename)[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New] Do Until Len(sFound) = 0[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   If Len(sSuffix) = 0 Then[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]     sSuffix = String(argSuffixLength - 1, "0") & "1"   [/FONT][/SIZE][FONT=Courier New][SIZE=1][COLOR=green]' start numbering at "1" - change to "0" if required[/COLOR][/SIZE][/FONT]
[SIZE=1][FONT=Courier New]   Else[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]     sSuffix = CStr(CInt(sSuffix) + 1)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]     sSuffix = Right(String(argSuffixLength, "0") & sSuffix, argSuffixLength)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   End If[/FONT][/SIZE]
[SIZE=1][FONT=Courier New]   sFound = Dir(argFolder & FileRoot & argSeparator & sSuffix & FileExt)[/FONT][/SIZE]
[SIZE=1][FONT=Courier New] Loop[/FONT][/SIZE]
 
[SIZE=1][FONT=Courier New] NextFilename = FileRoot & argSeparator & sSuffix & FileExt[/FONT][/SIZE]
 
[FONT=Courier New][SIZE=1]End Function[/SIZE][/FONT]

Call it as follows:-

[FONT=Courier New][SIZE=1]MyFilename = NextFilename("testfile.xls", 4, "c:\foldername\")[/SIZE][/FONT]

Call it like this:-

MyFilename = NextFilename("testfile.xls", 4, "_", "c:\folder\")/CODE]
 
If [B]testfile.xls[/B] doesn't exist in [B]c:\folder\[/B], [B]MyFilename [/B]will be set to [B]testfile.xls[/B]. If [B]testfile.xls[/B] does exist in [B]c:\foldername\[/B], [B]MyFilename[/B] will be set to [B]testfile_0001.xls[/B].
 
This call:-
[CODE]MyFilename = NextFilename("RAW DATA" & ".XLSX", 3, " ", "c:\folder\")

willl produce RAW DATA 001.xlsx.

I'm sure you get the idea.

You can modify the code to start numbering from 0 rather than 1: 've placed a comment against the line in question.

thanks for that Ruddles but the WB wont be saved by the code. so there is no file path just a unsaved WB

 

[Ruddles]

Oh well, it was worth a try. At least I've written something I may use myself one day and whiled away half an hour before my coffee break!

Hang on... so instead of checking for a file in a folder, I just need to check for an open workbook?

 

[melewie]

Oh well, it was worth a try. At least I've written something I may use myself one day and whiled away half an hour before my coffee break!

Glad I could be of some assistance