Computer Support Forum

Excel Help - Macro to save as & email

Question: Excel Help - Macro to save as & email

My sense is this is a pretty simple request, but I'm completely illiterate when it comes to VB.

I have an excel spreadsheet with a number of data points. I'm looking to export the entire sheet to a CSV file and send it via Outlook. Any suggestions?

More replies
Relevance 100%
Preferred Solution: Excel Help - Macro to save as & email

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

Relevance 73.39%

I am using a VB macro to save a list with 3 columns from excel to .csv file using the following code:

Application.ActiveWorkbook.SaveAs "filename", FileFormat:=xlCSV, local:=True

The last column may or may not contain a number. The last list separator ; appear in a irregular way. Sometimes it is missing, the error always start on a row that is a multiple of 16 (in the list below on row 64) and the error continues for 16 rows before the ending list seperator appears again. On what multiple of 16 the error uccurs is different depending on the list.

Anyone familiar with this problem or know why it appears?
The error is the same if I save it in .txt format

Line;Item Number;Quantity;Length
1;ITEM NUMBER 1;3;
2;ITEM NUMBER 2;10;
3;ITEM NUMBER 3;2;
4;ITEM NUMBER 4;1;54
5;ITEM NUMBER 5;1;561
6;ITEM NUMBER 6;1;56
7;ITEM NUMBER 7;29;
8;ITEM NUMBER 8;11;
9;ITEM NUMBER 9;2;3
10;ITEM NUMBER 10;2;
11;ITEM NUMBER 11;1;
12;ITEM NUMBER 12;1;
13;ITEM NUMBER 13;1;
14;ITEM NUMBER 14;6;
15;ITEM NUMBER 15;200;
16;ITEM NUMBER 16;56;
17;ITEM NUMBER 17;1;
18;ITEM NUMBER 18;1;
19;ITEM NUMBER 19;1;231
20;ITEM NUMBER 20;1;
21;ITEM NUMBER 21;2;
22;ITEM NUMBER 22;2;
23;ITEM NUMBER 23;1;
24;ITEM NUMBER 24;1;
25;ITEM NUMBER 25;2;23
26;ITEM NUMBER 26;1;1
27;ITEM NUMBER 27;4;
28;ITEM NUMBER 28;1;
29;ITEM NUMBER 29;1;
30;ITEM NUMBER 30;1;
31;ITEM NUMBER 31;3;231
32;ITEM NUMBER 32;3;
33;ITEM NUMBER 33;9;
34;ITEM NUMBER 34;3;
35;ITEM NUMBER 35;4;
36;ITEM NUMBER 36;2;213
37;ITEM NUMBER 37;2;3
38... Read more

More replies
Relevance 72.57%

I have a macro that saves my 40 page workbook to a PDF. The problem is that it places blank pages between each worksheet in the PDF.

Is there a way to stop the blank pages from occurring?

Here is the code I am using:
Code:

ThisFile = "C:\" & Replace(ActiveWorkbook.Name, ".xls", "") & ".pdf"

ActiveWorkbook.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=ThisFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False


 

Answer:Solved: Excel Macro -> Save As PDF

I found the answer on Adobe's website. The site indicated that the blank pages were a result of all the sheets having a different print quality.

Once I changed all the sheets to have the same print quality, the blank pages did not appear in the PDF.
 

1 more replies
Relevance 72.57%

I am trying to automate a spreadsheet that all our employees must update each day.

I would like the file to close after a set period of time (1 Minute)
 

Answer:Save and Exit by Macro in Excel

I've always steered clear of this as I've heard it's a killer on resources. Plus I wouldn't know where to begin.

So here is some code from the outstanding Tom Ogilvy to get you (or it) started -

Public NextTime As Date
Public EndTime As Date
Sub StartCount()
EndTime = Now + TimeValue("00:01:00")
NextTime = Now + TimeValue("00:00:01")
ActiveSheet.Range("A1").NumberFormat = "hh:mm:ss"
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End Sub

Sub Continuecount()
NextTime = Now + TimeValue("00:00:01")
If EndTime - Now < 0 Then Exit Sub
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
End Sub
 

3 more replies
Relevance 72.57%

Hi,I have 2 excel files:1.excel statement that has to be saved.2.excel database abc.xls which contains the client name, saving drive path and the name the file has to be saved.(this file contains column a: client name, b:file path c:file name to be saved)I want the macro to capture the active cell data from the statement and find the data in the database file, and if found save the file in the file name defined in column C and the given path specified in Column B. If the name not found then pop-up msg "name not found". The file should save in xls and pdf format.Please note I have 80-100 statements for diff clients and the same details are updated in the database file.Let me know if u any further information.Thanks in advance for your help.Santhosh

Answer:Macro to save a file in excel and pdf

re: The file should save in xls and pdf format.How are you saving files in PDF format?.pdf is not one of the default filetypes available in the "Save as type" list.pdf's can be created with a number of different pdf creation utilities so knowing how you are doing it would be helpful when writing code.

3 more replies
Relevance 72.57%

I have a worksheet that I want to concurrently print a hard copy and save to a .pdf file in a folder on my computer. The worksheet that I want to print and save to .pdf is not the active worksheet.I would like for the worksheet to be saved to .pdf with a unique file name. The worksheets saved as .pdfs will be archived, and it's important that they are not written over.Could someone help me with the VBA code to do so? Thank you for any help you can be.Sincerely,Mark1011

Answer:I want to use a macro to save an Excel worksheet to a .pdf.

Try here:http://www.rondebruin.nl/pdf.htmMIKEhttp://www.skeptic.com/

5 more replies
Relevance 72.57%

Appreciate a little help for a macro novice. Simple macro.
Sheets("SPA").Select
Sheets("SPA").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ChDir "C:\Documents and Settings\me\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\me\Desktop\SPA.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.WindowState = xlMinimized
Windows("PC 2011 Oct master.xls").Activate
End Sub

What and where do I enter a routine to prompt for the save file name. Presently it always save as SPA.xls I would like to be prompted to enter the name. I've seen threads on this but can't get it right. I need someone to say exactly where and what to enter.

Thank you in advance.
 

Answer:Excel Macro how to prompt for save name

After this line:
ChDir "C:\Documents and Settings\me\Desktop"

Add the following:

fileName = Application.GetSaveAsFilename
If filename <> False Then

' the name of the file chosen by the user will be stored in fileName
<put your code to save the file here>

End If


Don't forget to add this line at the top of the macro:
Dim fileName as Variant

Hope this helps.

Rajeev
 

3 more replies
Relevance 72.57%

Hello, I need to save diffrent worksheets with the tag "save" in cell A1 to a PDF file with a macrobutton.I Find this 2 code, Please could anyone help me to combine this two codes, or is there any other solution?[I need the fucntion that says, just youse thw worksheets with "save" in A1, and combine it with the seconde code that print PDF]--code 1---Sub Test5()Dim sht As WorksheetFor Each sht In ThisWorkbook.Worksheets If sht.Range("save").Value <> "" Then sht.PrintOut End IfNext shtEnd Sub--code 2---Sub Save_as_pdf()Dim FSO As ObjectDim s(1) As StringDim sNewFilePath As String Set FSO = CreateObject("Scripting.FileSystemObject") s(0) = ThisWorkbook.FullName If FSO.FileExists(s(0)) Then '//Change Excel Extension to PDF extension in FilePath s(1) = FSO.GetExtensionName(s(0)) If s(1) <> "" Then s(1) = "." & s(1) sNewFilePath = Replace(s(0), s(1), ".pdf") '//Export to PDF with new File Path ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ Filename:=sNewFilePath, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, _ IgnorePrintAreas:=False, OpenAfterPublish:=True End If Else '//Error: file path not found MsgBox "Error: this workbook may be unsaved. Please save and try again." End If Set FSO = NothingEnd Sub----Please help me :)

Answer:Save excel worksheets to PDF with macro?

I found this code, and it works, BUT I want to create a PDF file with more than one worksheet...Sub pdfsave()pdfname = Range("A1").ValueActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfname, Quality:=xlQualityStandard, _IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _TrueEnd Sub

2 more replies
Relevance 71.75%

I need some help with a macro. I need to save data from certain cells in 1 worksheet and to another worksheet. Details belowI want to grab the data in worksheet "survey" from cells A3, A4 and A6 and save them to worksheet "data" in cells A2, B2 and C2. After the save is done I then want to clear the data from cells A3, A6. I created an object button in my workbook that is clickable. I just need to assign a macro to it. I have been working on this for over a week and I am new to macros so it is a struggle. I have even went as far as purchasing a program call teachmemirosoftoffice and still can't figure out the whole thing. I get portions of it to work but not all of it. I would greatly appreciate anyone who can help me write this macro. I would forever be indebt to you my friend. Thanks.

Answer:Need a macro for excel 2007 to save data

