Computer Support Forum

need help writing a macro that takes Excel file to email

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

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.

Relevance 100%
Preferred Solution: need help writing a macro that takes Excel file to 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.)

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

I am trying to write a macro that calculates the total for all member id's and places them into a new sheet with column A being member id and column B being the total for all member id's
In column A I have a member id and in column B i have corresponding amounts.

I know its confusing. For example
sheet1
col a col b
111 350
111 400
222 500
333 250
333 400

now result in sheet2
col a col b
111 750
222 500
333 650
Keep in mind that there are about 8000 rows with data.

Any help would be greatly appreciated.

Thanks
 

Answer:excel help writing macro

6 more replies
Relevance 79.95%

Hello, I am trying to figure out how to get excel to scan a column of Dates and insert a row where there is missing data. I am new to macros and have recorded some and ran them successfully; however, I can not figure out how to incorporate a logic formula or statement into the macro. Any tips. Cheers
 

Answer:Excel macro writing

Here are a few tips for prospective excel macro users who want to develop their skills further from just simply recording a macro.

1. Once you have recorded your macro, go back to the macro and click on edit.
2. More than likely you will have to create a variable to manipulate your macro. Type DIM demand as integer or DIM fan as string.
3. You will have to create cursors to keep track of where the program is to insert into.
4. If performing complex column and row manipulations such as inserting rows, you will have to learn R1C1 notation e.g. A3 is R3C1. Its easier to manipulate 2 sets of numbers than one alpha and one numeric.

Summary: Learn Visual Basic, creating variables, object oriented design programming and basic maths.
 

1 more replies
Relevance 79.95%

I have a project that I need to compelte by EOB today. What i need to figure out is create ranges for the data.

I have a list of 5000 locations. I need to put them in a bucket of beginning location and the ending location should be next 24 rows down.
Meaning -- for example A1 will be my beginning location and then A26 will be my ending location and then A27 will be my beginning location and then A51 will be my ending location and so on.
PLease help me out with this -- I spend my entire weekend on this and I cant figure it it.

When I run a macro it does only those rows that I record but I need it to go down the list and do the same for the entire row which has around 5000 rows.

Let me know if you have any further question -- All your help is appreciated in advance.
 

Answer:Need Help Writing Macro In excel

Are you saying that you want to perform the same code on every 25 rows of data until you reach the end of the sheet? What is the action you are trying to perform in the macro? Your description is a bit vague and I need a better understanding of what you are trying to accomplish. More detail please and we'll be glad to help.

You are going to use a loop to execute the code until you reach the final row.

Here is a simple loop example. Just replace the messagebox with the code you want to perform.
Code:

Range("A2").Select

Do While ActiveCell.Row <= _
Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox (ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop

End Sub
Regards,

Rollin
 

2 more replies
Relevance 79.13%

I am attempting to write a macro that will do the following:
If A2=A1 set B2=B1
At first glance I know what you are thinking; just type that in as a formula dummy. Well, I would like it to run down an entire column of 6000+ entries such that also:
If A3=A2 set B3=B2
If A4=A3 set B4=B3
If A5=A4 set B5=B4
Etc.

I have a macro that I recorded and it gets the job done but
A) It is really sloppy
B) It does not take user selection into consideration such that
If I were to select Column E I want
If A2=A1 set E2=E1
If A3=A2 set B3=B2
If A4=A3 set B4=B3
If A5=A4 set B5=B4
Etc.

Any input will be sooo much appreciated. Here is the code I have right now that has limited functionality:
I was attempting to copy column D on this run.

