Computer Support Forum

Need Macro to automate email from excel

Question: Need Macro to automate email from excel

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

Relevance 100%
Preferred Solution: Need Macro to automate email from excel

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.)

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 86.51%

Hi all. This is my first question. I've searched the forums and can't find anything quite like I'm trying to do, even though it seems a fairly simple thing.

Background:
I am writing an application which will pick up csv files from a folder and put them in a database. This needs to be mainly done in IS in SQL Server 2005 for corporate reasons. However, the problem is that the data is not editable in IS. I am using XP and Office 2003 and SQL Server 2005.

Problem:
I have written an Excel macro to edit the data into a form that imports into IS and then a database, but the requirement is to automate the process. The source csv files will arrive with unknown names and at unknown times. Therefore the macro cannot be included in the individual files, and has been put in Personal.xls. Eventually, the IS routine will loop round and apply the editing and import to every file found in the particular folder.

Now, forget all about the IS side - I can do that later. What I'm trying to do is to Open Excel with a given spreadsheet name and then run the macro from personal.xls. I do not want this to run every time I open any spreadsheet, just named ones. The macro has a shortcut key - (^+)Q as it happens - and one idea I had was to try to send this as a key to Excel. I can do it from an Access macro, which would do, except that I would have to enter the spreadsheet name into the Access macro every time, which defeats the object.

The closest I've got was to overwri... Read more

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.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%

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%

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.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 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%

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 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%

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 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 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%

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%

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%

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%

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 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%

I have data in excel like this:

1
aaa
bbb
ccc
ccc
ccc
2
aaa
aaa
ccc
3
aaa
bbb
bbb
....

that I need to repeatedly make into a specific landscape format like this:

1 aaa bbb ccc
..............ccc
..............ccc
2 aaa ccc
...aaa
3 aaa bbb
........bbb
---------(PERIODS ONLY FOR ALLIGNMENT PURPOSES)---------------------

Any ideas how to start this. I use VB frequently, but the multiple rows (like in series 1 of several "ccc") are confusing me. Any tips would be appreciated!! Thanks,

Wino
 

Answer:Automate Report -- Macro Question

8 more replies
Relevance 66.01%

Microsoft Access - with a macro named as "autoexec" we can automatically save a report in the pc.Per the Access's menu - have converted the macro to visual basic.Have tried to automate the converted macro so as to automatically save a report but not successful. Is it possible?, If possible wonder whats are the steps?Thanks.

Answer:Access - possible to automate converted macro?

I think your message is confusing, but maybe that's because I haven't used macros in Access.  I believe all Office macros are recorded in a Visual Basic file.  So, what do you mean by "converted the macro to visual basic"?  In your first sentence, you said, "we can automatically save a report in the pc."   But, then you did this "conversion" and then you could not automatically save a report in the pc.  Sure seems like a contradiction.  And, based on what you've said, why do the "conversion" if it causes the "automatically save a report in the pc" to not work?

2 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 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 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

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%

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 59.45%

I need to automate courier rate using two sheets with different rates.two sheets are HEAVIES and NON HEAVIES RATES.each sheet will have the corresponding weight as rows and Country Zones as columns.i also have a separate sheet for the proper zoning of countriesi want to create a main page/excel wherein if I type in the weight with the corresponding country it will show me the correspinding rate.please help me

Answer:Automate price using excel

It sounds like VLOOKUP will work, but until we know a little more about your data, there is not much we can offer.Please click on the following line, read the instructions found via that link and then post an example of your data.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 59.45%

Got a tricky one here.

My employer has recently installed 5 payment kiosks. The kiosks generate csv files on a daily basis detailing the receipts. This has been running for about three months. NOW my employer wants some stats!

The machines spit out a page of html with links to the relevant csv files. However its only one specific type of file, i.e. dailyxxx.csv where xxx represents a numeric code. There are other csv files but these are to be ignored.

The very hard way, open up the html page, manually click on each dailyxxx.csv and paste the results into Excel, repeat for a lot of links.

The easy way, can excel be fed, the html page and automatically import the csv files of interest? They will always have the same prefix, daily, and the same file format .csv.

The awkward bit, any help would require to be without my being able to supply a sample due to the confidential nature of the financial information.

But it will always be a page of html links, and they, as stated, will always follow the same naming prefix and always be csv files.

I will be much impressed if this is something that can be automated with, for example vba or similar. I will not take any credit for any assistance provided.
 

Answer:Automate excel importing

16 more replies
Relevance 59.45%

Hi,Can someone please help me with my monthly report? I need to have the new monthly data automatically update and have the previous month archive. At the moment my data sheet is operating by =IF(month=current month,vlookup(data)). As a result I have to manually cut and value paste the previous month data before starting the new month. which some time I forgot to do (and it throw out my YTD cummulative data).thank you so much in advance