Since you do not appear to have a contiguous range of cells in your source document, you have to do each cell individually. A contiguous range could have been copied and pasted Transposed into your destination, but since you skipped A5, you can't use that method.Try this:Sub CopyThenClear() Sheets("data").Range("A2") = Sheets("survey").Range("A3") Sheets("data").Range("B2") = Sheets("survey").Range("A4") Sheets("data").Range("C2") = Sheets("survey").Range("A6") Sheets("survey").Range("A3, A6").ClearContentsEnd Subre: "I would forever be in debt to you "Forever is a long time to be indebted for a few lines of VBA code. A simple thanks and a promise to pass it forward will suffice.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 71.75%

I have a workbook that has 30 worksheets inside it. I need to be able to save each worksheet as an excel file with the tab name being the file name. I have been able to save them to PDF files but I can't seem to get it to work for the excel side. Thanks

Answer:Macro Code, Save as Excel Document

I image it'd be something like this:Dim s As Variant
For Each s In ActiveWorkbook.Sheets
s.SaveAs s.Name, ActiveWorkbook.FileFormat
Next 'sHow To Ask Questions The Smart Way

3 more replies
Relevance 71.75%

Good Evening,

I am trying to save a workbook using a macro, but I can not get it to save in a folder called Sugar_cookies. It just saves in the specified drive. Any idea what I do wrong?

ActiveWorkbook.SaveAs Filename:= "N:\Sugar_cookies" & DateDay & addinfo

Thank you,
codedreamer
 

Answer:How to save excel in a specific folder using a macro

6 more replies
Relevance 71.34%

I am using the following code to email word document (its actually a form) as attachment and to save it to my "P:/" drive. It seems working but the code will need to modify for the following reasons:
Once I am done filling out the form and hit the command button to run the macro? the document (form) actually sent to email and also saved to my "P:/" drive successfully but its over saved the master file. I need the form to be blank at all time.
Also, I want to have the files to be stamp with date and time (as a file name for the form) that going to be email and save to "p:/" drive.
I will appreciate any help. Thanks!!

The code I am using is:

Private Sub CommandButton1_Click()

Dim OL As Object

Dim EmailItem As Object

Dim Doc As Document

Application.ScreenUpdating = False

Set OL = CreateObject("Outlook.Application")

Set EmailItem = OL.CreateItem(olMailItem)

Set Doc = ActiveDocument

Doc.SaveAs

With EmailItem

.Subject = "Insert Subject Here"

.Body = "Insert message here" & vbCrLf & _

"Line 2" & vbCrLf & _

"Line 3"

.To = "[email protected]"

.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow

.Attachments.Add Doc.FullName

.Send

End With

Application.ScreenUpdating = True

Set Doc = Nothing

Set OL = Nothing

Set EmailItem = Nothing

Flag = True

sPath = "P:\"

ActiveDocument.SaveAs FileName:=sPath & ActiveDo... Read more

More replies
Relevance 70.93%

Since my company has upgraded to Windows 7 I now have a problem recording Macros in Excel. I find that the commands SAVE and SAVE AS do not record any more.
Also when stepping through (F8) a macro, when it gets to a SAVE command it just runs to the end unless I put a Breakpoint after that command. Any ideas??
 

Answer:Excel Macro recorder will not record a save command

Hi, could you be a little more specific in what you mean with A macro will not record a Save command?
Here a two mcros recorde with the recorder
Code:

Sub testSave()
'
' testSave Macro
'

'
ChDir "G:\_Forii\11-November"
ActiveWorkbook.SaveAs Filename:="G:\_Forii\11-November\Testing Save.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub
Sub TestSave2()
'
' TestSave2 Macro
'

'
ActiveWorkbook.Save
End Sub

The first made it Save As because it was a new file and the second is recording a save action.

Also Windows 7 (you dit not tell us which version of Excel, my version is 2010)
 

3 more replies
Relevance 70.93%

How will I write a macro in excel that will enable me to save a current workbook in a specified directory and use the wording in a cell on the spreadsheet as the file name.

I have tried copying a macro and have got as far as the new directory, but do not know how to name the file after wording in a cell in a spreadsheet.
 

Answer:Macro to save excel workbook as name in cell on spreadsheet

Just one simple line. Just change the portions in red to reflect your true save path and cell to use. You may need to also change the xls file extension if you are using Office 2007 or newer.
Code:
ActiveWorkbook.SaveAs Filename:="[COLOR="Red"]C:\TEST\[/COLOR]" & Range("[COLOR="Red"]A1[/COLOR]").Text & ".xls", FileFormat:=xlNormal

 

1 more replies
Relevance 70.93%

Hi All,I have an Excel macro (see below) that I want to run from a VB script, the macro opens a txt file, formats it.Sub reformat_output()'' reformat_output Macro' Macro recorded 10/11/2010 by Windows XP Mode'' Workbooks.OpenText Filename:="C:\scripts\wri conversion\output.txt", Origin:=437, _ StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 9), Array(3, 1), Array(9, 9), Array(10, 1), Array(11, 9), Array(33, 3), Array(43, 9), Array(44, 1), Array _ (49, 9), Array(51, 1), Array(55, 9), Array(57, 1), Array(62, 9), Array(63, 1), Array(67, 9), Array(69, 1), Array(74, 9), Array(75, 1), Array(81, 9), Array(88, 1), Array(94, 9), Array(95, 1), Array(101, 9), Array(102, 1), Array(108, 9), Array(111, 1), Array(116, 9), Array(117, 1), Array(123, 9), Array(124, 1), Array(129, 9), Array(130, 1), Array(132, 9)), TrailingMinusNumbers:=TrueEnd SubThis works great.I need a VB Script that will open the excel file, run the macro, save the document, and close excel.I have tried to open just the excel file and it doesn't open, Set objFileSystem = CreateObject("Scripting.FileSystemObject")Set objExcel = Wscript.CreateObject("Excel.Application")Set objFile =objFileSystem.GetFile("C:\scripts\Working scripts\reformat_output.xlsm")objExcel.visible = trueobjExcel.Workbooks.Open(objFile.path)objExcel.Run "reformat_output.xlsm" objExcel.QuitSet objExcel = nothingThanks in advance for any help.Don

Answer:vb script open Excel, run macro, save, close

Ok, I was able to get it to open and run the macro using the script at the bottom of this message.What it does, is ask if I want to save the file when it tries to quit. The file has already been created, so I want to disable/bypass the save file window, or automatically enter no to save, so Excel closes without user interaction.Set objExcel = CreateObject("Excel.Application")Set objWorkbook = objExcel.Workbooks.Open("C:\scripts\WRI Conversion\reformat_output.xlsm")objExcel.visible = trueobjExcel.Run "reformat_output" objExcel.Quit

3 more replies
Relevance 70.11%

Is there a way to save a workbook if a cell within a range is changed?
 

Answer:Solved: Excel Macro -> Save Workbook On Cell Change

16 more replies
Relevance 70.11%

I have an Excel 2013 spreadsheet that develops hyperlinks for the user to click on to view certain files. However, I want to be able to give the user the option of saving the source file to a folder on our server. Is there a macro that I could create that would do this? I have researched online extensively and cannot locate anything that does this. I have included a screen capture of the raw data on my spreadsheet. Screen capture is not working for whatever reason so here is my data:File Path B1 E:\ABC\DOCS\Client ID B2 4444ABCDSeparator B3 .File Location B4 https://statements.company.com/ISD....Preferred File Name B5 Trimark Income Growth Fund A Fund Facts.pdfComplete Path&File Name B7 E:\ABC\DOCS\4444ABCD.Trimark Income Growth Fund A Fund Facts.pdfComplete Path&File Name is a formula =B1&B2&B3&B5Any help would be very appreciated. Thanks in advance.Stevemessage edited by airhockeycanada

Answer:Excel Macro to Save File Named in Specific Cell

I think I've got it!I have to give credit to Razor2.3 over in the Web Development forum. I posted my version of your question in that forum and Razor2.3 pointed me to this website where I found the code required to access the external server and download the file.http://serverfault.com/questions/29...With some minor customization to have the code pick up the information from your spreadsheet, as opposed to having it hardcoded in the macro, I was able to download the file directly to a folder on my system.2 items to mention:1 - The code is set to pull the information from the first Sheet in the workbook: Sheets(1)You can either change the number to reference the actual Sheet you are using or you can use the Sheet Name, enclosed in quotes. Sheets("My Sheet Name")If you use a number, and then later move the Sheet to different position, the code will not update. It will still reference the Sheet in the position represented by that number.If you use the Sheet Name, and then later move the Sheet, the code will still reference the Sheet by its Name, so that won't be a problem. However, if you change the name, then the code won't be able to find the Sheet unless you also change the name in the macro.2 - I noticed that you are building the file path within your spreadsheet (=B1&B2&B3&B5) so that is how I set up the macro. In other words, the code is going to pull the path and filename from B7.
strHDLocation = Sheets(1).Range("B7")Just so you know, The building of the path and fil... Read more

20 more replies
Relevance 70.11%

Hi guys,

