Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Macro Programming MS oficce 2010 Save AS

Posted on 2011-03-01
8
Medium Priority
?
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

688 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