Answer:How to get excel to automate data

There's no way we can answer your question without more information.We don't know what the data looks like, how it is laid out, calculated, etc.Post some examples (after reading the How To reference below) and we'll see what we can do. Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 59.45%

I apologize if this has been asked and answered before. I have searched and could not find an answer specific to the situation I am facing.

I am needing to create automatic email reminders based on due dates. The issue is there are several milestones for each project referring back to one person. I need a method to send emails (through Outlook) automatically when the due date is approaching (within 3 days).

I have attached a sample sheet for reference.

Thanks.
 

Answer:Automate Emails from Excel

8 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 58.63%

A real estate agent I know wants to setup a bulk email process for her network of clients. She is looking for a tool which can email to several hundred recipients at once and can automatically send these notes at regularly scheduled times, (ie: weekly, monthly, etc).

Does anyone know of a product or products she might consider to do this for her?
 

Answer:Need to automate bulk email

8 more replies
Relevance 58.63%

Hi,

I am looking to create something that will auto setup email accounts in outlook express, 2000,2003,2007 like the old .ins files but where user input is only need to insert email address and password
 

More replies
Relevance 58.63%

Hi everyone,

I currently have a server setup with 1 desktop and 2 laptops connected to it. The server stores all of our media etc on it.

What I would like to do is have it setup so that when a user downloads a file to the server the user name and file name are extracted and when the download completes the user who requested it is sent an email.

in steps its

1. file moved to folderA user name and file name recorded

2. file moved to processing folder and downloaded

3. file moved to completed folder and user emailed file name and short message
I think steps 1 and 2 could be completed with a batch im just unsure how to do it and as for step 3 i think i would need some sort of watcher or something?

any help would be great! cheers!
 

Answer:Automate downloads and email

Posted via Mobile Device
Don't think you can do this natively. You would need 3rd party software to accomplish this.
 

3 more replies
Relevance 58.63%

On one worksheet I have 4 rows of exactly the same data with one column only containing a new data. E.g.:A100 A 1A100 A 2A100 A 3A100 A 4I want to keep only one row of the data and have column C data listed with semicolonsA100 A 1;2;3;4Do you know if there is a way to automate this task?

Answer:Automate Repetitive Excel Task

You did not include Row or Column labels so I am going to assume that your data is in A1:C4.If that is the case, then try this:=A1 & " " & B1 & " " & C1 & ";" & C2 & ";" & C3 & ";" & C4 Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 58.63%

Hi All,
I have been automating IE 7 with excel 2007 VBA in XP for a couple of years and have hit a stumbling block. I need to click on a text box to move a secured site forward in the process to edit a record. The text field that is NOT active NOR is it a hyperlink, but has an unnamed onClick event. As this is a secure site, the URL path never changes.

Attached is the Source Code with alias names/values for the text I need to click,
and my vba to get to this point:

How can I click this item with vba code?
 

Answer:Automate IE with Excel onClick text

Figured it out!

I was trying to click on a row of a table. This row had no name, but did have a Class and Title with an onClick="postURL...."
So what you need to do is loop through all items on the page until you find the class that equals the name. in my case, <tr class="shadedRow"

First you need to identify what element to loop through:

Dim inputElements As Object
Dim inputElement As IHTMLElement
Set inputElements = appIE.Document.getElementsByTagName("TR")

I selected "TR" above because <tr class="shadedRow". if you wanted to search through tables <table, then just type "table" instead of "TR".

For Each inputElement In inputElements
If inputElement.className = "shadedRow" Then
inputElement.Click
GoTo A
End If
Next inputElement

again, if you're searching for a tablename, change the if statement in the code above to tableName = "yourtablename"

Hope this helps someone other than me.
 

5 more replies
Relevance 58.22%

Background:
We have a weekly meeting that we all sit around and dish out our schedules and manually input them into a master excel sheet. This is inconvenient, time consuming, and inefficient. We would like to automate the process.

What we need:
Outlook Calendars (7 in total) -> Master Excel Sheet -> Member Schedule Excel Sheet

Outlook needs:
1) We need all 7 outlook calendars to go into one single excel sheet. We want it to happen on a weekly basis on Fridays.

2) The excel sheet needs to have variables for the owner, category, subject, start date, end date, attendees.

a. This is already in the code below

3) The code below needs to be edited to where it is automatic and not manual. At the moment we have to manually select the dates that the code draws from on the calendar. We want it to be an automated process to take place every Friday evening.

4) Also, we have a system of classification in place to say if the file is confidential or not. This has caused problems with the code when trying to save since it cannot tell the program what to do. This is a minor problem that we could probably work around, but would be nice to have it automated as well.