I have a question that has stumped me so far. Here is the scenario: I am dealing with a spreadsheet that has several tabs on it. In each tab, there are several word, powerpoint, and pdf documents that are embedded as objects. My task is to write a macro, that will for each document (keep in mind its an embedded object that needs to be opened), open it with the appropriate program, and save it using a pre-assigned name (the name of the column heading and the row it is in) to a predetermined folder path. This process needs to be duplicated about 200 times. I'm not sure if this is even possible, but I know enough about vba to believe that it is. I've been working on it since yesterday, and I can't seem to tie the pieces together. Can anyone help on this?

Thanks!
 

More replies
Relevance 69.29%

I am getting a message, when I close Excel 2016, asking if I want to save changes to my Personal Macro Workbook, but I have not made any changes. Is there any way to get rid of this message, other than to click Save (I would have no idea what I am saving).

T

More replies
Relevance 69.29%

I have a spreadsheet that is used by a number of users and has a macro built into it to collect updates from several other sheets. I want to add to the macro so that once it has been updated and saved the macro then saves another version of the sheet in a seperate folder with the date and time it was saved as part of the file name.

I have tried

ChDir"H:\Resourcing Teams\Placements\Weekly activity\Archive"
ActiveWorkbook.SaveAs Filename:= _
"H:\ResourcingTeams\Placements\Weekly activity\Archive\" & Now.Day & Now.Month &Now.Year &" .xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

But get errors

Any suggestions greatly appreciated
 

Answer:Solved: Excel Macro to save a copy of the file in new location with the date

9 more replies
Relevance 69.29%

Hello all. Not a regular user of Excel; but do need help in creating something that would be useful to me and a few others at work. I suppose the best way of explaining what I'm after is by giving an example.

I have a directory C:\Users\Tekko\Desktop\Maintenance Project\Cape Nelson
In Cape Nelson are a number of folders named alpha beta charlie delta echo and foxtrot and so on.

I would like to have an excel template in "Cape Nelson" with a macro that when activated names the file as whatever folder name might be in say cell A1 and whatever ever date might be in cell B1. Eg charlie_15-mar-2013.xls
This then is saved in the relevant folder. So in the end I would end up with
C:\Users\Tekko\Desktop\Maintenance Project\Cape Nelson\charlie\charlie_15-mar-2013.xls

Also the macro script would ensure that the macro was disabled in the saved file.

Hoping this is achievable and look forward to replies.
 

Answer:Solved: Excel 2010 - Macro to name and save file to a specific folder

16 more replies
Relevance 68.88%

Any way of controlling the time a email is sent via a excel spread sheet using vba?
 

Answer:Excel - macro email-

6 more replies
Relevance 68.88%
Question: Excel Email Macro

Hi

I am trying to write a macro in Excel 2003 to automatically send an email using Outlook to a list of individuals asking them to confirm their personal details using the attached template. Could someone show me where I am going wrong with the script please?

Many thanks
 

Answer:Excel Email Macro

16 more replies
Relevance 68.88%

I have an Excel model (office 2000) which has a range of cells that contain if/then statements. I'm trying to get Excel to automatically send an email if any of the cells within that specified range were to provide a value that was false. Is this possible? Thanks for any insight.
 

Answer:Email Macro in Excel??

16 more replies
Relevance 68.88%
Question: Excel Email Macro

Hi All,

I have a spreadsheet and i am needing to create a macro that will opena new message in Outlook and populate the 'To' Field with one cell and then the 'CC' field with 2 email addresses, one from one field and the other from a different field. then the subject from another field and then the body from another. it then leaves the message window open so i can check it right and hit send.

I found the script the other day but can not find it anywhere including this site.

Any help on this will be great.

JPL

Any
 

Answer:Excel Email Macro

Hi JPL,

You could use this function, just pass the values to it
Code:

Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String)
Dim eSubject As String, EBody As String
Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)

With Itm
.Subject = mSubject
.to = Sendto
If Not IsMissing(CCto) Then .CC = CCto
.Body = mMessage
' .Attachments.Add (NewFileName1) ' Must be complete path
.Display ' This property is used when you want
' .send
End With
Set app = Nothing
Set Itm = Nothing
End Function

It's from one of the posts, I do not remember which one
 

1 more replies
Relevance 68.06%

