Solved

Macro Programming MS oficce 2010 Save AS

Posted on 2011-03-01
8
359 Views
Last Modified: 2012-05-11
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 = strNum
End Sub

Open in new window

0
Comment
Question by:falimorad
  • 4
  • 4
8 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35011576
Please incorporate this code in the above code to save the file as .xlsx

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "C:\MyFile.xlsx"
    Application.DisplayAlerts = True

Open in new window


Sid
0
 

Author Comment

by:falimorad
ID: 35011627
sid can I concatenate the number to the file name, and can I close the existing xslm file.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35011639
Yes. In that case, put the code in the end after the number is stored in the variable strNum

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs "C:\MyFile" & strNum & ".xlsx"
    Application.DisplayAlerts = True

Open in new window


Sid
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:falimorad
ID: 35011675
Hey Sid,

I am getting an error, could be because its an xlsm file
err.jpg
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35011727
What happens when you press debug?

Sid
0
 

Author Comment

by:falimorad
ID: 35011753
It shows ActiveWorkbook.SaveAs "C:\MyFile" & strNum & ".xlsx" is highlighted, but I actually was googling this and the solution seems to be

ActiveWorkbook.SaveAs "C:\MyFile" & strNum & ".xlsx", FileFormat:=51

I wouldnt have found it if it wasnt for your solution.  

Another quick question, how can I set it so that when the file is opened, it wont give the enable macro option and just enable it.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35011782
0
 

Author Closing Comment

by:falimorad
ID: 35011825
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question