Hello I have a code that was created by one of the members here, and I am trying to figure out how to add to it. What this code does is as soon as its opened, it generates a number and puts it to a log file, and this number is used as a invoice number on the cell N2. But what I also need is also, that as soon as its opened, the file will be saved as an xslx documents so that the user can make any changes to the document and come back to it without the number being touched. The main template will be stored somewhere and a user can open it up, and I want it to save as to an xslx to another location and close the xlsm file. THe filename of the new file is going to be "invoice_strNum.
Can anyone help
Private Sub Workbook_Open() Const ForReading = 1 Const ForAppending = 8 Dim objFSO As Object Dim objFil As Object Dim strFilePath As String Dim strLast As String Dim strNum As String Dim strContents As String Dim arrLines strFilePath = ThisWorkbook.Path & "\log.txt" Set objFSO = CreateObject("Scripting.FileSystemObject") On Error Resume Next Set objFil = objFSO.getfile(strFilePath) On Error GoTo 0 If objFil Is Nothing Then Set objFil = objFSO.createTextFile(strFilePath, ForWriting) objFil.writeline "1," & Environ("username") & "," & Now() Else Set objFil = objFSO.OpenTextFile(strFilePath, ForReading) strContents = objFil.ReadAll objFil.Close Set objFil = objFSO.OpenTextFile(strFilePath, ForAppending) arrLines = Split(strContents, vbCrLf) strLast = arrLines(UBound(arrLines) - 1) strNum = Val(Left(strLast, InStr(strLast, ",") - 1)) + 1 objFil.writeline strNum & "," & Environ("username") & "," & Now() End If objFil.Close Sheets("EXPENSE FORM").Range("N2").Value = strNumEnd Sub
SID is the best, whenever I have a problem and I see that he is one of the participants in answering, I know that the solution is going to be quick and precise.
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Open in new window
Sid