Windows 7 --------------------------------------------------------------------------------First thing...I know nothing about macros!! I do know a very little about formulasI have created "IF" formula in excel 2010, based on a date it will create a send due in column "E", =IF(D5=$A$2,HYPERLINK(mailto:"&$K$1&"?subject="&A5&-B5&"&body="&$C$3,"sendworks great but, I have to go thru 86 rows in column "E" and hit "Send Due" numorous times,then hit send again on all the emails, can we automate this some how, like a macro that engadges when I open my outlook every morning?Thank u

Answer:Need Macro to automate email from excel

I have written code in Outlook, which is essentially a template, you fill in the details, all sorts of stuff happens accordingly to what you have selected, and you press send and it will send the emails to whoever it is you want. Iv done this in Outlook, so my question is, why do you need this to be done in Excel? is Outlook acceptable?

3 more replies
Relevance 68.06%

Hello all;

I am new to this forum and also to Excel macros. I have a large Excel file detailing financial accounts with my small business.
Anyway, I would like to email any customer who has an overdue balance as per column V in my attached sample file (any customer with a greater than zero value in V). I would like to send an email; something like this:

Joe Wilson;
As of 2/28/11 (latest date in the row that is on/after the current date), your account is currently overdue and you owe $1500.00 (G3).
Thank you.
Treasurer
I found several other threads explaining how to send an automatic email from Excel, but I haven't found one for sending an individualized email to a list of recipients. If this question has been answered, please feel free to redirect me. Otherwise, I'd appreciate any suggestions. Please let me know if I can detail my needs any better.
Thanks!
 

Answer:Excel Macro: Auto Email

welcome to the board.

I put some code in the sheet

The macro name is CheckAndMail

Let me know if this is what you need

PS: I just corrected something I came accross and have re attached the corrected sample
 

2 more replies
Relevance 68.06%

I have a user who wants to create a macro which will automatically send an excel spreadsheet into outlook and email it.
Any suggestions welcome. Thanks.
 

More replies
Relevance 67.24%

Hi - I have a list of names for a particular company where I have the first name and last name of the customer plus the email naming convention their company uses. For example the company's email naming convention is: [email protected] Is there a way I can create an Excel Macro that will automatically create the email from the available information? For example if I create three columns where one has the first name, last name and the @companyurl.com?I hope someone can help with this as it will save a lot of time!!Thank You!

Answer:Need Excel macro to creat email address out of just a name

This can be done fairly easily, but I'm not going to offer any suggestions.Since it appears that you do not work for this company, I can only assume that this will be for some sort of mass, unsolicited mailing. If your project was sponsored by the company in question, they would supply you with a distribution list and you wouldn't have to build your own.For all I know, I'm on that list of names that you have and I don't need any more unsolicited email.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 67.24%

I would like to ask for an Excel macro which can send an automate email on a certain date every Month.

The dates would be as follows:

1st Monday: Email should be sent to [email protected] automatically
2nd Wednesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically
3rd Tuesday: Email should be sent to [email protected] automatically

Subject line: ABC123D5679_Data transfer expected today

Details of the body of the text message:

"Dear All,
This is to remind you that we are expecting the data transfer to be posted in Novartis system today . This is just an advanced intimation.

Kindly let us know if you foresee a delay in the data transfer.

Kind regards,
Rohit"
Many thanks in advance for your help guys. This means a lot.
 

More replies
Relevance 67.24%

Hello

I need to come up with a simple outlook macro (or script?) that fires off whenever a new email meeting certain criteria is received.

1. new email is received with "please refresh quotes" in the subject
2. outlook runs a macro that causes another macro to run (the second macro is located in a excel workbook which is already running in my office pc)
3. the excel (attached) is simply refreshing market prices and emails whenever the macro is triggered. because the quotes are obtained via a DLL feed I can only get it at work.

the purpose is simply to get live market quotes on my personal email from my work email whenever i need.
thanks in advance for your help.
 

Answer:Solved: Run excel macro when new email is received

9 more replies
Relevance 67.24%

Hi there,

I have a workbook which i would ideally like to send an automated mail when the date is within 30 days of "Todays date" .
I have found something similaar on past posts whichprints certain cells to an email but is triggered by a button press not date, but wondered if anyone could adjust it for me as my excel knowledge is very limited.
I really am struggling.

The password for the spreadsheet is Kalibratedbyme (capital K)

Best regards and many thanks!
 

Answer:macro to allow a date to send an email in excel

The content is different but why are you duplicating a post?
 

3 more replies
Relevance 66.42%

Hy guys

2nd time i am posting stuff for help, and as i was helped before i will again look forward the response.

I have a file of excel, in which i am sending emails to different candidates of admission, with scan letter placed in the same folder by name.

I want to edit this code, which could select attachment based on Column A list adjacent to the email address

I am attaching the file also pasting the code

Sub Test1()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2013
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then

Set OutMail = OutApp.CreateItem(0)

strbody = "We at Graduate School of Engineering Sciences and Information Technology are extremely pleased to know that you have selected Hamdard University as preferred choice for your graduate/post-graduate Studies. " & vbNewLine & vbNewLine & _
"Hamdard University is a pioneer Higher Education Institute (HEI) of Karachi producing Masters and PhDs in the fields of Engineering, Computer Sciences, Information Technology, Energy and Environment since 19... Read more

Answer:Attachment based on cell value in a excel email macro

anybody ???
 

2 more replies
Relevance 66.42%

I found this code in this forum.
i want to add recipient as CC or BCC. What is the correct code for that?
Thanks in advance!

Code:
Public Sub email()

Dim SubJ, Recip As String

SubJ = "Enter your suject"
Recip = "[email protected]"


ThisWorkbook.SendMail Recip, SubJ

msgbox "Email Sent"

End Sub

 

Answer:Send excel sheet ( email) through macro with recipient and cc

6 more replies
Relevance 66.42%

Hi
I need help writing a macro that takes an Excel file to email.

The Excel file is a data base, I wanted the Macro to take the email from a column of the Excel file, and send out the email to all the emails in the database, if possible also adding a column of the Excel file to address the specific person.

thank you.
 

Answer:need help writing a macro that takes Excel file to email

Can you post a sample file? Please remove any sensitive data.

Rollin
 

2 more replies
Relevance 66.42%

Hello,

My first post, sorry to be a leech right off!

I'm trying to automate some of my day to day emails. I'd like to get a macro set up that will send an email from Excel and paste the range I select in to the body of the email. Would be great if it would send to a list of addresses listed on the page as well.

Using the attached example, I'd like the macro to take whatever range I highlight on the table, copy it to the body of an email and send it to the group of emails listed on the right side.

I'm learning excel, this would help me a lot! I'll be editing this to fit a variety of other tasks. I hope it can be flexible. Thanks in advance.

Chris
 

Answer:Macro: Email Range/Selection to group from Excel

http://www.rondebruin.nl/mail/folder3/mail4.htm

I have done the looping attached.
 

2 more replies
Relevance 66.42%

Hi:
I am very new to Excel 2007 and macros. I have a spreadsheet that I am trying to get to send an email reminder to the point of contact [ col b ] 5 days prior to the closure document due NLT date [ col m ]. I am looking for assistance in writing a macro which will accomplish this if it is possible. I have attached the spreadsheet that I referenced.
Your assistance would be greatly appreciated.
Thanks in advance.

desantisj
 

Answer:Excel 2007 Macro to Send Reminder Email

desantisj, welcome to the Forum.
There are already 3 or 4 posts on this forum that have the VBA code (Macro) that you can modify for your Workbook if you can read the code. Zack has written the code so it is a bit complicated, but it should be a case of substituting your Cell references that hold the data for the ones that others have used.
Otherwise it is a case of waiting for an Excel guru to come along and help. If none of them come along I can probably help you, but I normally work with Access.
 

2 more replies
Relevance 66.42%

Hi there - Using MS Access 2007.
I have a FORM with a macro that runs a query to generate ONE excel file and attaches to email using MS Out look to send out. - That Works great.

Problem: Multiple users to send email & attachment.
I would like for the macro to generate excel file (S) - based by the UID.

Can someone suggest a better way of handling this that is not time consuming?

Thanks In advance!
 

Answer:MS Access 2007 - Macro > Excel > Auto Email

Access can open Outlook and send an attachment to mutiple recipients either as a list or as individual emails.
So are the attachments all the same for the recipients?
 

1 more replies
Relevance 66.01%

Hello,

I have a list of email addresses that have either:
[email protected]
[email protected]
[email protected]
or no corelation to [email protected]

I'm looking for a macro that will take the emails with dots in them and put the first and last names into cells next to the email. The others I can do by hand. PLEASE HELP!
 

Answer:Solved: Excel Macro to convert email addresses to names

16 more replies
Relevance 66.01%

I found how to send an email from an Excel Macro (http://forums.techguy.org/business-applications/1056070-solved-excel-email-outlook-code.html)

Here is the code I'm using:
Code:

Sub Email_Test()
'
' Macro11 Macro
' Keyboard Shortcut: Ctrl+r
Dim eSubject As String
Dim Sendto As String
Dim CCto As String
Dim Body As String
Dim app As Object
Dim Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)
'Populate variables

mSubject = "Excel EMail Test"
Sendto = "[EMAIL="[email protected]"][email protected][/EMAIL]"
CCto = ""
mMessage = "You have received Knowledge Document Feedback for your domain."

With Itm
.Subject = mSubject
.To = Sendto
If Not IsMissing(CCto) Then .CC = CCto
.Body = mMessage
' .Attachments.Add (NewFileName1) ' Must be complete path
.Display ' This property is used when you want
' .send
End With
Set app = Nothing
Set Itm = Nothing
End Sub
Is there a way to format the message section with bullets? How about paragraph breaks?

Name: John Doe

Date: 6/15/2012
Doc #: KM123456
Etc:
Etc:
Comments:
I'm perfictly willing to look at how-to documentation if someone can point me in the right direction.

Thanks
firstshot
 

Answer:Solved: Formatting Excel Macro Email Message Section

Hi, I haven't really looked myself but instead of bullets I generally use a dash and a tab
Paragraph as such are not possible but you have the linefeed and carriage return

example to add to your macro code before calling the mail proc
Code:

Dim mytext as string
mytext = vbnullstring
mytext = "Dear Jon Doe" & vbclrf & vbcrlf
mytext=mytext & "-" & chr(9) & "Date :" & the date variable & vbcrlf
mytext=mytext & "-" & chr(9) & "Doc #:" & the dos variable & vbcrlf
mytext = mytext & "-" & chr(9) & "Etc :" "etcetera"
mytext = mytext & vbcrlf & vbcrlf
mytext = mytext & "Sincerely yours," & vbcrlf & vbcrlf
mytext = mytext & "Jane Doe" & vbcrlf

vbcrlf can also be replaced wij Chr(10) or Chr(13)

See it this helps.

Maybe html formattingis possible but this works just as fine.
 

3 more replies
Relevance 65.19%

Hi all

Looking for some help! Working on an excel document which contains a button I need to send to multiple people, that's fine what I need is for the subject field of the email to take the contents of a particular cell in the workbook and display that. My current code is;
Code:
Sub SendThis_ActiveWorkbook()

Dim Recip()
With ActiveWorkbook

ReDim Preserve Recip(0)
Recip(0) = "[EMAIL="[email protected]"][email protected][/EMAIL]"
ReDim Preserve Recip(1)
Recip(1) = "[EMAIL="[email protected]"]example[/EMAIL][EMAIL="[email protected]"][email protected][/EMAIL]"
.SendMail Recipients:=Recip, _
Subject:="Vacancy Authorisation " & Format(Date, "dd/mmm/yy")
End With

End Sub
Basically I want the subject to be "Vacancy Authorisation [cell contents] 01/01/2000" so I want the contents of a cell to appear in between the date and the "vacancy authorisation" part of the subject line. Assistance appreciated!
 

Answer:Solved: Excel email macro auto-populate subject line

Subject:="Vacancy Authorization " & Range("A1").Value & " " & Format(Date, "dd/mmm/yy")

Regards,
Rollin
 

2 more replies
Relevance 65.19%

Although decently experience with excel, I am quite new with VBA and I cannot "record" this macro, so any help writing this in VBA is much appreciated.

1) I need to create a macro that will take a number that is entered into a cell, and upon hitting a button in the excel sheet create an email where the subject line is the number that was in that original cell.

1b)I would like to do this without changing the name of the excel file though, or at least have another macro that resets the file to its original name.

2) Also, if possible, the same macro/vba as above, only with two cells, a number in one and a name in the other, which then shows up as "Number-Name" as the subject line in the email.

Thanks to anyone that can help - Mike
 

Answer:Excel Macro -> email created with field data as subject line

8 more replies
Relevance 65.19%

Hi all,

I need to know if is it possible that a macro in Excel sends an email containing the visible cells using Microsoft Outlook.
btw, I saw some code on the internet, that saves the visible cells in a new excel file and attach it in a new email using Microsoft Outlook, but I don't want to have attachments in the email.

thanks for any possible help.
 

More replies
Relevance 63.96%

I got a spreadsheet from my boss earlier today and was putting in some new numbers. I forgot that I had opened the file directly from my email client (thunderbird). Anyway, I finished up hit save and closed Excel. The email still has the old copy, where did Excel save the new one too? All searches (including for files modified this week) were fruitless.

Did I just loose my data?
 

Answer:Opening an XLS from Email client, where does Excel save?

check your Temporary Internet Files folder

Thats in c:\documents and settings\(user name)\local settings\temporary internet files
 

3 more replies
Relevance 63.55%

Hi all,
This is a great board-- I have received a lot of help from here!
This is my next project.
Open the attached file and, on the right of the form, there are six cells with text.
I need to concatenate these with one space in between each one, then copy the group of them and use the clipboard to paste so I can open the "save as" dialog box and paste it as the filename. We need this in our environment to be able to create the file name quickly, paste it and save the file.
I want to be able to do this with a button that activates a macro.
You can see the button on the right side when you open the file. The macro assigned to it called "createfilename". The code is in module 1.
I got it started, but I know it needs help.
Thanks!
 