Master excel sheet needs:
1) The 7 calendars need to be imported into this one sheet

2) The variables mentioned above should be the columns

3) The code below does this well, but as mentioned, we need it to be automated

Member Schedule Excel Sheet:
1) This excel sheet has a list of the members with dates by day and m... Read more

More replies
Relevance 58.22%

I have list of suppliers in excel (column A)I need to copy each cell (A1,a2,a3?)select supplier from drop down in a web page (list will be from Excel)Where there are multiple links by clicking on it, it would open an invoiceTake the screen printfor particular regionSave it in the local drive

Answer:In Excel how do you Automate web tasks to take screen prints

Not sure about others, but it would help if you could include some screenshots of what you're talking about. Because it sounds like you're trying to collect data from multiple places and take a snapshot of it, yes? 20 years in IT and counting, baby\m/ > < \m/

3 more replies
Relevance 58.22%

I want to place a button in an excel spreadsheet which when pressed performs the refresh data action, rather than use right click.The only way I know is to record the refresh data action in a macro and assign it to the button. Is there a way of assigning one action to a button without using the macro recorder?

Answer:How to automate refresh data action in excel

I just did the macro and it took a few seconds, so I've really answered my own question.

1 more replies
Relevance 57.4%

New to the forum, and hoping that there already exists a solution to my problem, or that the community gurus can help...

I was tasked at work with creating several new pages (multiple files) of Visio schematics. I have an Excel spreadsheet that lists the major components as a sort of Bill Of Materials. Each major component has a Reference Designator (i.e. "2A7") associated with it.

I would like a way to automatically search through all of the Visio files to determine on which file(s) each Reference Designator appears. I can do the job manually by simply typing "2A7 " into the Windows Explorer Search window. But, obviously, this takes a lot of time, is prone to me transcribing something wrong, and requires me to re-do a search any time something changes, either on the drawing files or the BOM.

I've asked the same question on an Excel forum, and there's apparently some limitation that VBA has with accessing Visio's .vsd files.

More replies
Relevance 56.99%

I manage a small database of paid memberships for a non profit. We are keeping up with membership dues and renewal dates in an excel spreadsheet. I would like to automatically generate an email through outlook 30 days before the renewal date, again 14 days before the renewal data (if they haven't renewed), again 5 days before renewal date (if they haven't renewed), and again 5 days after it has expired. Furthermore, I would like to customize the message of the emails if possible, including my signature that is saved in outlook.

In my limited knowledge of VBA/Macros, I would assume that I will need a column that is checked if a membership has been renewed, so that the macro will know whether to generate an email. Also, I am assuming that this would be easier if I had multiple macros instead of just one.

I am attaching a excel sheet that has the columns we will be using with dummy data.

Thank you.
 

Answer:Automate Renewal Emails To Members Based Off Excel Dates

11 more replies
Relevance 55.76%

Speed up Outlook email chores: 5 ways to automate repetitive tasks
Anything that speeds up email communication in Microsoft Outlook has to be a bonus.
Typing and retyping the same paragraphs, the same information, explanations, directions, instructions,
and so on is half the problem, one that can be easily solved with these five, easy, time-saving tips.
Full story: http://www.pcworld.com/article/2902903/ ... tasks.html

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%

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%

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%

Im 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%

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%

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.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.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%

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%

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%

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%

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 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%

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%

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 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%

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 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 49.2%
Question: Excel Macro

I have two worksheets within the same workbook. WkSheet "Data" as my source and wksheet "Form" that I need to populate from the source.

I need to be able copy the quantity from my "Data" wksheet along column A, starting with A5 to my "From" wksheet in column A, starting with A9.

I need the quantity from the 'Data" wksheet to be pasted to the next open cell along the column A in my "Form" wksheet.

Can someone please help!!! Thanks.
 

Answer:Excel Macro

Hi, welcome.
A 'picture' tells more that a thousand words, but it's quite simple.

The other thing is; Which version of Excel are you using?
Code:

Sub CopyData2From()
Dim xRow As Long, yRow As Long
xRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
yRow = Sheets("From").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Data").Range("A5:A" & xRow).Copy
Sheets("From").Range("A" & yRow).PasteSpecial
Application.CutCopyMode = False
End Sub
 

2 more replies
Relevance 49.2%
Question: Excel Macro help

Few questions we've been having around the office. As the most junior on the programming team I get the short straw with the IT stuff cause our IT guy only comes once every couple of weeks.

Our accounting team has been utilizing more macros in their Excel work (2007) and we're having problems with editing macros. A macro won't work the way they want it to so they'll go to edit or delete it and come back with an error about hidden workbooks.

