• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

Excel 2010 Macro

See attached file.  I got the code in the attachment to work but I dont want the VB code in the spreadsheet to be sent in the file being emailed.  So is there a code I can add and where to make this happen or what is the code tpo save it in a format that would not include the vb macros
ExcelMacro.txt
0
HFSCI
Asked:
HFSCI
  • 5
  • 4
1 Solution
 
Ken ButtersCommented:
Go to File... Save As...

Beneath the file name... you can select the save as type as "Excel Workbook (*.xlsx)

This will strip out any VBA code.   You can then email the xlsx version.
0
 
HFSCIAuthor Commented:
No - I need the code in the macro.  What I am trying to do in the original txt file attached the macro code is so I can schedule the spreadsheet to open (in scheduled tasks) and the macro runs to email the spreadsheet out and then closes.  The problem is it sends the macro/vb code with it which causes it to run when the email recipients open the spreadsheet.  This all happens unattended thats why I need it in the macro code I attached to the question.
0
 
Ken ButtersCommented:
ok.. then

Can you have the code save a copy of the running sheet as xlsx.... and send that copy in the email.
0
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!

 
HFSCIAuthor Commented:
See my attached code on the original question (I will attach it here again) so where in the code do I put it and what exactly would the code be??
ExcelMacro.txt
0
 
Shanan212Commented:
Set wb2 = Workbooks.Open("C:\Users\axxxx\Desktop\Sample.xlsm")
wb2.SaveCopyAs "C:\Users\xxxx\Desktop\Sample2.xlsx"

Open in new window

The above code will go above the following code

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

Open in new window


Adjust as needed
0
 
Ken ButtersCommented:
This will generate the new name with the xlsx extension:


   currentFile = Left(ThisWorkbook.FullName, (InStrRev(ThisWorkbook.FullName, ".", -1, vbTextCompare) - 1)) & ".xlsx"


This will save your workbook in new format.

  wb1.SaveCopyAs fileName:=currentFile, _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

now just attach "CurrentFile" to your email.
0
 
Ken ButtersCommented:
I updated and attached the code.

it seemed to work fine when I ran it  in Excel 10.
exelmacro.txt
0
 
HFSCIAuthor Commented:
shanan212 - this errors on the 1st line of your code
1. Set wb2 = Workbooks.Open("C:\Users\axxxx\Desktop\Sample.xlsm") which I made read like below:
                                 
    'inserted code between here from expert community
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & ".xlsm")
    wb2.SaveCopyAs TempFilePath & TempFileName & ".xlsx"
    'inserted above code
   
    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
0
 
HFSCIAuthor Commented:
buttersk - I got your email with the spreadsheet and pasted your attached code above in my workbook and it errored (see attached)
excelmacroerr.doc
0
 
HFSCIAuthor Commented:
Thanks I guess I pasted wrong or something but it now works after I tried again.  Works Great
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now