Answer:Solved: Excel macro to concatenate and copy cells for pasting to "save as" dialog box

7 more replies
Relevance 62.73%

Hi,

I am new to VBA in Excel. I am trying to accomplish the following:

Have the following operation triggered by having the user click a "submit" button, then
Copy the active sheet to a new workbook
Save the new workbook to the user's desktop
Email the saved file to a standard email address, but with an editable subject line
(If it is possible to skip step 3, and still do step 4, that would be acceptable.)
I would appreciate any help that can be offered. Again, I am quite new to VBA, so simpler is better.

Thanks!

MW
 

More replies
Relevance 61.91%

Also, skype has stopped working (won't load). I am using Windows 8.1 and this problem started suddenly today. I have done a virus scan which is clean. Have googled for answers but found none helpful. Also deleted onedrive, restarted but problem not solved. Also the same issue occurs when i open a programme (like ms word) in safe mode. Can anyone help?
Paula

More replies
Relevance 59.04%

I am using the MSN Stock Quote addin with MS Excel 2003. Prior knowledge of this should not be necessary, but I just thought I'd provide full context to my issue. I wrote a macro that essentially records daily stock prices. Because I don't want to store old quotes into perpetuity, i need to ensure that the update quotes button has been hit. I use sendkey() to accomplish this, and then the macro copies the quotes and pastes them elsewhere. My main goal is to have the task scheduler open this file daily so i dont ever have to think about this again, and i'm so close. Essentially my problem is that it takes a few seconds after the update button is hit for the add-in to fetch the new prices from the internet, and this process appears to be delayed until the macro is finished. This is a problem because the macro ends up recording un-updated quotes. I have tried the Application.Wait procedure, and I've tried time consuming loops, do while loops, etc. All of these approaches result in the same thing: Can someone please help me with this? I'm too lazy to hand update this spreadsheet every trading day for the rest of my life (or until i quit). Thank you in advance!
 

Answer:Excel Macro - Allow cells to update query before macro completes

Can't you just put the update macro code in a module of it's own and call it from the main macro?

Regards,
Rollin
 

1 more replies
Relevance 55.35%

Hi, I'm new to this forum (actually forums in general) I was wondering if anyone could help me with a macro code. I work with 4 Excel sheets for my shippments, 2 sets for one plant and 2 for the other but all within the same file.

What I am trying to do is set up a macro that will automatically search for changes within sheet 1 (also for sheet 3, the other plant) When I change the rows color from blank to green/red, I need the macro to automatically cut that whole row and paste it into sheet 2 on the next blank row.

This doesn't seem like it would be that hard to do, but I know know the whole macro language.

If you can answer this please send me an email to soulblazor(@)hotmail.com

Thank you for your help.

More replies
Relevance 54.94%

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

Answer:Solved: Excel macro to extract Word form data into Excel

7 more replies
Relevance 54.94%

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

Answer:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

1 more replies
Relevance 54.94%

Iím using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

Answer:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

7 more replies
Relevance 54.94%

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

Answer:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

1 more replies
Relevance 54.94%

Hello,

I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

Notes:
the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.
Mzz
 

Answer:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?
 

1 more replies
Relevance 54.53%

Hi,
I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.
Thx
 

Answer:Excel - macro to merge 2 excel files using a common column

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?
 

3 more replies
Relevance 54.53%

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

Answer:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

1 more replies
Relevance 54.12%

With due respect and credit to DerbyDad03 whose earlier post gave me the code below; is there any way to have the new worksheets created from a variable length field e.g. a persons name in a list or a field of fixed length other than a date as the script below uses?Sub CreateMonthlySheets()Dim lastRow, mMonth, tstDate1, tstDate2, shtName, nxtRowOn Error Resume Next'Turn off ScreenUpdatingApplication.ScreenUpdating = False'Make a copy of the data sheet and sort by date Sheets("Sheet1").Copy After:=Sheets(1) Sheets(2).Name = "SortTemp" With Sheets("SortTemp") lastRow = .Cells(Rows.Count, 1).End(xlUp).Row Rows("2:" & lastRow).Sort Key1:=Range("A2"), Order1:=xlAscending 'Using SortTemp Sheet, create monthly sheets by'testing Month and Year values in Column A'Loop through dates For Each mMonth In .Range("A2:A" & lastRow) tstDate1 = Month(mMonth) & Year(mMonth) tstDate2 = Month(mMonth.Offset(-1, 0)) & Year(mMonth.Offset(-1, 0)) 'If Month and Year are different than cell above, create new sheet If tstDate1 <> tstDate2 Then ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count) 'Name the sheet based on the Month and Year ActiveSheet.Name = MonthName(Month(mMonth)) & " " & Year(mMonth)'Copy Column Widths and Header Row .Rows(1).Copy ActiveSheet.Rows(1).PasteSpecial Paste:=8 'ColumnWidth ActiveSheet.Rows(1).PasteSpecial 'Data... Read more

Answer:Excel Macro to create new worksheet in Excel 2010

I created a quick macro to help you out. This macro creates a new sheet and names it using the contents of cell "A1".Sub CreateNewSheet()
Dim NewSheet
Dim SheetName As String

SheetName = Worksheets("Sheet1").Cells(1, "A").Value
Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
NewSheet.Name = SheetName

End Sub
Law if Logical Argument: Anything is possible if you don't know what you're talking about.

4 more replies
Relevance 54.12%

I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:"Run-time error '445':Object doesn't support this action.The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearchCan you help me with this? I've searched for a solution for a long time but could not find anything which worked.The following is the start of the macro code:Sub UpdateTable()Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As VariantDim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As RangeDim ServRange As Range, SrcOpen As Boolean, SourceName As StringDim FilSrch As Object, MyFilArray() As String'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"'Exit Sub'Sheets("LookUp").[a12] = NowWith Application .ScreenUpdating = False .StatusBar = "Counting source files ..."End WithSet FilSrch = Application.FileSearchWith FilSrch .NewSearch .LookIn = ActiveWorkbook.Path .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then ReDim MyFilArray(.FoundFiles.Count) For I = 1 To .FoundFiles.Count If .FoundFiles(I) <> ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then ... Read more

Answer:Excel 2003 Macro doesn't run under Excel 2007

As far as I know, Application.FileSearch is not available in 2007. Searching for filesearch replacement turns up lots of hits with various workarounds, such as this:http://groups.google.com/group/micr...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 54.12%

Does anyone know how to run Excel 2003 print macro on Excel 2007 or higher? When I tried running my 2003 macro, the VBA editor pops up w dialogue box, " Run-time error '1004': Method 'ActivePrinter' of object'_Application' failed ".

This is my code: Application.ActivePrinter = "Epson LQ-300 ESC/P 2 on USB003"

Can anyone help?

Thanks!
 

Answer:How to run Excel 2003 print macro on Excel 2007?

Welcome to TSG hope we can help you and you can help others.

I'm not an Excel macro guy but you code looks reasonable. What happens if you run this. I suspect printer name is a little off.

MsgBox "The name of the active printer is " & Application.ActivePrinter
 

1 more replies
Relevance 54.12%

Hi, I have created an excel macro that runs sensitivities in my model. The excel button runs 5 different cases.

The macro runs fine and does as I want it to.

Once the macro has run, and I go to edit a cell and hit "F2" excel crashes. This only seems to happen in certain cells and not all of the time.

Does anyone have experience with similar questions?

Thanks
 

Answer:Excel Macro runs fine..then excel crashes

11 more replies
Relevance 54.12%

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

Answer:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
 

1 more replies
Relevance 54.12%

I run a weekly excel 2007 macro that converts a spreadsheet to a Word document, but it stops after row 29 of data. Any spreadsheet with 29 rows or less works fine. If I have 34 rows of data, the macro hangs. See below for script. Any help would be appreciated.

Public Sub AddControls(WrdApp As Word.Application, CurRow As Integer)
Dim OptChecked As Boolean
Dim GrpName As String
GrpName = "Grp" & CurRow
'Calculate the colors based on the cell information.
CurRange = "F" & CurRow & ".." & "F" & CurRow
Range(CurRange).Select

If InStr(1, UCase(ActiveCell.Text), "X SRM") > 0 Then 'SRM
OptChecked = True
Else
OptChecked = False
End If

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Governance:" & vbCrLf

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "SRM"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

If InStr(1, UCase(ActiveCell.Text), "X PPO") > 0 Then ' PPO
OptChecked = True
Else
OptChecked = False
End If

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "PPO"
.Font.Name = "Arial"
.Font.Size = 8
.Value = O... Read more

Answer:Excel Macro - converting excel to word doc - stops after row 29

13 more replies
Relevance 54.12%

The computer involved is using Windows 7 Pro 64bit and Office Professional Plus 2010.

I am having a problem I cannot find a solution to. I have one user at a clients location that has recently stopped being able to save excel files in their my documents. The my docs is being redirected to a server. The files open correctly and can be edited, but when save is clicked an error box says the document not saved. Using cntrl-shift-i I found the error code to be 100490. I can open any other file and save them just fine. And any new excel documents can be opened and saved correctly. The file can be saved into the same folder using save as is and the document is renamed. However, the user cannot erase the old file.