Did a little research and I think the problem comes from the settings in the workbook itself about having macros visible or enabled for that particular file because when I go to the macro settings and bring up their individual file they're working on the macro list is empty. (This also might by why the macros aren't working the way they want them to.)

So I think I need advice on how to edit macro settings for individual documents. If I haven't identified the problem correctly I would appreciate any information on how to fix my problem.
 

Answer:Excel Macro help

AFAIK, there's isn't really an easy way to edit macro settings for individual files. You can change the security level in Excel to something like low which would enable most macros by default but this isn't typically a recommended practice.

It's also possible that the user has disabled macros from running (i.e. clicked the Disable button at the opening dialog) and then can't edit / run any.

Something else to look at is if the macro has the designation Private. You can check this by opening the code window (ALT-F11) and then double-clicking the various sheets and modules in the left-hand pane. If any of the various modules have Private Sub listed at the top of the subroutine, it won't show up in the macro list (Tools > Macros), but will be available to run, and be available to edit through the code screen.

Also, for hidden workbooks, check under Window > Hide or Window > Unhide.
 

3 more replies
Relevance 49.2%
Question: Excel macro help

Hi,

I want to open the pdf using the pdf's url link and save it.

for Example:

In Cell A1 = http://amsdc1-s-9729-a2.europe.shell.com/ReportServer?/Shell.SSRS.CSandA.Reports.PrintToPDF/Report&rs:Format=PDF&rs:Command=Render&rc:Toolbar=False&rcarameters=False&FormID=416032&AccountGroup=YSTP&Country=FR &CompanyInformation=1&Credit=1&TaxVat=1&Order=1&Delivery=1&Payment=1&FormSupport=1&ExtendedWorkFlow=1&NameAddress=1&InternationalAddress=1&Classification=1&BusinessPartners=1&Setuponcaps=1&Assesment=1&Response=1&SalesOrder=1&CustomerMaterial=1&ListingExclusion=1&OrderTemplate=1&ShippingReceiving=1&MRN=0
In Cell B1 = The pdf name

How to do this in excel macro. It's very urgent can anyone help me on this....

Pleaseeeeeeeeeeeeeeeeee

Swamy
 

More replies
Relevance 49.2%
Question: excel macro

Does anyone know how to make the file name show up in the open portion of the dialog box?

When I use the command:
Application.Dialogs(xlDialogFindFile).Show
The dialog box shows up, but when you browse and find a file, the file name does not show up in the open box below. Although it doesn't cause a problem if you just hit the open button, it caused confussion with a friend of mine.

I found an example on the web for saveas:
Dim ck As Boolean
If newName = "" Then
str1 = "Enter New File Name Here"
Else
str1 = newName
End If
ck = Application.Dialogs(xlDialogSaveAs).Show(str1)
If ck = True Then
newName = ActiveWorkbook.Name
End If

and this one works good, but when I replace the saveas with findfile I get an error. Any suggestions?
Thanks,
Sly
 

Answer:excel macro

6 more replies
Relevance 49.2%
Question: Excel Macro

I am using Excel97. I have a twenty column worksheet with a combination of text and numbers. The data will be inputed by numerous sites and then sent to a central site for collation.
I have written a macro to do a specific sort automatically at the individual sites - Using column p (text) as Sort Key 1 and column q as Sort Key 2. Here is the part I cant figure out Column P is a mixture of blank cells and text entries. In order to sort the blank cells first I have the sheet replace the blanks with 1s and then I have it sort column P first and then column Q. But when that is done, I want the macro to ignore all the cells with 1s and switch so that the sort is done on Column Q first. After the first sort, can I then specify a range based on cell contents?
 

Answer:Excel Macro

7 more replies
Relevance 49.2%
Question: excel macro

i would really appreciate your help!

I would need a macro that can do the thing described in the attached file. so basically its a search tool on sheet2 of a workbook that gives back a specific value of the table on sheet1. so for ex. i write in row 6 and column C and it returns me the content of cell C6. Its an abstract thing so the purpose would be illustrating and easy search without typing in functions or manually go through the tables., so as like a user interface...
is there such a thing or is there a better software for a quick illustration?
 

Answer:excel macro

I added a form and some code, try and see.

You will have to program some more if you wish other actions or extend the data

This will work even if you add rows or columns without having to change the code.

I use the tips form the following site: http://www.xlpert.com/partH.htm
It's the first one that poped up when I searched in Google.
I hope it helps you
 

2 more replies
Relevance 49.2%

Excel 2013:

Hi Guys,

I have a simple excel macro that lopps through showing the sheets in a spreadsheet. All works well except once running I can't stop it.
Esc doesn't work
Ctrl Break doesn't work

Ideally I would like some code where I could assign a button to stop the macro running.