Sub Macro1()
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.NumberFormat = "General"
Range("D6").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-3]=R[-1]C[-3],RC[-1]=""""),R[-1]C,RC[-1])"
Range("D6").Select
Selection.AutoFill Destination:=Range("D6:6287"), Type:=xlFillDefault
Range("D6:6287").Select
ActiveWindow.ScrollRow = 6251
ActiveWindow.ScrollRow = 6242
ActiveWindow.ScrollRow = 6233
ActiveWindow.ScrollRow = 6224
ActiveWindow.ScrollRow = 6206
ActiveWindow.ScrollRow = 6198
ActiveWindow.ScrollRow = 6180
ActiveWindow.ScrollRow = 6126
ActiveWindow.ScrollRow = 6055
ActiveWindow.ScrollRow = 5922
ActiveWindow.ScrollRow = 5761
ActiveWindow.ScrollRow = ... Read more

Answer:Solved: Need help writing an Excel VBA macro please!

PS I meant

I have a macro that I recorded and it gets the job done but
A) It is really sloppy
B) It does not take user selection into consideration such that
If I were to select Column E I want
If A2=A1 set E2=E1
If A3=A2 set E3=E2
If A4=A3 set E4=E3
If A5=A4 set E5=E4
Etc.
 

3 more replies
Relevance 79.13%

Hi gurus,

Help me score some points with my boss, please. I created a spreadsheet to keep a schedule of meetings for a conference room. I used a formula to automatically populate the correct dates for Monday - Friday of each week (only the current 5-day work week is displayed).

I have no idea how to write VB syntax, so I need some help. Will anyone help me? This is what I need.

When someone pushes a button, which we'll call button 1, I need a macro to do this. I'll figure out the rest....

Set cell B7 = cell B8
Set cell C7 = cell C8
Set cell D7 = cell D8
Set cell E7 = cell E8
Set cell F7 = cell F8

I'd really appreciate it! I'll figure out how to modify this macro to suit my other needs, I just need a starting point!

Thanks,

Richard
 

Answer:need help writing a microsoft excel macro

6 more replies
Relevance 79.13%

Hi,

Im building a tool using Excel that resolves discrepancies between purchase orders and invoices. Things are moving along fine, but I would like to record/write a macro to perform a task. My macro knowledge is at a beginner level. The following is the task I would like to perform. If Sheet 2 (VNDR ADJ WKS), Cell GP78= PO ADNL CST = INV ADNL CST I would like a macro to automatically move me to Sheet 3 (PO REC WKS) and land me at Cell C450/Range("C450:BQ452"). Is this task possible via a macro and if so could you help me record/write the macro?

Thanks,

Warren
 

Answer:Writing a specific macro within Excel

Code:
Public Sub Test()

If Sheets("VNDR ADJ WKS").Range("GP78").Value = "PO ADNL CST = INV ADNL CST" Then
Sheets("PO REC WKS").Select
Range("C450:BQ452").Select
End If

End Sub
How will the macro be fired? Will you run the macro manually or do you want the code to kick off automatically under a certain condition? What exactly are you trying to accomplish? Many times there are much easier ways to do things that you may not know about. If you state exactly what you are doing maybe we can help you accomplish what you want more efficiently. If you need more help, include as many details as possible and try to post your workbook by using the "manage attachements" button at the bottom of the posting window.

Regards,
Rollin
 

1 more replies
Relevance 78.31%

Hey all,

Im currently working on a macro of rather large size and im a little stuck at the moment.
The endgoal of the macro is to have a clean file out of all the mess and that can be mailed to other pple of my staff. Now here it comes:
In my example there are 3 tabs: Raw file, Database and Endfile. The point of the whole macro is to look up some things between Raw file and Database and then to write it to the Endfile. The Raw file and the Database are allready macromade and all three sheets have a layout that cant be changed.
--> First thing is that i need to check if the Rapprt N in column L of the Raw file is somewhere in the Database file at column M (CINCUS).
--> IF the number is there I need to check the type wich can be found at column S (CINSTA)
--> IF THEN that type is 7 then it has to be written away to Endfile, what has to be written away is colored blue in my example.
--> IF THEN that type is 8 it has to be written away to the sheet "Type 8" for further investigation, the data is the same (blue colored)
--> IF the number is not found it has to be written away to the sheet "Not Found" with also the same data.

I think thats it, its much i know. But im stuck in finding all the formulas and stuff. So some help is greatly appreciated. Thx in advance.
 

Answer:Macro excel matching and writing files.

9 more replies
Relevance 78.31%

I am looking for a macro that willl do the following:
I have a date in cell A12
I want to put that date in each cell of column b as long as the corresponding cell in column a has a number in it.
After all dates are entered, I want the macro to skip two lines and start again, but this time add one day to the previous date.
Can someone help me on this?

Here is a screenshot which will hopefully clear up what Im wanting to do.


 

Answer:Solved: need help writing macro in excel 2007

16 more replies
Relevance 76.67%

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.
 

More replies
Relevance 75.85%

I need to write a macro that will copy data from one spreadsheet into a separate spreadsheet (not in the same file). Here is the scenario...

On the first spreadsheet I have a customer's name in one column and a customer's address in another column. On a separate spreadsheet a have those same customer's names without the addresses. On the second spreadsheet the customer's name may be listed more than once. What I need to do is add the corresponding address to the second spreadsheet beside the name of the customer (identical data from both spreadsheets). The reason copy and paste won't work is because the customer may appear numerous times on the second spreadsheet and their are approximately 50,000 rows of data.

The macro needs to basically say IF Spreadsheet 1, Column A (customer) equals Spreadsheet 2, Column G (customer) then ADD/REPLACE info from Spreadsheet 1, Column B (address) to Spreadsheet 2, Column H (currently blank). I just can't figure out how to make it do that. ANY HELP ANYONE CAN GIVE IS APPRECIATED!
 

Answer:Solved: Need Help Writing a Macro for Excel to Move Data From One Spreadsheet to Anot

7 more replies
Relevance 69.29%

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

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

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%

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%

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%

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%

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%

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

Hi

Ive been searching the net for help with this and i just cant find what im looking for.

Im still new at excel macro's so still learning everyday.

I need help to wright a macro that will search in a folder for a file with a specific keyword that can be in the file name.

example: lets say i want to open a file that is in a folder that has "leadership" in the files name.
Because i send the file to people called "leadership.xls" and i recieve files back called "copyofleadership.xls" and different other names but there will always be leadership in the file name.

Can anyone please help me with this.

Thanks
bill6432
 

Answer:Excel macro to open a file in a folder with a specific keyword in the file names

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

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

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

Hello,I have been running a tab-delimited text file through a macro tfor about a year, which puts the data for each accounting number into separate tabs. In the future, I will be receiving Excel files. The Excel files that I was provided with will not run through the macro. I have little experience with macros, but as far as I know, the macro searches for the word CAN in the file, retrieves the actual accounting number from the next line to label the tab, and pulls all the data into that tab until it comes to the word CAN again. The process then repeats. It creates about 40 tabs, which represents the accounting numbers in the file. When looking at the Excel file and the tab-delimited text file, they look the same. Do you have any idea why the Excel file would not run? Any help would be greatly appreciated!Thanks,Christine

Answer:Macro Problem using Excel file

It might help if you posted the macro so we can see what it is doing.Before you post the code, please click on the following line and read the instructions on how to post code in this forum.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 64.37%

I have two excel files.

File A and File B

File A contains the column DRS Id.
File B contains the column TC ID and DRS Id.

I have to write a macro to pick the DRS Id from File A and search in File B for the DRS Id and pick out the corresponding TC Id. If there are more TC Id's for one DRS Id then I have to put that with a comma separator.
How to write a XL macro for this?

Please help on this regard.
 

Answer:Excel Macro to search for a value in another file

Hi, Welcome to the forum.
First thing I advice is to Always mention which version of Excel you're using. For VBA that isn't that much of an issue but it does help if we know what type of file we would have to attach for you.
Your question is not that difficult and yes that can be done relatively simple.
Could you attach two sample files, one for the 'A' file and one for the 'B' file with some dummy data in them?
You name the column's headers but it's easier to see than trying to imagine it.
You mention comma separated for what? Output?
 

1 more replies
Relevance 64.37%

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

Hello.

I have been trying to work out how i can select a file from mY c drive to add as aan attachment

I know how to build the E-mail up i am looking for how to select and install an attachment.
 

Answer:Add PDF file to e-mail, excel, Macro, attachment

Are you looking for a macro to include the .pdf file in a excel macro ? Could you please give more information about your need ?
 

2 more replies
Relevance 63.55%

Hey,
Can someone tell me what I need to do to have a macro in excel, open another file?
For example, I need the following file opened:
c:\FolderA\FolderB\FileC.xls
How do I do this, and also, at the end of the macro, I want that file closed. Please help!
Thanks,
 

Answer:VBA Code to open file in Excel Macro

Give one of these lines of code a shot...
Code:

Workbooks.Open("Book2.xls")

or
Code:

Workbooks.Open("C:\MYFILES\BOOK2.XLS")

What are you going to do with the spreadsheet once opened?
 

2 more replies
Relevance 63.55%

hi i need to search a file using macro in excel the name of the file is mention in cells A1,A2...................... so i wanna search that mention files and get the last modified date of paricular files only in the same sheet in cell F1,F2................... can this be done using excel macro, if so can you provide me that coding

Answer:hi i am navin, i need to search a file using macro in excel.

Here is something i used a long time ago, i have modified it for your requirements, you can try do that yourself. this will give you additional information as well as the last modified date of a file, However this works on a file path rather than file names within a worksheet.

Sub TestListFilesInFolder()
Workbooks.Add ' create a new workbook for the file list
' add headers
With Range("A1")
.Formula = "Folder contents:"
.Font.Bold = True
.Font.Size = 12
End With
Range("A3").Formula = "File Name:"
Range("B3").Formula = "File Size:"
Range("C3").Formula = "File Type:"
Range("D3").Formula = "Date Created:"
Range("E3").Formula = "Date Last Accessed:"
Range("F3").Formula = "Date Last Modified:"
Range("G3").Formula = "Attributes:"
Range("H3").Formula = "Short File Name:"
Range("A3:H3").Font.Bold = True

ListFilesInFolder "C:\", True
' Change this to your folder location make sure you have the "\" at the end

' list all files included subfolders
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long

Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder(SourceFolderName)
r = Range... Read more

2 more replies
Relevance 63.55%

Hi,

I have no idea about vb script though i tried to record a macro which does not solve my concern.

I have an XML file which i want to open in excel , which when done manually i select the 'As a Read only workbook' option.

So the excel should have a browse option to provide the xml file location and macro should then locate few columns (search the column header) and fetch the entire column values of the searched string and paste the details in new tab.

Please help me with this code.

Thanks in advance

More replies
Relevance 63.55%

Hi,

I'm hoping someone with a fair understanding of Excel and VBA can give me a hand here

I don't really use Excel much, even less the VBA side of things (more web type things).

Here is the problem, I receive a file of tabulated data in Word format which I need in Excel, with the data in the correct cells. Currently someone is transcribing this manually which is taking about a week every month

It is a fixed file format (columns always start in the same place) so can be saved as a txt file, but being fixed format there is no delimiter so Excel dosen't know what to do with it and just sticks the whole thing in cell A,1.

I have read that there is a funtion workbooks.opentext which might be able to help but i'm unsure of the parameters and how to implement this

I'm using Excel 2000 with the built-in VBA 6 (I think).

Any help is gratefully received
 

Answer:Excel Macro to Read in Text File - Help R

9 more replies
Relevance 63.55%

Hello. I hope anyone can help me with my little problem. I been trying to do this for two days, without sucess.

My problem:
I have a problem with a part of my macro:

I have one long row in my excel sheet I would like to copy into a text.file. And at the same time you should be able to save the text.file wherever you want on your computer. I believe this shouldn't be to difficult, but I haven't managed. Tried to use CreateTextFile method combined with GetSaveAsFilename method...

Then later I need a new macro: This should ask where to find the text.file. You choose the location and the macro copies the data from the text.file into the same row in the excel sheet as it was pick from. And then another macro will use this row to some other stuff. Is this possible and how?

Might sounds strange to do it this way, but the clue is, is that my excel sheet is supposed to be on a cd, and not be save into the computer, only the text.file will be saved (too save space on the computer)

Would be really greatful for any tips that could help me!
Have a good day!!

Bjorn
 

Answer:Macro question: Get one row from excel to a text.file

You really should post some sample data to your next post so that we can see exactly what you are talking about. If possible, post a sample Excel workbook with data and the corresonding text file that should be created. Writing to a text file shouldn't be too difficult. When you say text file are you referring to a true .TXT file or another format such as .DOC

Rollin
 

3 more replies
Relevance 63.55%

I have a macro in EXCEL that will feed various SQL statements into ORACLE. It's fine when the SQL statements are fairly brief to just hard code them into the macro. I'm wondering whether there is a practical way to include a large SQL statement in the macro by some sort of indirect reference. I tried submitting a "Start command" followed by a text file location containing the SQL code in the macro, but it errored out with an SQL syntax error, even though the same statement worked just fine in SQL*Plus.

Any ideas on "elegant" ways to include large SQL statements, such as creating a rather complex view in an EXCEL macro? I'd rather not have to deal with all the extra punctuation created when the SQL code is coded directly into the macro.

My VBA skills are fairly intermediate at best, so please don't hesitate to be include details.

Thanks!
 

More replies
Relevance 63.55%

I am trying to create a macro that will look at the contents of a cell and insert it into an address on the server. For example, the contents of the cell is: folder\file

The location to hyperlink to is: \\server\share\folder\file.doc

I need to keep the file extension in the macro so that clicking the link will automatically open the file. I just need to get the contents of the cell to fill in the folder name and file name.

Thanks in advance.
 

Answer:Solved: Excel Hyperlink Macro to File

16 more replies
Relevance 63.55%

I am looking to write (or record preferably) a Macro where when I click on a button I create (let's say it's named "Open") a new worksheet is opened and the Open menu screen is displayed in that worksheet. I would then search for the file that I want imported and when found and chosen it of course is brought over to my worksheet. I have zero experience writing complex Macros and again would prefer to record this as opposed to writing it.

Answer:Macro To Open a File in Excel 2003

"brought over to my worksheet" how?How To Ask Questions The Smart Way

3 more replies
Relevance 63.55%

Windows XP SP2:

It now takes 3 minutes or so to attach any size file to an email program.

It is the same for both Outlook Express and Mozilla Thunderbird.

Everything else seems to be working okay.

I suspect it may be associated with filepicker but I am not sure.

Have Norton firewall and Avast antivirus running, and have run Ad-aware and Spybot.

Couldn't find any solutions in seach engines.
 

Answer:attach any file to email takes 3 minutes

13 more replies
Relevance 63.55%

I have a large excel file (8mb) that takes 15 min to open on 2 PCs.I think it started after the file exeded 7mb, before that it was about 30 sec.
Both Pcs have XP pro sp3 and office 2007 and about the same spec(c2d processor, 2gb RAM),well there is now 16 PCs on the same Domain connected on the same switch 24 ports, the point is that the 14 other pcs open that same excel file (witch is on a network drive on the server) and it open in less than 10 seconds!
I double cheked the cables if they were damaged and replaced them without any speed up.
But i noticed that all the other PCs were "Win7-office2007" - "win7-office2010" or "winXP-office2010" leaves only 2"XP-2007" so i wonder if there is some kind of issues when opening big files in excel2007 with XP????? or any way to fix it, this is some kind of annoying lol!
2007 sure takes longer to open files than 2010 but that much? I open 6mb files on the same pc and it take less than 20 sec!! and 8bm -->15min! NO WAY!
The same 8mb file on a XP-office2010 machine take less than 10 sec to open the file....
all the OSes and office are up to date.
well, any thoughts???
 

Answer:Solved: excel file takes 15min to open

6 more replies
Relevance 63.14%

After running macro1 in my .xls file, macro2 is very slow, requiring 97 seconds to process on my computer.
However, if I hit the save button between running the macros, macro2 only requires 33 seconds to process.

I believe the code in macro1 is the issue, so I have pasted it below. Sorry if the format is incorrect for this forum, but I could not quickly find the correct way to post code.

Is there any way to unload the large amount of data or memory that is being monopolized? Or could my code be streamlined better?

Sub Macro1
'
' Macro1
' Macro recorded 11/17/2008 by *
'
Columns("A:B").Select
Selection.Copy
Sheets("Peaks").Select
Columns("A:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Input").Select
Range("A1").Select
Sheets("Peaks").Select
Range("B1").Select
LastRow = Range("B65536").End(xlUp).Row
Range("C5:C" & LastRow).FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"
Range("D5" & LastRow).FormulaR1C1 = "=R[1]C[-1]/RC[-1]"
Range("E5:E" & LastRow).FormulaR1C1 = "=IF(RC[-1]>0,FALSE, TRUE)"
Range("A1").Select
Range("E1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="TRUE"
Range("A1").Select
Columns("A:B").Select
Selection.Copy
Sheets("Extremes").Select
Range... Read more

Answer:Solved: Excel Macro slow until .xls file saved

Code:
Sub Macro1
'
' Macro1
' Macro recorded 11/17/2008 by *
'
Columns("A:B").Copy
Sheets("Peaks").Columns("A:B").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
LastRow = Sheets("Peaks").Range("B65536").End(xlUp).Row
Range("C5:C" & LastRow).FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"
Range("D5:D" & LastRow).FormulaR1C1 = "=R[1]C[-1]/RC[-1]"
Range("E5:E" & LastRow).FormulaR1C1 = "=IF(RC[-1]>0,FALSE, TRUE)"
Range("E1").AutoFilter Field:=5, Criteria1:="TRUE"
Columns("A:B").Copy
Sheets("Extremes").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Peaks").AutoFilter Field:=5
Sheets("Input").Select
End Sub
Firstly, remove all the selects, its completely unnecessary, its the way the macro records it, but we remove them all in VBA so that we can actually just directly affect them without needing to navigate sheets or select cells.

You unnecessarily select range A1 5 times "Range("A1").Select", and then go somewhere else - all out.

I've only done this roughly, and so may need to iron out any errors, test it and let me know if you get any errors.

P.s. (edit): Copying the whole of A:B seems like a bit of an overk... Read more

3 more replies
Relevance 63.14%

hi

I'm trying to get a macro to open when the worbook is opened but can't get it right

private sub workbook_open ()
macro1
end sub
any suggestions?
 

Answer:atuot start a macro when the file opens in excel

White: If Macro1 is the name of your macro, it should work as you have it.

You could also try:

Call Macro1()

or

Call Macro1

(I forget)
 

2 more replies
Relevance 63.14%

HiI'm still really new to VB, but I'm writing a macro which copies data from spreadsheet to another when a submit button is clicked. However, because two people could be trying to do this at the same time, I want to include at the beginning of the macro a process where it checks whether the file needed is already open by someone else. If the file is open, I need it to create a message box asking the user to try clicking submit again in a couple of minutes, with the vbOKonly option.If the file is not already open, I want the macro to continue running. Any help would be much appreciated, as I'm going round in circles :-)

Answer:Macro needed to check Excel file open

Define "the file."How To Ask Questions The Smart Way

5 more replies
Relevance 63.14%

I have been reading up on creating log files with Excel Macros.

I am trying to figure out if it is possible to have a Macro output the contents of the currently selected Cell to a text file?
 

Answer:Solved: Excel Macro Copy Cell to log file.

16 more replies
Relevance 63.14%

Hi all,
This is just a preliminary request for advice before I undertake writing a fairly substantial VBA macro.

My plan, in short, is as follows:

- User receives a daily Invoice file. The data from this is to be dropped onto an Excel Table I've prepared (let's call this the Invoiced Table) in the Master File, which will allow some formulae, such as IFs and Vlookups to static tables, to auto-populate. Each day's data is to be appended to the bottom of the table.

- User receives a weekly Paid file. The data from this will be dropped into a second Excel Table (let's call this the Paid Table) on another worksheet in the Master File.

- Once the weekly file data has been dropped in, the user will run the macro. This will compare records from the Invoiced Table against the Paid Table using at least 4 (probably 8) different sets of lookups. In a simplified example, these might be 4 different customers.

- Each set of lookups will result in some matches. For each matching record for a specific customer, fields from the Invoiced and Paid tables need to be combined into a new record on a seperate worksheet.
So in a simplified example, Record 1 might have an Invoice Number in the Invoice Table that matches the Invoice Number in the Paid Table. I'll need to copy the Invoice Number, Customer Number and, say, the Invoice Date field from the Invoiced Table, plus the Paid Date from the Paid Table.

- I'll then need the macro to delete those lines from both t... Read more

Answer:MS Excel 2007 - VBA Macro to split large file

Is excel the only option? Access would do a lot of this without the having to write a macro every step.
 

2 more replies
Relevance 63.14%

I have a macro from MrExcel.com that parses each row from an Excel spreadsheet into a separate word document and saves each document to My Documents as "File-Row #.doc". For example, row 2 is saved as: File2.doc, row 3 as File3.doc, etc. to the last row with data. The macro starts on row 2 to skip the column headers.I would like to modify the macro to have the file name equal the concatenated values from the first two cells in each row (Columns A and B from row 2 to the last row with data).An example of a simplified version of the spreadsheet: A B C1 Date as Text Record # Record Status2 2012-05-28 APF-2012-1940 Closed3 2012-05-29 ICM-2012-1987 In Process 4 2012-06-02 PAT-2012-2317 Awaiting ClosureOnce the macro is run, the results from the above would be 3 word documents with the file names as below:Row 2 file name is: 2012-05-28 APF-2012-1940.docRow 3 file name is: 2012-05-29 ICM-2012-1987.docRow 4 file name is: 2012-06-02 PAT-2012-2317.docThe macro as it currently exists:Sub ControlWord() Dim appWD As Word.Application Set appWD = CreateObject("Word.Application.8") appWD.Visible = True Sheets("Data").Select FinalRow = Range("A9999").End(xlUp).Row For i = 2 To FinalRow Sheets("Data").Select Range("A" & i & ":N" & i).Copy Sheets("Template").Select Range("A" & i & ":N" & i).PasteSpecial Tr... Read more

Answer:Modify macro to use values in two Excel cells as file name

In the future, if you are going to post data or code in this forum, please click on the blue line at the end of this post and read the instructions on how to post example data and VBA code in this forum.As far as your current issue, this syntax is probably not converting to what you want it to be:Range ("A" & "B" & i)For i = 1, this would seen to VBA as:Range("AB1")I would try something like this within the loop:tmpFilename = Range("A" & i) & " " & Range("B" & i)...
..
...
appWD.ActiveDocument.SaveAs Filename:=tmpFilenameThis will build the Filename from the values in A1 and B1 with a space in between the 2 values.You should also be aware that you might run into problems with the dates in Column A.If VBA picks up the dates as 05/28/2012, then your code will fail because slashes can not be used in a filename.It will depend on how the default dates are set up on your system.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 63.14%

I wrote a Macro in Excel that will open an e-mail and send it using the following code.

ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope
.Introduction = Range("T22")
.Item.To = Range("T15")
.Item.CC = Range("T16")
.Item.Subject = "CST CARRIERS"
.Item.Send
End With

I want to attach a file to this e-mail before sending but don't know the syntax to do this. I know exactly where the file will be stored as well as the file name. Does anyone know the syntax for performing the attachment of the file?


I know you can attach files to these e-mails because if you insert a "Stop" statement just Prior to the ".Item.Send" statement and run the macro you can see and manually send the e-mail. There is an attachment button on the e-mail that I have successfully used to manually attach the file prior to sending the e-mail. I tried recording a macro to get at the code but it didn't copy anything while I performed the manual steps.
 

More replies
Relevance 63.14%

I require help on macro , couldnt find this issue anywhere else in this forum.

I have a list of names(around 500 mech parts) in excel 2003.Eg: "5922 X0E". These names can be converted to filenames if the spaces in between are removed. I have pooled all the files with these filenames in server directory(\\Server\ABC).

I need a macro which automatically hyperlinks the excel list to original files (which is .dwg). If i could assign a software with which the files would open, it would be added advantage.

have been searchin for solution since 1 week Pl give me some magic script.
 

Answer:Solved: Excel Macro for hyperlinking file list.

Please help me!
 

2 more replies
Relevance 63.14%

Hi excel-macro experts, I am writing a data-compiling macro which does, 1)select folder, 2)open xls files in the folder, 3)select all data for each file (only sheet 1 has data), 4)create a new file in the folder (let's say summary file), 5)paste data selected in the process (3) to the summary file created. With a lot of help from many websites, the following macro has been created. However, there is a PROBLEM that is when the data are pasted, all data were pasted into one column (sorce data of each original file has many columns). I need to avoid this. All I want is pasting the source data to Sheet1 of the summary file with the same number of columns (all source data files has the same number of columns) as the sorcce data has. Your help would be greately appreciated!!!

Function RDB_Last(choice As Integer, rng As Range)

' A choice of 1 = last row.
' A choice of 2 = last column.
' A choice of 3 = last cell.
Dim lrw As Long
Dim lcol As Integer
Select Case choice
Case 1:
On Error Resume Next
RDB_Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
Case 2:
On Error Resume Next
RDB_Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
Case 3:
On Error Resume Next
lrw = rng.... Read more

Answer:Help!: Excel Macro; copy multiple files into one file

Can you put your code in the CODE blocks (there is a button in "Go Advanced" mode)? It'll make it much easier to read - that is, if you've indented your code.
 

1 more replies
Relevance 62.73%

I'm using XP pro and Excel-2007. Recently switched to Office 2007 (from Office 2003) and have the following problem when opening any Excel file:

When I try to open an Excel file, MS-Excel starts right away but it takes while (MUCH LONGER as compared with Excel-2003) before the file opens/gets displayed. BTW, I do not have any add-ins enabled.

Can anyone please help me solve this problem?
 

Answer:Solved: MS-Excel 2007 takes too long to open a file

7 more replies
Relevance 62.32%

Hi Gurus,

I have many excel files containing similar data.

1. I want to copy specific columns (columns A to U) from all files and paste the consolidated data into another excel file.

2. I also want to copy specific columns (columns C,D,E,G,H,K,L,T,V,W,X,Y) and paste the consolidated data in a different excel.

Please help.
 

Answer:Excel macro - copy specific columns from different files to one file

Are all of these source Excel files stored in the same directory? Do they need to be opened and consolidated in any particular order? More details please. If possible please try to provide a sample file with any sensitive data removed.

Regards,
Rollin
 

1 more replies
Relevance 62.32%

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

My excel takes minutes, up to 5 or more, to save to a server.
The file size is irrelevant, and windows explorer shows not responding. . .

What am i missing for services that keeps network connections active or
at least able to be opened in less than 5 minutes.

Closing a file without changes caused the same issue.

thanks in advance

sportsguy

Answer:Excel Saving to network drives takes over 5 minutes for any size file

I think i have found the issue, for my work domain, i had network discovery turned off,
and did not have lower level encryption turned on. . .

1 more replies
Relevance 61.5%

Hello

I have a corrupted Microsoft Excel Macro-Enabled Workbook xlsm file. Does anyone know how i can restore or recover it please?
 

Answer:Corrupted Microsoft Excel Macro-Enabled Workbook xlsm file

You can see here http://www.mathworks.com/matlabcent...rosoft-excel-macro-enabled-workbook-xlsm-file
 

1 more replies
Relevance 61.5%

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

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 59.86%
Question: Writing a Macro

Hi guys,

I hope you can help here.

I have a list of 50,000+ items and I need to split this into an action list for 15 people. Sharing the workbook for so many people is inviting trouble, so I thought I would do this by splitting the original workbook into 15 books, asking them to add a comment and then save in a central location, and then I will combine all the date in the 15 books back into the 1 file.

Can you help me with the 2 macro's needed to do this? The first to split the data by the filter and save to new workbooks, the second to combine the workbooks again.

As additional info, the filter will be in the Q (17th) column.

Thanks so much for the help in advance!

Dan
 

Answer:Writing a Macro

7 more replies
Relevance 59.86%
Question: help writing macro

hi everyone

what i am trying to do is basic time sheet that will fill the specific gaps automatically according to different codes for example when entering letter "V" in "other hour code column" and put the hours in "other Hour Column" it will fill "TOTAL VAC. HOURS" in the bottomwith total vacation hours, when entering letter "E" it will fill " TOTAL SICK HOURS", when entering "16" it will fill " TOTAL DOUBLE TIME HOURS", when entering "H" it will fill "TOTAL PERSONAL HOL. HOURS", and so on, knowing that i am using excel 2007 on XP operating system and here is the attachment

thanks in advance
 

Answer:help writing macro

Hi,
I really don't think you need a macro, better use the Sumif function.
 

3 more replies
Relevance 59.86%
Question: Writing a macro

I have a client that want to set the worksheet up so if you do a calculation and the value is 0 in the column that the value that is dragged down will not show. Attached is a sample file.

In the file I want C to be null if there is no value in A or B

I am sure it can be done in VB but not sure how to do it or write it.
 

Answer:Writing a macro

11 more replies
Relevance 59.04%

I am making a form that will act as a Rolodex. In the first box, you will put in a name or location, and then hit a find button. Below that, I then have Headers named Number 1, Number 2, and Number 3 with three text boxes underneath that which will populate with different numbers. I have listed the locations in a different spreadsheet in row A with Number 1 in row B, number 2 in row C, and number 3 in row D. How can I write a macro that when I put a location in my first box it will search the second spreadsheet in row A and then populate the boxes with the phone numbers from that same worksheet from the different rows? Any ideas?

Answer:Writing a Macro for a form.

There are no such things as Row A, Row B, etc.Rows don't have letter designations, columns do.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 59.04%

Hi All,

I want to write a macro in MS word that insert page break in the ms word document where ever there is a sentence called
----PAGE BREAK--- in the word document

I have around 200 page word document where there are at least 100 PAGE BREAK sentences in the document. I want the macro that inserts the page break wherever the PAGE BREAK sentence is seen.
Also, I want to write "THIS IS A DRAFT DOCUMENT" as the HEADER and FOOTER for the entire document.

any help will be greatly appreciated.

-anjali
 

Answer:writing a macro in ms word

Based on what you describe I believe you can use a simple find and replace.

Find Value: ----PAGE BREAK---
Replace Value: ^m

Rollin
 

1 more replies
Relevance 59.04%

hi i don't know if anyone can help me, but i need to write a macro that will identify groups of counts > 970. I have attached a sample of the data I am working on.

I know that "countif" will identify how many counts >970 in a specified range...but within that range I would like to know how many clusters >10minutes of >970 counts.

It maybe clearer if you can have a look at the attached file!!

Could someone please help a desperate PhD student who hasn't a clue when it comes to macros!!
 

Answer:assistance writing a macro

the cluster of 10 minutes isn't too clear - you just have one time, which does not appear to be a number, but an hour:minute. To count the >970, use:
=IF(Count>970,COUNT(D12356),0) (the group is named Count)
or you can use the equivalent
=IF(Count>970,COUNT(Count),0)
 

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

Hallo, my friends, I've a Satellite M40-112 which was bought 4 months before. Now I have problems with my DVD-Ram, the writing of 4xDVD-RW or 4xDVD-R of a volume of 4.3 G normally takes more than 45 minutes, sometimes even one hour. The transfering of data on DVD-RW or DVD-R of the same volume also takes the similar time. Meanwhile, the CPU occupying rate amounts to 100% when writing or reading the DVD-RW/Rs. I've tried the same jobs on my friends Satellite L20 series, the average CPU occupying rate was normally only 10-20%. Could some experts give me your treasurous suggestions or ideas? Thanks in advance.

Answer:Satellite M40-112: DVD writing takes to long

Hi my friend

It is very strange. A friend of mine has the same unit and we have created a few DVD movies and burning procedure is much faster. I don?t know which burning software you use but please check if the option called ?simulation? is activated. If yes disable this.

Bye

1 more replies
Relevance 58.63%

I am creating a form in Microsoft Word 2003. There is an area that requests a physical company information, including; Address:City, State, Zip:Phone:Fax: Primary Contact:Email:I would like to make it so that when a box labeled 'same as physical address' is checked that the information that has been entered will autofill to corresponding fields labeled Billing Information, with the following fields:Address:City, State, Zip:Phone:Fax: Primary Contact:Email:Can anyone help me with this?

Answer:Writing an Auto Fill Macro

Hi,When you fill out the initial company information and address, what identifies where each piece of information is. Is it identified by it's location in a table in Word, or is the information in named fields.What is the 'same as physical address' box - what type of object is it?RegardsRegards

4 more replies
Relevance 58.63%

Hi Everyone,

I need some help with a macro that I have been working on. I have some elements of it working, but I have big dreams about what I would like it to be able to do.

I have 4 different reps that all have a 4 sheet workbook that they fill in to track:
Revenue
Revenue that they are 80% confident of gaining
Lost business
& a Work in progress section.

I have a sales report "master" that I have been able to get the Revenue & the 80% stuff into but I would also like to include the lost business & this is where it starts getting complicated:
I would like to take parts of the WIP & put them into different sections of the sales mater based on "type" of WIP, I have things like sponsorships, 2plus2, BDI, proactive & just normal WIP.

What I have so far is a "macro" sheet that looks for the 4 different reps files & takes the Revenue & 80% stuff & puts it into the master, then saves the master as whatever week commencing we are working on.

I am looking for any possible help with the additional elements that I would like to include.

If anyone could help, it would be greatly appreciated.

Thanks Rebecca
 

Answer:Solved: Help with writing a rather complicated macro

14 more replies
Relevance 58.63%

My goal is to be able to take each input in column B and parse it into preferably one cell with commas ( , ) so that I can copy and paste the value into another program easily. This would save a lot of time as copy/pasting each value one at a time would take forever.

So I decided to make a formula every 50 cells or so (as you'll see in the C column) that parses them out with the commas but I can only go so far before the maximum formula size is reached. I tried to think of a way to do a For...Next loop with the counter at the top of the worksheet like:

For 1 to x = C2

...

Next

But I can't really think of anything. Suggestions?
 

Answer:Writing a macro for Parsing Cells

12 more replies
Relevance 58.63%

specific data from one sheet to anotherHi, I've got to write a Macro to shift specific sales data that has a quantity of 1 from the Input sheet to the output sheet and i need the description and everything else in that row. It looks like this:Part No. DESCRIPTION CHF Quantity PriceXXXXX Blah Blah Blah Blah 99,999 1 =CHF*QuantityYYYYY Blah Blah Blah Blah 99,999 0 =CHF*QuantityI only need the Quantity 1 item and i need all the stuff that goes with it (Part no. Description, CHF, Quantity and Price) I'd really appreciate any help here.Thanks!

Answer:Having trouble writing a macro to move...

You say that you are "having trouble". Does that mean that you have tried coding something?Why not post what you have tried and we'll see if we can help you correct any errors?

2 more replies
Relevance 58.63%

I have had little experience with macros outside Excel, and would like to broaden my knowledge but am finding it difficult to know where to start (other than downloading a copy of Macromaker).

I have an initial task: I want to be able to automate changing the settings of my mouse driver (which, for some reason keeps resetting itself). So I need to be able to call up the driver from the control panel, but I cannot see how to do this in Macromaker.

My questions are:

1. How do I call up a control panel driver in order to modify it in Macromaker?

2. Can someone point me to a resource that I can use to develop a broad understanding of writing Windows macros (including syntax, etc.) - and then hopefully I won't need to bother you with any more daft questions ! ! !

More replies
Relevance 58.22%

I am generally quite satisfied with my Win 7 system.

There are only two quite ennoying things:

a. My Samsung DVD-writer Samsung SH-S223L writes data quite quick, but lead-out takes foreever, while leadout should only last less than 1 minute according to the burning information, takes up to 25 minutes each time I write a DVD.

b. There is also an enormous delay in printing. I own a Kyocera FS-1030D. It was always a very reliable and quick tool, but with Windows 7 it takes up to 1 minute before the first page is printed. Afterwards it is quite quick. This ennoying delay is even effected if only one page is printed.

Would be grateful for any advice

Best

Erwin

Answer:Samsung DVD writing (leadout) takes forever

Welcome to the sevenforums ErwinE!

To help us try to solve your issues, could you please tell us

What burning sw you are using?
Have you tried different manufacturers blanks, with same results?
What type burner is your samsung, SATA or IDE?

3 more replies
Relevance 57.81%

I've been trying to wrap my head around how to do this but haven't had any luck -- I want to search for text in excel in a spreadsheet of 4000 entries...so it'll save a ton of time.

Here's the situation I want solved.

I have an array of 22 columns (AI:BD) that is mostly blank (formulas returning text of "") and occasionally 1-2 (identical) text inputs per row. What I want to do is search this pretty much blank array for the account code (a number) and put it in one column (AH) on the same row. How do I do this?
 

Answer:Solved: Help writing a Macro - search for text

16 more replies
Relevance 57.81%

Hi All

Please can I ask someone familiar with writing Macros for a little help please? I would like to be able to use a keyboard shortcut (say Ctrl+L) to write the word 'Leasehold' in the body of an email.

I do not have the faintest idea of how to write a Macro, I can do one in Word, but that is designed to be very easy anyway.

Thank you in advance
 

Answer:Macro for writing text in Outlook 2007

Without needing a macro, you could use AutoCorrect to do this for you. In Outlook, open a new email and go to the Office Button, and then Editor Options (I think. I have 2010, so I'm bringing this part up from memory) You may have to go to Options and then click on Editor Options. In the Editor Options window go to Proofing and click on AutoCorrect Options. You can add an entry along the lines of Replace l* with Leasehold and it should correct that for you. Let me know if that works for you!
 

5 more replies
Relevance 56.58%

Hello all,

I have been presented with a project that involves Forms that I am not able
to resolve.

Basically I have been asked to create a document template where other team
members can fill out a form that asks a series of questions, then to have
only the completed form results export to a new file.

I am aware that Word can export the form results to a text file, but when I
do this all of the questions that were on the original form are exported to
the new file.

What we are looking to achieve in the end:
If the end user is faced with 20 questions, but only 12 of the questions
pertain to the end users project, only the 12 completed form fields will
export to a new file.

I know that this should be possible with using a macro button to export the data, but that is where I need the help.

I tried searching for this exact situation, but I did not find any matches, hence the new thread.

Thank you in advance,

Bill
 

Answer:Macro help needed to export Word form results to a new file... Word/Excel

11 more replies
Relevance 56.58%

Good Morning,

I am a small business owner (in-home tutoring), and I use Quickbooks Pro 07 on Vista and XP. I am logging in locally and remotely to email an Excel file of either customers or vendors, but I get an error of "file already in use." I can email a .pdf, but not email.

Any suggestions?

Thank you in advance!

Boe
 

More replies
Relevance 56.58%

I have Windows XP. I emailed an excel spreadsheet from my excel 2003 laptop to my excel 2007. I opened the file and continued working on it. This being the first time i ever used excel 2007. I repeadedly pressed the save button, so I thught I had saved it all but it did not save to my c drive.

I cannot locate this file. I have carried out searches and looked through the c drive temporary files.

When I open the document via email it does say it is in the process of being modified. However cannot find where the changes have been stored. My email account is google. Are there tempory files within google? Do yo uhave any ideas where it might have gone?

Please help!
 

More replies
Relevance 56.58%

Hye guys...i have excel file to control expiry date for technical documents. Every month, i need to send reminder email to the user on the expiry date, so they can review back the applicability of the documents to the user.

My question is..if there any way i can sent the email to the user automatically from excel file, one day before the expiry date.
Currently i m using Zimbra email for internal communication.

Appreciate your idea.

Thanks
 

Answer:Email alert from excel file

Hi, welcome to the forum.
I personally do not have any experience with Zimbra but have you checked the othet posts with similar questions?
There are quite a few and also some I helped with.
If you do a search in this forum you will see the answers and the code used which will maybe help you.
We can always carry on from there.
 

1 more replies
Relevance 55.76%

When someone sends me an email excel attachment and I open it, it does not automatically load to Excel, it says it cannot find the file (excel does open). However, I can save it and go to excel and find it that way. Also, if I go to my recent documents and click on an excel file, it says it cannot find the file either. However, once again if I go to excel and open the file there, it finds it. This has just started happening, but effects all files.
I have rebooted and run disk check. Anyone else have any ideas. Thank you in advance.
 

Answer:Outlook email problem with excel file

12 more replies
Relevance 55.76%

My daughter has just installed MS Office on her Vista laptop. She tried to open an email attachment which is an Excel file with a xls suffix, but gets a report that Adobe Reader cannot open this file. She uses Yahoo email, which I can't see any way of saving the file as you can in Windows Mail. Can anyone help please?

Answer:MS Office Excel, opening email XLS file

You need to download the attachment to open it with MS Office Excel.Download it with Outlook click here

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

How safe to open these .exe files??

I emailed some Excel 2000 .xls files from my work computer to my home computer. I'm wary about opening them because 6 out of 7 of them have arrived at home as .exe files.

Many, but not all, of these files have lots of vlookups or possible filtering. Would vlookups or filtering cause the file to change to an .exe file? ie Is it probable they are safe to open?

A quick response would be much appreciated, since I'm hoping to do lots of work at home tonight!

 

Answer:Excel .xls email attachment received as .exe file.Safe?

I don't like the sounds of it. Are you using Winzip--possibly turning them into self-extracting zip files?

Try going to www.freedrive.com or www.driveway.com and sign up for some free web space. Upload your files, then download them when you get home. At least until you find out why it's doing that!
 

1 more replies
Relevance 55.35%

Hi All,

I need help in sending emails from an excel file. I have attached the format in the sample file.From the file attached if the value in the column E is "yes" then an email to be sent to the email address in the corresponding column F.

Its urgent and I trust this is not a difficult task for most of the super brains here.This is my first post please help
 

Answer:Help trigger email from excel, file attached (urgent)

13 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