Doing some digging I made sure the trust zone was enable for the network path and trusted locations on a network were allowed. They had already been configured.

I checked for the windows update that microsoft reported could fix this problem and it was not installed. I went ahead and tried the hotfix, but it would not run because of the missing update.

I tried changing permissions and ownership. This where it gets strange the user has full control enabled on the folder. On any other folder or file I can right click and view the security tab. However, on the excel files when I click the security tab a red line is displayed that says "This file is currently not available for use on this computer." If I try to make a security change on a whole folder any excel fil... Read more

Answer:Cannot save excel files in a redirected folder unles I use save as

To resolve this problem, reinitialize the cache of offline files. To do this, follow these steps:Click Start, type regedit in the Start Search box, and then press ENTER.
If you are prompted for an administrator password or for a confirmation, type the password, or click Continue.
Locate the following registry subkey, and then right-click it: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\CSC
Point to New, and then click Key.
Type Parameters in the box.
Right-click Parameters, point to New, and then click DWORD (32-bit)Value.
Type FormatDatabase, and then press ENTER.
Right-click FormatDatabase, and then click Modify.
In the Value data box, type 1, and then click OK.
Exit Registry Editor, and then restart the computer.

Taken from Gray "X" on network share folder icon

3 more replies
Relevance 53.71%

I have a word document that includes a form the user must fill in. I want to create a button that will do all of the following:
1. Update all fields
2. Attach the updated word document to an email
3. Enter an address I have stored in the macro into the To field of the email.

I have one module which does the first part:
Sub CommandButton1_Click()
Dim oStory As Range
For Each oStory In ActiveDocument.StoryRanges
oStory.Fields.Update
If oStory.StoryType <> wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
Set oStory = Nothing
End Sub

I found that if I add this to the before End Sub it attaches the updated Word document to an email:
Application.Options.SendMailAttach = True 'Or False
ActiveDocument.SendMail

However, I can't figure out how have it put a specific address into the To field of the document.

So I tried another tact. I have the email address as a hyperlink in the document, and set a bookmark at the hyperlink. Then I created a module that will go to the bookmark and open the hyperlink. However, this will not add the document as an attachment:

Sub Open_Hyperlink()
Selection.GoTo What:=wdGoToBookmark, Name:="hyperlink"
With ActiveDocument.Bookmarks
.DefaultSorting = wdSortByName
.ShowHidden = False
End With

Selection.Range.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
End Sub

So then I found this bit of coding on the web, and tried imp... Read more

Answer:Word Macro to attach doc to email, then send address email

I found a solution, but I think it is a bit bulky. It seems to work but I'd love to know if there is a simpler solution:
Sub CommandButton1_Click()
Dim oStory As Range
For Each oStory In ActiveDocument.StoryRanges
oStory.Fields.Update
If oStory.StoryType <> wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Fields.Update
Wend
End If
Next oStory
Set oStory = Nothing

ActiveDocument.Save

MsgBox "Thank you. Your order has been sent", vbOKOnly, "Thank you. Your order has been sent"
Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
On Error Resume Next
If Len(ActiveDocument.Path) = 0 Then 'Document has not been saved
ActiveDocument.Save 'so save it
End If
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then 'Outlook isn't running
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If

Set oItem = oOutlookApp.CreateItem(olMailItem)
With oItem
.To = "[email protected]"
.Subject = "Promo Order" '
.Body = "Thank you for your order"
.Attachments.Add Source:=ActiveDocument.FullName, Type:=olByValue
.Send
'**********************************

End With
If bStarted Then 'If the macro started Outlook, stop it again.
oOutlookApp.Quit
End If
Set oItem = Nothing
Set oOutlookApp = Nothing
End Sub
 

1 more replies
Relevance 53.71%

Hello, I'm just joined and am trying to create a macro that will automatically email an entire workbook to a list of individuals. I also want the macro to copy and paste a range of cells within a specific sheet in the workbook and paste it into the body of the email. I am able to email the workbook but can't figure out the second part. Any help would be appreciated.

I am using Excell 2007 and LotusNotes 8.5.1
 

Answer:Macro to email centain range in body of email as well as the whole workbook

Hi, welcome to the forum.
There are several posts where I added some simple tips and tricks.
Do you have the macro you use now to mail the whole workbook?
If I see how you have done this then maybe it's a cinch to add the extra info
You may also post a workbook with fictious data if you wish
 

1 more replies
Relevance 53.3%

Does anyone know the best way to setup a Macro in Excel 2007 that will work in Excel 2003 as well?

I have an Excel 2007 workbook that I am using a macro to change the "Fill Color" and "Font Color" of cells. However, I will get everything working in Excel 2007, but when I open the workook in Excel 2003 I get many debug errors.

If I change the code in Excel 2003, and then re-open in Excel 2007 I get debug errors.

There must be a way to setup teh workbook to work on both Excel 2007 & 2003.
 

More replies
Relevance 53.3%

Excel '07 won't open an "excel 4.0" macro (i.e. *.xlm file). I am one of a few users at work using excel 2007, I believe this file was made with excel 2000 or similar. When I had excel 2000 it opened fine, it's a common file on the network that everyone should be able to access, and everyone with excel 2000 can easily. So I know it's not the file. When I try and open it with excel 2007 I get that stupid error about the file being different to the extension.

I've tried registry edits and workarounds after scowering the net but nothing seems to fix it. All it does is display the error, then opens excel in the background without even a new worksheet. Excel isn't hanging, it just does nothing. Any ideas?
 

Answer:excel 2007 won't open excel 4.0 macro

XLM was long ago replaced by VBA (with Excel 5 in 1993) and MS no longer really supports it (although I think they claim that they do, kinda, sorta.) So the only thing you can do, I think, is to make a copy of the xlm file and convert the xlm code in the copied file to VBA. Fortunately, I found a link that tells you how to do that:
http://blogs.msdn.com/b/excel/archive/2010/02/16/migrating-excel-4-macros-to-vba.aspx
(Look at the link at the bottom, too. But you will need to download that to an older PC, possibly.)
Now, assuming all goes well, this will give you two versions of the same file, but if everyone else has Excel 2000 (or later) then they will all be able to use the new VBA-based file, so you won't end up with two files that would lead to confusion.
Still, it looks to be a lot of work, so good luck!

On a final note, if you need this file done in VBA and you can't figure out how to redo it, make a post describing your needs - there are some amazing VBA coders on here.
 

1 more replies
Relevance 53.3%

I have an equation that I need help with putting into an excel Macro. The equation is as follows:
=IF(AND(ISBLANK(E2),ISNA(F2)),"XXXXXX",IF(AND(ISBLANK(E2),ISNUMBER(F2)),"ASSIGN LAN ID",IF(AND(E2="XXXXXX",ISNUMBER(F2)),"ASSIGN LAN ID",IF(AND(E2="XXXXXX",ISNA(F2)),E2,IF(AND(OR(ISBLANK(E2)="FALSE",E2<>"000000",E2<>"111111",E2<>"999999",E2<>"IOIOIO",E2<>"XXXXXX"),OR(ISNUMBER(F2),ISNA(F2))),E2,"No Criteria Met")))))

I need this put into a macro as either an if then else statement or a case statement. If anyone could help me out i would appreciate it
 

Answer:Excel Equation converted to an excel macro

Not sure if this will work for you or not, untested...
Code:
dim e as range, f as range, c as range
set e = range("E2"): set f = range("F2"): set c = activecell
if e = "" and worksheetfunction.isna(f) then
c.value = "XXXXXX"
elseif e = "" and isnumeric(f.value) then
c.value = "AS SIGN LAN ID"
elseif e = "XXXXXX" and isnumeric(f.value) then
c.value = "ASSIGN LAN ID"
elseif e = "XXXXXX" and worksheetfunction.isna(f) then
c.value = e.value
elseif (e <> "" or e <> "0000 00" or e <> "111111" or e <> "999999" or e <> "IOIOIO" or e <> "XXXXXX") and (isnumeric(f) or worksheetfunction.isna(f)) then
c.value = e.value
else
c.value = "No Criteria Met"
end if
HTH
 

3 more replies
Relevance 53.3%

A macro I wrote in VBA for Excel 2004 won't run in Excel 2011 (alas...). Upon opening the spreadsheet an "on open" routine runs, and bombs out on the following command:
Code:
Set cmdbar = Application.CommandBars.Add _
(Name:="Sort", _
Position:=msoBarTop, _
MenuBar:=False, _
temporary:=True) 'msobartop docks the menu at the top

Does anyone know if the VBA structure ("CommandBars.Add") has been removed or altered?

Is there another way to accomplish the same thing in 2011? I want to create a custom command bar (I populate it later in the module).
 

More replies
Relevance 52.89%

Excel MacrosI am trying to create a macro for Excel 2010 that will save each Excel worksheet, within a workbook, to be save as a PDF and the file name be the tab name. Does anyone know if this is possible? Thanks