Sub StartSlideShow()
Application.OnTime Now + TimeValue("00:00:02"), "ShowNextSheet"
End Sub

Sub ShowNextSheet()
Dim lastIndex As Integer, nextShtIndex As Integer

If ActiveSheet.Index = Worksheets.Count Then Worksheets(1).Select

lastShtIndex = Worksheets.Count
nextShtIndex = ActiveSheet.Index + 1

If nextShtIndex <= lastShtIndex Then
Worksheets(nextShtIndex).Select
StartSlideShow
Else
MsgBox "End of slide show"
End If
End Sub
 

Answer:Help with Excel macro

7 more replies
Relevance 49.2%

I have a large excel spreadsheet. Each row is a separate record with multiple columns. The first column is an identifier (ID_x) and the remaining N columns are numerical features (features 1, 2, .... N). There are multiple rows (M) for the same identifier ID_x and corresponding N features. See the following example:

ID_1 feature1_1a feature1_2a .............feature1_Na
ID_1 feature1_1b feature1_2b.............feature1_Nb
...
ID_1 feature1_1M feature1_2M............feature1_NM
ID_2 feature2_1a feature2_2a .............feature2_Na
ID_2 feature2_1b feature2_2b.............feature2_Nb
...
ID_2 feature2_1M feature2_2M............feature2_NM
....

I want to create a new spreadsheet that contains a single record per ID_x. For each single record, the features are replaced with the average (or median, or stdev, ...) of the corresponding features. This is what I mean:

ID_1 average(feature1_1a,1_1b,...,1_1M) average(feaure1_2a,1_2b, ...,1_2M) ... average(feature1_Na, 1_Nb,...,1_Nm)
ID_2 average(feature2_1a,2_1b,...,2_1M) average(feaure2_2a,2_2b, ...,2_2M) .....average(feature2_Na, 2_Nb,...,2_Nm)
.....

Is there a macro to do this?

Thank you.
 

Answer:Help with Excel Macro

Hi Vicky,
Welcome to the forum.
In general macro's an be written for everything.
I don't know if it would help in your case but you could try a pivot table
A sample with non-private, non-sensitive data would help give a better picture and also you should always mention the version of Excel you're working with.
 

1 more replies
Relevance 49.2%

Hi,
I need some somewhat simple excel macro/scripting help.

Say I export a report from our database program into excel, basically, it is a 100+ page report with headings on each page. When we export to Excel, it shows the headings throughout. It is something like:

:BLANK SPACE:
:BLANK SPACE:
:BLANK SPACE:
Database: XYZ PAGE 2
USER ID: BLAH
:BLANK SPACE:
:BLANK SPACE:
:BLANK SPACE:

:Actual Data:

:BLANK SPACE:
:BLANK SPACE:
:BLANK SPACE:
Database: XYZ PAGE 3
USER ID: BLAH
:BLANK SPACE:
:BLANK SPACE:
:BLANK SPACE:

Etc.

What's an easy way to strip out that information so we have have concise spreadsheet? My guess, is: Search for the word "Database" and delete 4 lines above it and 5 lines below it.

Also,

When the report is exported to Excel, all the font formatting is stripped. So if we have lines like:

Office Supplies $300 (This is a total line, but doesn't state the word total)
Pens $100
Paper $200

Is there a way to search for a specific string, say I insert the word "total" to the certain lines, so it searches for that word "total" and it bolds entire land? Thanks for the help in advance, you guys rock!
 

Answer:More Excel/Macro help :)

16 more replies
Relevance 49.2%
Question: excel macro

Hi,
Everyday, I get a file with a list of items and thier prices.How would i create a macro in EXCEL to automatically copy the same data to four regions. This can be anywhere in the sheet and the item and price need to be in the same columns.For example:
item# price
5555 10.00
6666 20.00
7777 30.00
8888 40.00

I would have to do the same with regions 1,2,3,and 4
item# region price
5555 1 10.00
6666 1 20.00
7777 1 30.00
8888 1 40.00
5555 2 10.00
6666 2 20.00
7777 2 30.00
8888 2 40.00
5555 3 10.00
6666 3 20.00
7777 3 30.00
8888 3 40.00
5555 4 10.00
6666 4 20.00
7777 4 30.00
8888 4 40.00
 

Answer:excel macro

6 more replies
Relevance 49.2%
Question: Excel Macro

Excel_Macro
I am working with two worksheets. I would like to look at the value starting from A4 and down the A column in my master worksheet and when the value is greater than zero, copy all the data in that row from A to G and paste it in my destination worksheet from A to G. When pasting in my destination worksheet, i need to get back to the next empty row. Can anyone help me. I need this for my school project asap. Thanks.
 

Answer:Excel Macro

What kind of school project are we talking about. We normally don't provide the answers for what is otherwise considered "homework"

