I have previously posted this butr it dissapeared over night?
Can anyone help?
I have a userform which saves its info to a spreadsheet. what i want to do is to also open a new workbook, save the same info to the new workbook, copy and paste from existing spreadsheet will do instead of linking the userform to a different workbook (i can do all this) I then want to save the workbook in a specific folder(folderA) but named by the next number ie workbooks 1, 2, 3, 4, 5, 6, 7, 8 etc exist so the next workbook should be called 9 so I need some code to look for the names of existing workbooks in the folder and determine the next.
Then I want a spreadsheet to at a pre-set interval(15 minutes) open each workbook , copy the line to the next blank row and move the workbook to a second folder(folderB).
The folders A & B will only contain these workbooks.
Any help greatly appreciated
Name/Number Of Next Workbook In Numeric Series
-
-
-
Re: Naming Workbook Next In Series
Code
Display MorePublic Function lFreeFileNumber(ByVal sPath As String, ByVal sPrefix As String, ByVal sSuffix As String) As Long 'Returns the first non-zero number in a set of numbered files. 'Example: Assume that ' sPath = "C:\" ' sPrefix = "Book" ' sSuffix = ".xls" 'If folder C:\ contains files named from Book1.xls to Book7.xls, the function returns 8 'If a number is missing in the sequence, for instance Book4.xls doesn't exist, the function returns 4 Dim lCounter As Long Dim bFound As Boolean If Right(sPath, 1) <> "\" Then sPath = sPath & "\" Do lCounter = 1 + lCounter bFound = Len(Dir(sPath & sPrefix & CStr(lCounter) & sSuffix)) Loop While bFound lFreeFileNumber = lCounter End Function
On the rest, you'll have to give a little more detail to get help.
-
Re: Naming Workbook Next In Series
thanks fencliff i'll give this a try when i get in tomorrow and update with the rest of the problem
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!