Answer:Macro Code, save as PDF

This seemed to work.Some notes:- You'll have to change the path to match where you want the PDF's saved.- You can change OpenAfterPublish:=True to False if you don't want to see each PDF after it's created.- The code will overwrite a file with the same name without asking.You have been warned! ;-)Sub CreatePDF()
For sh = 1 To Sheets.Count
Sheets(sh).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\username\Desktop\" & Sheets(sh).Name & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

10 more replies
Relevance 52.89%

This is a very simple things for experts, but because I am a beginner its difficult for me to do this simple thing.
Below is the macro I've written. What I want is:
1) FileToOpen
Instead of using the fixed file "Pay Deduction Run 1812.xls" , I want my macro to ask user to select their own file so that they can use different Pay Deduction Runs.

2) SaveAs
Instead of saving the file as "Z:\FINANCE\GERARD\EXCEL\2009 Reconciliations\Payroll\Pay Deductions JNL\PY 1812.xls" , I want my macro to save the file automatically in the same path but with corresponding payrun number that was opened by user in step 1 e.g PY 1814, PY 1815, PY1815, etc...

I will be very thankful if somebody could edit my macro.
Sub PayDed()

Workbooks.Open Filename:= _
"Z:\FINANCE\PAYROLL\Ryde Deductions\Pay Deduction Run 1812.xls"
Columns("A:E").Select
Selection.Copy
Windows("Pay Deduction.xls").Activate
Sheets("Paste Here").Select
Columns("A:E").Select
ActiveSheet.Paste
Sheets("Journal").Select
Application.CutCopyMode = False
Sheets("Journal").Copy
Cells.Select
Selection.Copy
Application.CutCopyMode = False
ActiveSheet.Unprotect
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"Z:\FINANCE\GERARD\EXCEL\2009 Reconciliations\Payroll\Pay Deductions JNL\PY 1812.xls"
Active... Read more

Answer:Macro Help for FileToOpen ---->> Save As

7 more replies
Relevance 52.89%

I work in an office with some "stupid" people so need to write a macro that will convert word (2003) .doc files to .pdf (Adobe Acrobat 6.0 Professional) when they go to save these important documents. Is this possible & how do i use the macro? I know it is possible to select it as a print option but they aren't able to
Any help would be more then welcome.
 

Answer:Macro to Save Word as PDF?

16 more replies
Relevance 52.89%

Hello, I'm still learning this Excel stuff. I can make a macro to save it as a PDF by saying its filename is "invoice 1" but everytime I click my macro button, it always overwrites it. (Maybe because its just a simple saving macro i guess). But is there a way around this overwriting? Please could anybody give me a step by step? Most grateful thankyou. Nathan.

Answer:Macro: Different PDF file name on each save

You can use Application.InputBox to have the macro ask you for the name each time:
Sub SaveAsPDF()
MyFilename = Application.InputBox("Enter Filename")
If MyFilename = False Then Exit Sub
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="Z:\Your_Real_Path\" & MyFilename & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End SubIf you will always be saving this file as a PDF, you can use the BeforeSave event to run the code whenever you save the file, without using your macro button.In the VBE, double-click on the ThisWorkbook Module.Use the pull down next to General and choose Workbook.Use the pull down to the right and choose BeforeSave.Use the instructions from above to get this:Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MyFilename = Application.InputBox("Enter Filename")
If MyFilename = False Then Exit Sub
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="Z:\Your_Real_Path\" & MyFilename & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 52.48%

Received a few MS Excel files from an outside vendor as e-mail attachment. I am able to save the attachement to My Doc or desktop and then open or open them directly from Outlook 2003 or 2007.

But when I tried to save the doc with or without making changes, the spreadsheet just sits there and saying Saving at the bottom. I waited several miniutes to be saved, but it just sits there. It does not taking any extra resources. On Application event viewer, it just displayes application hang error.
 

Answer:Unable to save or save an Excel files

16 more replies
Relevance 52.07%

Dear Guyz

I have a macro helped and developed by a respected admin of this group

it has following statement

If Cells(lRow, "N").Value >= 5 And Len(Trim(Cells(lRow, "P").Value)) > 0 Then ' check if the months passed are >= 5 (same as the red conditional formatting)
If Len(Trim(Cells(lRow, "O").Value)) = 0 Or (IsDate(Cells(lRow, "O").Value) = True And Month(Cells(lRow, "O").Value) <> Month(Date)) Then
toList = Cells(lRow, "P") 'gets the recipient's email address Column O

I want to add another line from another macro

If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then

which makes sure that only @ emails are there and also ensures to which person it has to email and which it has avoid.

Can anybody help me to sort out this especially @Keebellah
 

Answer:Yes for Email No for no Email Condition in an if Statement of macro

7 more replies
Relevance 52.07%

I want to be able to save a paragraph that I use often somewhere that I can get to it easily. Is there a way to permanently save something to the clipboard, or a way to do a macro so all I have to do is hit a key and that paragraph will be pasted anywhere I want? Thanks!
 

Answer:Permanent Save to Clipboard OR Macro

Hi,
I've been using this free prog to do what you're looking to do:

http://www.phraseexpress.com/

Hope this helps?
Richard
 

3 more replies
Relevance 52.07%

Sub Macro7()'' Macro7 Macro'' Sheets("Form 16").Select ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Users\Rajesh\Desktop\Form 16 FY 2013 - 2014 Testing 1.pdf", Quality:= _ xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=TrueEnd Subi required to save the pdf file with the specific cell like (A21), i:e A21 text will be the my pdf file name , please helpmessage edited by Rajeshindium

Answer:how to Save As Pdf & Name From Cell with macro code

First, a posting tip... if you click on the blue line at the bottom of this post, you can read the instructions on how to post VBA code in this forum so that the format is retained. As for your question, try the following code.Note: I am assuming that A21 does not contain the .pdf extension. If it does, you should be able to see how the file name was built by the code and modify it to work.
Sub SaveAsPDF()
Dim Fname As String
myFname = Sheets("Form 16").Range("A21")
Sheets("Form 16").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Rajesh\Desktop\" & myFname & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 52.07%

How to create MS Excel 2007 macro which every time the file is launched, it will prompt the user to ?Save as"?So that the file will not be overwritten.Help me with the macro, please!

Answer:Creating Macro to Prompt for Save As

This code is adapted from the example given in the VBA Help files for the GetSaveAsFilename amd SaveAs methods.I put it inside the Workbook_Open event so that the Save As dialog box opens whenever the workbook is opened.As written, it does not prevent the user from canceling the Save As, so that would have to be added if needed.1 - Use Alt-F11 to open the VBA editor.2 - Double-click the ThisWorkbook entry under the VBA Project list.3 - Paste the following code into the pane that opens and save the file.Private Sub Workbook_Open()
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Workbooks (*.xls), *.xls")
If fileSaveName <> False Then
Me.SaveAs Filename:=fileSaveName
End If
End Sub

2 more replies
Relevance 52.07%

Hi,

I am trying to write a macro that will open a workbook and save it as a new file name. I need to do this 150 times, so I would like to be able to write something in the macro that will save the workbook as mulitple unique files. can this be done?

thank for your help
Nicky
 

Answer:Macro to Save as mulitple workbooks

6 more replies
Relevance 52.07%