Rollin
 

1 more replies
Relevance 49.2%

I am creating a report for work and have stumbled upon a snag. Each month, we track the number of sales that each rep has, their volumes, etc. My boss wants me to create what is basically a report card for each rep. Each month I must resort the master report based on the three month total of each sales rep (column U); therefore, the data is never in the same order month to month (ie For May, John Doe could be #1 for the team [U5], but in June, he could be ranked #3 [U7]). Short of having to copy and paste each individual entry into the report card form, is there a macro of some sort or something that could make my life a little easier?

Thanks,
 

Answer:Excel Macro (?) Help

Welcome to TSG.

Without knowing your data layout it's difficult to say much ... BUT it's unlikely you'll need a macro to get what you want. You can probably get away with some basic functions, e.g. MATCH and INDEX.

The attached file should give you some idea of what you can do.

HTH,
Andy
 

2 more replies
Relevance 49.2%

Need help with some Excel VBA as I am not very good a programming!
I have a long list of records in a single excel spreadsheet and I would like to move a whole row based on the the data content in one of the cells to a new workbook with the cell data as the name of the work book.

bouitac
 

Answer:Excel VBA/Macro Help

16 more replies
Relevance 49.2%

Hi,
I have been working on getting multiple files to open in excel and maybe even organise the data in the format i want. A number friends have helped and here is what I have:

Public Sub open_files()
Dim intIndex As Integer
Dim fsSearch As FileSearch
Dim fsoFile As FileSystemObject
Dim tsmContents As TextStream
Dim strPath As String
Dim wksSheet As Worksheet
Dim intCount As Integer
Dim fdDir As FileDialog

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set fdDir = Application.FileDialog(msoFileDialogFolderPicker)
fdDir.Show
fdDir.AllowMultiSelect = False
strPath = fdDir.SelectedItems(1)
Set fdDir = Nothing

Set fsoFile = New FileSystemObject
Set fsSearch = Application.FileSearch
fsSearch.LookIn = strPath
fsSearch.Filename = "*"
fsSearch.Execute
If fsSearch.FoundFiles.Count > 0 Then
For intIndex = 1 To fsSearch.FoundFiles.Count
intCount = 0
Set tsmContents = fsoFile.OpenTextFile(fsSearch.FoundFiles(intIndex))
Set wksSheet = ActiveWorkbook.Worksheets.Add
wksSheet.Name = fsoFile.GetFileName(fsSearch.FoundFiles(intIndex))
Do
Call Import_Contents(tsmContents.ReadLine, intCount)
intCount = intCount + 1
Loop Until tsmContents.AtEndOfStream
Next intIndex
End If

Set fsSearch = Nothing
Set wksSheet = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

The trouble is the code is not really working in my worksheet as I keep getting compile errors.

AS I described before, I want the... Read more

Answer:Help with Excel macro

What does the Compile error tell you?
 

3 more replies
Relevance 49.2%

Hey guys I am working on a spreadsheet for this cinema thingy at school and I wannaget top marks sooo can any of you tell me or show me how to make a macro to reset certain cells ie B2-B5 by reset i mean change the number to 0 please help I rlly need help i cant write macros lol
 

Answer:Excel macro help please

6 more replies
Relevance 49.2%

I have a macro written in VB that works but not "perfectly".
Basically it looks at a range of cells and replaces X text with Y text. Very simple.
The problem that I have is I would like for it to replace the text with a literal match.
For example:
Replacing "B" with "BOMB" on a row that has "BOMB" on it would result is "BOMBOMBOMB"

What happens is it takes every "B" and replaces it with "BOMB".
What I would like it to do is to replace cells that have literally only "B" in them with "BOMB".
If a cell has anything else than just "B" it is ignored.

Here is the code:

Code:

Sub Replace_ALL()
Dim lr As Long, c As Integer, r As Integer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

lr = Cells(Rows.Count, "A").End(xlUp).Row

For r = 1 To lr 'cycle rows
For c = 1 To 1 'columns A to A
Cells(r, c).Value = replace(Cells(r, c).Value, "B", "BOMB")
Next c
Next r

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
 

Answer:Excel VB Macro help...

Here's a quick and dirty solution, just test for the length of the cell before substitution:
Code:
Sub Replace_ALL()

Dim lr As Long, c As Integer, r As Integer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

lr = Cells(Rows.Count, "A").End(xlUp).Row

For r = 1 To lr 'cycle rows
For c = 1 To 1 'columns A to A
If Len(Cells(r, c).Value) = 1 Then
Cells(r, c).Value = Replace(Cells(r, c).Value, "B", "BOMB")
End If
Next c
Next r

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
 

2 more replies
Relevance 49.2%
Question: excel macro

does anyone know a macro code line to move to the next row...for instance i am in a4 and i want it to move to a5 then a6

thanks
doug
 

Answer:excel macro

8 more replies
Relevance 49.2%
Question: excel macro help

I am trying to copy selected cells from a source worksheet into a seperate woksheet in the same workbook.

Each record in the source worksheet (named 'master') starts with a transaction number (column A), Bank name (Column B), Amount (C),Booking unit (D), confirmation date (E), and various different fields with the last field in column AF.

When I go to column A of a particular record (in my 'master' worksheet)and press ctrl+t (my shortcut to the macro) I want all the cells in that particular row, from column A to column AF to be copied into the other worksheet called 'slave'. I want this to be pasted starting from column BB row 2 in the slave worksheet.

When I go to another record in my master worksheet and press ctrl+t I want the same thing to happen, BUT NOW i want this pasted in the slave worksheet in coloumn BB row 3.

I want this to happen every time I select a record to be transfered.

I know this may be asking for a lot of help, but would really appreciate any kind of help on this as it is an urgent requirement for my work.

Thanks in advance
 

Answer:excel macro help

6 more replies
Relevance 49.2%
Question: Excel Macro Help

Hello All,

I am using Access 97 to generate a database. After entering data into forms in Access, the user then clicks a button which opens a report in excel. I have created a macro in excel which should automatically maximize the worksheet in excel. This macro also changes the active worksheet to "sheet2". The part of the macro which changes the active worksheet is working fine when I load the report through access, but not the part of the macro associated with the maximization of the window. Both parts of the macro work if I open excel on its own (ie: not through access). Below is my code:

Sub auto_open()

Worksheets("Sheet2").Activate
ActiveWindow.WindowState = xlMaximized

End Sub


Any suggestions?

Thanks in advance for your help.

engineer_in_training
 

More replies
Relevance 49.2%
Question: Excel Macro

I have a CSV file that exports to excel and produces and sorts various pieces of data with some simple macros, what I am then left with is a sheet of information that I need to paste into a MSWord table, the table already has the font and style formats that I need, but because the data is based on the information from the Excel workbook I cannot predict the number of rows needed, although this number is displayed in a single cell in the workbook. When the table has been pasted I would need to be able to rename and save the Word document using a week number, displayed in the workbook as the file name. Is it possible to write a piece of VBA code to handle this?
I should have said I am running office 2002
 

Answer:Excel Macro

8 more replies
Relevance 49.2%
Question: Excel macro

Excel macro

A friend has a spread sheet he receives every week. He has to copy info from that sheet and past it into a new Excel sheet in a different order and then save the new sheet as a "prn" file.

He wants to create a macro (or two) that will do as much of the following as possible.

The following cells are always the same.

He wants to copy the following cells and then paste each cell area below the previously pasted cell area with one blank cell between each cell area.

The "pasted" info is then saved as a "prn" file.

Here's the cell info he's coping.

D7-F18
D21-F32
H7-J18
H21-J32
L7-N18
L21-N32

I can create a macro but this is out of my league.
Questions? Solutions?
 

Answer:Excel macro

7 more replies
Relevance 49.2%
Question: excel macro help

I am writing a macro that will clean up a lager exported from an accounting program.

I have the following sheet:
Now:
A B C
1 GL Account-8600
2
3 1 / 2011
4 1121
5 1416
6 1416
7 1416
8 1121
9 1121
10 1121
11
12 2 / 2011
13 1416
14 1121
15 1121
16 1416
I would like cell A3 copied and pasted until they have an empty cell. In this example, it will be A3-A10 will be changed to the number 1, and cell A13-A16 will have a number 2. (The amount of rows will vary) I will not have a number greater then 12 months. Also, the empty rows (A2, A3, A11 and A12) and the row below needs to be deleted. I will use this data to make a table in excel, ant the macro has been written for that.

I would like the following outcome:
A
1 GL Account-8600
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 1
11 2
12 2
13 2
14 2
15 2
16 2
 

Answer:excel macro help

8 more replies
Relevance 49.2%
Question: Excel Macro Help

I bought a 50,000 name phone fax list from experian. The list came with the phone numbers in the format of: 9092248212.

Winfax, my fax program, does not work with that format and experian will not change it for me. I need to build a macro that will strip the first 3 numbers (the area code) and either put that in a separate column or change the format to: 909-224-8212 (preferably instruction to do both if that is not WAY too much to ask)....

I am getting kind of desperate! Any help given will be (GREATLY) appreciated. I have used Excel since it was invented but this kinda throws me....

Regards,
Jim K
[email protected]
 

Answer:Excel Macro Help

6 more replies
Relevance 49.2%
Question: Excel Macro's

Hello,

I have developed an approx 50 page (work tabs) spreadsheet in Excel 2003 (Windows XP).

The workbook contains three main macro?s and after several manual inputs are performed the salaries for the organization are projected.

When I send out the report to the applicable parties, I hide the other 49+ tabs and then protect the workbook with a password to ensure the other parties cannot see the other areas information.

Due to the size/complexity of the document I am unable to delete the tabs (takes a long time) and that is why I have hid the rest of the tabs.

Each tab runs on the three main macros and the figures that are generated are rolled up in summary pages for the applicable areas (these summary pages do not used the macros)

Question #1: in a complex workbook is there a way to delete the tabs quicker?

Part 2:

A majority of the parties still use Windows 2000 (Excel 2002) due to other applications that are based around this operating system.

They are able to update/save the report and any changes that are made are accurately reflected. However, when the Excel 2002 user saves the document and sends it back to me it takes an extremely long time (45 mins) for their report accurately show the figures. This message occurs in any cell that requires the macro:

#NAME?

Another piece of information that I obtained said:

Microsoft Office Excel re-calculates formulas when opening files last saved by an earlier version of excel.

This leads me to believe that since the... Read more

Answer:Excel Macro's

Your problem reads like there's a lot of unstated constraints in your situation, like resources and procedures for distribution, management, return, collation. Too much for me. I'm looking here just at a more efficient message setup operation.Sounds like much of your overhead irritation relates to bothway transmission of large quantities of irrelevant (even though hidden) data. Here are three lines of thought:1. Another macro to generate 50 individual spreadsheets from your workbook.2. Rework your base dataset in EXCEL to bare-bones tabular form, to enable mailmerge into a user-friendly WORD template. I've done this with 50 data fields into a 20-page 85-client report template, and it's a ho-hum WORD mailmerge task once you get the master doc looking pretty.3. Take this on to include the transmission stage with the article "Integrate mail merge in Microsoft Word with Excel" at TechRepublic, at http://techrepublic.com.com/ The direct approach http://search.techrepublic.com.com/index.p...l&go=Search might even work.I'm afraid any of those options is a week's work, but that's why we're paid so well.

1 more replies
Relevance 49.2%
Question: excel and macro

I am new to macros. I want my excel sheet to populate a ready-made word doc by putting in the data from the cell I click on, and no more than that.
Anyone help an absolute beginner to this stuff?
 

Answer:excel and macro

moved to business applications forum
 

2 more replies
Relevance 49.2%
Question: Excel Macro Help

I have started recently to mess around with macros but I can't quite figure out how to do this one.

I have this list of songs with three columns (artist, title and track #) but what i want to do with it is to insert a row between each artist while dropping down the track #. But when the artist is the same i want to add these track numbers.
Attached is what i have and on sheet 2 is what I am trying to figure out if my explanation is not too clear.

Any help would be greatly appreciated
Thanks a lot.
T.
 

Answer:Excel Macro Help

Why not apply a Pivot Table?
I did it in your sample,
The total column is necessary or else it won't show anything but I think this is waht you want?
 

1 more replies
Relevance 49.2%

I am designing a ?basic? database in work and having trouble with some macrosThe database is a record of production. Information is added at pre and post production. Adding the pre-production information I have sorted. A form is filled in, submit button hit and a new row is created with the information in. Along this row there are gaps where post production information is entered, but trying to get this information to paste into the correct row I cant do.I have a form to fill in the information (one each for pre and post production) which I then want adding to the database against the reference number. Converting the information from the input form into the database I can do for pre production, but getting the post-production data entered into the database along the correct row I don?t know how to get there.I believe I want a search function that takes the database reference number (on the post-production form) and finds this in column A. once this ref number is found, the cell is active and I can to paste the information from the post production form into the gaps on the correct row Anyone have a clue?

Answer:help with Excel macro

Can you clarify a point here?  Your title is "help with Excel macro".  Excel is a spreadsheet program, yet you refer to a database over and over and do not once mention a spreadsheet or other terms, such as Workbook or worksheet, which imply Excel.  Macrosoft Access a database and is part of the Pro version of Microsoft Office.  So, are you using Access?  If not, I'm curious why you keep referring to database when you are using Excel, a spreadsheet program.  Or, are you using both for that application you are working with? 

10 more replies
Relevance 49.2%

Hello.... I am trying to prepare some reports for my boss and I am stuck on the macro I am writing. somehow I need to put the date in as a variable. you know hte same way that excel has the today() formula but I don't know what the code for that is. Can anyone tell me how to state that the variable TDay is equal to the current day so that tomorrow it will be automatically equal to tomorrows date?

Thanks

 

Answer:Help With Excel Macro... Please....!!

Tday = date

does this work for you?
 

3 more replies