Hi Everyone,
Iam new to this forum as well as excel. I saw a file before that had a button "Save Data" and it will save the data I wish to be saved in another worksheet and it will go down one row automatically for the next set. Somehow i managed to create a excel file with the fileds i need to input. However i am not able to complete it without the macros. Hope some one could complete this and send me the file ( i dont't know even a bit about excel ).
 

Answer:Macro to save data to another sheet

16 more replies
Relevance 52.07%

This is sort of relates to my other thread, but is for word this time so I figured it deserves its own thread.

I need a macro in MS Word, that will, before saves, update footer fields. This is mainly to automatically update the timestamp without having to go into print preview.

the update all keyboard command does not apply to headers / footers it seems.
I found a macro that will update all including headers and footers.

Sub UpdateAll()
Dim oStory As Range
Dim oField As Field
On Error Resume Next
For Each oStory In ActiveDocument.StoryRanges
For Each oField In oStory.Fields
oField.Update
Next oField
Next oStory
On Error GoTo 0
End Sub

I just need to a macro to launch the above macro before every save.

Thanks.
 

Answer:Solved: MS Word Macro - Before Save

16 more replies
Relevance 52.07%

How to save defferent file names need macro....

Answer:macro code for save diferent name

Ok so write the macro then.!!! ORBe more precise with your 'question' and we can try to help you...

2 more replies
Relevance 52.07%

I will pay a fee if needed just to get the answer.
I have a file containing approx 50 docs. Some are password protected, some are not. I need a macro that will open each doc (unprotecting those files that need a password), insert the text "second request" along with the current date (using Arial 12 pt bold font) under my header and centered. Lastly, I need it to save, reprotect the docs, and convert into pdf.

Is this possible? Please help me.
 

Answer:Macro to insert text and save as PDF

Hi Shanna, welcome to PCR! Which Office app is this for please?
 

7 more replies
Relevance 52.07%

Hi! i need you help!.. I am unable to open or save files in the shared drive using Excel 2007 but i can open/save excel 2007 files saved in my local drive

The error message I get when i try to open is "Could not open...." followed by the path and excel name.

This is the error message i get when i try to save in the shared drive:

Microsoft office excel cannot acces the file". There are several possible reasons:

the file name or path does not exist
the file is being used by another program
the workbook you are trying to save has the same name as a currently open workbook

Please help
 

Answer:Unable to Open and Save Excel files in the shared drive using Excel 2007

15 more replies
Relevance 52.07%

I have Windows 10 and Office 2007 installed on my home computer. I sent an Excel workbook from my work office to home via an outlook email that I opened in gmail. I cannot save that attached excel workbook as an excel file to my home computer.
Formerly in Windows XP all I had to do was open an Excel attachment and use "Save As" to save the attachment as an .xls document.

More replies
Relevance 51.66%

Hi I am new to this site and would like to send my greetings to all of you. I am new to macro and have never used em before, please help.I need help on making a macro for the following situation. I receive a excel file that have more then 7000 names in cloumn B and i need to filter it to display only a certain name and then save as html format with the name that i just filtered and i have to create a main folder then a sub folder for every single saved file and on top of that i have to put it on the G: server drive.I am sorry for my explanation, please bare with me. all this task is being done manually everyday and is taking much of my time. So i would like to know if there is a way for me to have a macro that auto filter the list and save as htlm every filtered result and create the folders and sub foders with date stamps.Any imput is tremendously appriciated, thank you all in advance.

Answer:Macro to auto filter&save as html

Hi,A few questions for clarification:1.When you say more than 7000 namesIs that 7000 different namesor7000 cells with names - but many are the same.2.If many are the same, approximately how many unique names are present in column B3.If there were say 100 unique names, can you confirm that you want 100 files saved, each in a unique sub-folder named with the appropriate 'filtered' name.4.If each file saved is filtered by a unique name, do you need to save only the data that belongs to that name.5.What row does the data start on. Is there is a header row or rows before the data.6.What is in column A - is it data that also needs to be saved.Regards

18 more replies
Relevance 51.66%

hello,
I'm trying to come up with a macro that will keep only a couple of certain rows visible when I open the workbook. The "certain rows" will be changing each month. I'm attaching a sample workbook which should make this easier to understand

Mike
 

More replies
Relevance 51.66%

Thank to Humar for his hard work and knowledge he came up with the following macro for me: Option ExplicitSub Parser()'Parsing by regionDim strWkBkName As StringDim rngStart As RangeDim rngEnd As RangeDim rngCell As RangeDim rngDestStart As RangeDim arryNames() As StringDim intArry As IntegerDim blnNotPresent As BooleanDim blnFound As BooleanDim intDestOffst As IntegerDim wkbkNew As WorkbookDim strPath As StringDim strNamePath As StringDim objFSO As ObjectDim n As IntegerOn Error GoTo ErrHndApplication.ScreenUpdating = FalsestrWkBkName = ActiveWorkbook.NameSet rngStart = ActiveSheet.Range("B9")Set rngEnd = ActiveSheet.Range("B" & CStr(Application.Rows.Count)).End(xlUp)Set rngDestStart = Worksheets("Sheet2").Range("A9")strPath = "C:\Users\Documents\test\"intArry = Int(rngEnd.Row / 4)ReDim arryNames(intArry)For Each rngCell In Range(rngStart, rngEnd) blnFound = False blnNotPresent = False For n = 0 To intArry If rngCell.Text = arryNames(n) Then blnFound = True If blnFound = True Then Exit For If arryNames(n) = "" Then blnNotPresent = True If blnNotPresent = True Then Exit For Next n If blnNotPresent = True Then arryNames(n) = rngCell.Text End IfNext rngCellFor n = 0 To intArry If arryNames(n) <> "" Then Worksheets("Sheet2").Cells.Clear Worksheets("Sheet1").Range("1:8").Copy _ Destination:=Worksheets("Sheet2").Range("A1") Worksheets("Sheet1").Rows(1).Copy Worksheets("Sheet2").Ra... Read more

Answer:Macro to auto filter&save as html

Hi for what its worth please don't flame my nobness here is what i have tried .. i was able to filter the column c and save it as folders and html file .. my problem is the main filtering of colum B and saving them as main folder here is my code:Option ExplicitSub neeew1()'Parsing by regionDim strWkBkName As StringDim rngStart As RangeDim rngEnd As RangeDim rng1Start As RangeDim rng1End As RangeDim rngCell As RangeDim rng1Cell As RangeDim rngDestStart As RangeDim arryNames() As StringDim intArry As IntegerDim blnNotPresent As BooleanDim blnFound As BooleanDim intDestOffst As IntegerDim wkbkNew As WorkbookDim strPath As StringDim strNamePath As StringDim objFSO As ObjectDim n As IntegerOn Error GoTo ErrHndApplication.ScreenUpdating = FalsestrWkBkName = ActiveWorkbook.NameSet rngStart = ActiveSheet.Range("B9")Set rngEnd = ActiveSheet.Range("B" & CStr(Application.Rows.Count)).End(xlUp)Set rng1Start = ActiveSheet.Range("C9")Set rng1End = ActiveSheet.Range("C" & CStr(Application.Rows.Count)).End(xlUp)Set rngDestStart = Worksheets("Sheet2").Range("A9")strPath = "C:\Users\bdo\Documents\S-report\region test\test\"intArry = Int(rng1End.Row / 4)ReDim arryNames(intArry)For Each rngCell In Range(rng1Start, rng1End) blnFound = False blnNotPresent = False For n = 0 To intArry If rngCell.Text = arryNames(n) Then blnFound = True If blnFound = True Then Exit For If arryNames(n) = "" Then blnNotPresent = True If blnNotPresent = True Then Exit For Next n ... Read more

2 more replies
Relevance 51.66%

When you visit a web page you can click on view, source, save as text and you can save the html as a text file on your hard drive. Is there any way to do this in some kind of batch program so I can save 100 or more html files as text at a time?
 

Answer:Is There A Tool Or Macro To Save HTML As Text

HTML files are, by nature, text files.
You are not stating where these files are at, and what kind of access you have and what kind of protocol(s) you can use to get at them.

I assume you want to use the http protocol to snag them off of a network somewhere.
If so, take a look at wget. I think it will do what you want.

-Ken
 

1 more replies
Relevance 51.66%

Dear All,I am looking for a macro to save a file name with active cell value(where the cursor is placed) and with 3 date options.Date options = a pop-up should come-up with buttons asking A B and C. If I click A = save the file with today's date. Click B = yesterday's date and Click C = day before yesterday's date.In brief the flow of the macro should be:Open the excel file>>>Place the cursor on the cell which should be the filename(the name may be anywhere in the sheet)>>> run the macro >>> A pop-up with with button A B and C>>> say click on B>>> file should be saved in a drive path with the file name and yesterday's date like _xyz_20100626.xlsCould anybody help me in building this macro for me, I would be greatful to them and this may change my life in the office I work.Thanks a lot in advance for helping me.

Answer:Macro to save file with active cell value

Hi,Here is a simple macro to allow you to save the active workbook with a combination of the name in the selected cell and a date.The first option is to save with today's date - if that is rejected, you are asked to save with yesterday's date and if that is rejected, you are asked to enter a date.Finally the filename and date are presented for final confirmation before saving.The workbook is saved to the same directory as the last save and in the default format.The macro could easily be changed to include a path/folder, rather than rely on what has recently been used.The macro also tests that the selected cell isn't empty - so that you don't go through the steps only to find that there is no name.I suggest that this macro, is stored in a module in an Excel Add-In so that it is always available, and the macro code does not end up in dozens of saved workbooks.The macro is run from a button on the Quick Access Toolbar.(I don't have Excel 2010, so I am going on Excel 2007 - hopefully the steps are the same - but I can't guarantee it).Start with a new workbook. Save it as an Excel Add-In (*.xlam) - use the 'Other formats' option in 'Save as' and find the Excel Add-In option.Name it e.g., "SaveAsName.xlam" and save.From the Office button select Excel options (at the bottom) and select 'Add-Ins' then in the drop-down at the bottom select 'Excel Add-ins' and click 'Go'In the dialog box click 'Browse' and your new add-in should be visible in the list - select it and click OKSaveAsNam... Read more

5 more replies
Relevance 51.66%

Hi,I have less than 700 branches in numeric code listed in drop down list. It is placed in cell F1. I would like to save it independently in pdf file using macro.Thanks guys for the help!

Answer:Macro to save multiple files independently in pdf.

What does this mean?Don't forget we have no idea what your workbook looks like, nor do we know what you do and what your data means. So telling us you have branches in a drop down means nothing. My assumption is that you have VALUES in a dropdown list, and you want to save each value in that list as a pdf file? if yes, then it is possible, but why would anyone wish to do that?If no, then please explain, in detail what you need.

2 more replies