Computer Support Forum

Solved: Excel Macro to Outlook Calendar

Question: Solved: Excel Macro to Outlook Calendar

Hey guys, I am quite new to creating macros. I do have some primitive experience with code. My goal is to take 3 or 4 colums of information and put them into Outlook Calendar.

A- Discription B- Start -C Finish

So, in the Calendar in outlook it would show a discription and connect the dates in B and C to the days in the Calendar. I don't know if it can be done. At my current state I can't figure out how to get even one to move over. Any suggestions on where to start??

I am running Outlook and Excel 2003 all updated. On Windows XP Pro Service Pack 2.

Relevance 100%
Preferred Solution: Solved: Excel Macro to Outlook Calendar

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: Solved: Excel Macro to Outlook Calendar

16 more replies
Relevance 101.68%

Hi,

We have a spreadsheet that keeps track of when our computer warranties expire. Would like to run a macro using the warranty expired date to create an appointment in Outlook. That part I understand. The problem is that when I go to run the macro again, it creates a duplicate appointment if the appointment already existed. Very frustrating!

This is the code below, any help would be appreciated!!!! Thanks
Sub AddToOutlook()
Dim o As Outlook.Application
Dim ai As Outlook.AppointmentItem
Dim r&, sSubject$, sBody$, dStartTime As Date, dDuration#

For r = 4 To 10

sSubject = Sheet1.Cells(r, 1).Value
sBody = Sheet1.Cells(r, 4).Value
dStartTime = Sheet1.Cells(r, 3).Value
dDuration = 30
Set o = GetObject(, "Outlook.Application")
Set ai = o.CreateItem(olAppointmentItem)
ai.Body = "Service Tag= " & sBody
ai.Subject = "Warranty Expires for Server " & sSubject
ai.Start = dStartTime
ai.Duration = dDuration
ai.Close olSave

Next r
End Sub
 

Answer:Solved: Excel Macro to Update Outlook Calendar Creates Duplicates

6 more replies
Relevance 100.45%

Hi,

I need help with a macro. So far the macro is operating as it should , creating MS Outlook 2010 calendar appointments using data from an excel data spreadsheet (data base). The issue I’m having is that every time the macro is run for a new entry, duplicate calendar appointments are created for the already existing data in the data base. I would like to have the macro do a search to ensure an appointment does not exist before creating one. I have read a few threads with similar problems but have had major issues incorporating the solutions to resolve the issue I’m having.

I have attached the macro in txt. format for reference.

Thanks for your help.

kkfonty
 

Answer:Solved: Excel macro updating Outlook calendar is creating duplicate appointments.

Hi
You can use this function to check if an appointment exists based on the Start Day/Time

Code:

Public Function CheckAppointment(ByVal argCheckDate As Date) As Boolean

Dim oApp As Outlook.Application
Dim oNameSpace As Outlook.Namespace
Dim oApptItem As Outlook.AppointmentItem
Dim oFolder As Outlook.MAPIFolder
Dim oMeetingoApptItem As Outlook.MeetingItem
Dim oObject As Object

On Error Resume Next
' check if Outlook is running
Set oApp = GetObject("Outlook.Application")
If Err <> 0 Then
'if not running, start it
Set oApp = CreateObject("Outlook.Application")
End If

Set oNameSpace = oApp.GetNamespace("MAPI")
Set oFolder = oNameSpace.GetDefaultFolder(olFolderCalendar)

CheckAppointment = False
For Each oObject In oFolder.Items
If oObject.Class = olAppointment Then
Set oApptItem = oObject
If oApptItem.Start = argCheckDate Then
CheckAppointment = True
End If
End If
Next oObject

Set oApp = Nothing
Set oNameSpace = Nothing
Set oApptItem = Nothing
Set oFolder = Nothing
Set oObject = Nothing

End Function

For this to work you need to have Tools/References/Microsoft Outlook xx Object Library enabled.
Then alter your macro like this

Code:

Private Sub cmdOutApt_Click()
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")
' Start at row 2
r = 2
Do Until Trim(Cells(r, 1).Value) = "&q... Read more

2 more replies
Relevance 91.84%

Good Morning,

I have very limited knowledge when it comes to Macros and VBA.

What I need is one of two possible scenarios.

1. Information from a Training Assignment PivotTable exported to a shared calendar on Outlook.
2. Information from a Training Calendar bulit as a Macro in Excel exported to a shared calendar on Outlook and the ability to update either calendar if new information becomes available.

Don't know which is best using code via VBA or using the Export/Import functions.

Any help is greatly appreciated
 

More replies
Relevance 91.84%

Hello Everyone
I'm a pro atlete (quite iliterate in Biz Apps) that based on previous post (from Zack Baresse and computerman29642) has unsuccessfully tried to set up a code to merge my next year training calendar (xls format) into outlook. I herewith included the code I'm using and the test xls file I'd love to merge. Any help would be really appreciated
Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
'Create the outlook item for the table entries:
'Rows:
' Row 1 = Startdate
' Row 2 = Finishdate
' Row 3 = Subject
' Row 4 = Location
' Row 5 = Body
For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt

.StartDate = arrAppt(i, 1) + arrAppt(i, 2)
.Finishdate = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = arrAppt(i, 6)
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.AllDayEvent = True
.Save
End With
Next i

Set olApt = Nothing
Set olApp = Nothing
End Sub... Read more

More replies
Relevance 89.79%

Good Afternoon,

I'm quite stuck trying to fix my code. I've scoured the internet looking for similar questions, but to no avail. I am trying to have excel create appointments in the outlook calendar and be able to run the macro multiple times without have duplicate appointments.

I could run my macro and create an appointment, which worked fine, but there was no routine to check if the appointment already existed. So I would have multiple appointment alerts for the same appointment if I ran the macro more than once. (If I were to add something and run the macro again to keep everything up to date).

So I tried to add a loop checking first the date of the appointment then the title (subject). However, now, I am getting the "Object doesn't support this property or method" error when I run the following code.

If anyone could help me with what the error refers to specifically in my code or any code I need to add or take out to make my loop work I would really appreciate it. Thank you very much in advance.
Code:

Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

Dim olCalendarFolder As Outlook.MAPIFolder 'use MAPI to loop through folder
Dim olMessage As Object 'items in calendar/inbox are messages
Dim olInboxMessages As Object
Dim olAppSession As Outlook.Namespace
Dim strSubject As String
Dim myDate As Date

Applic... Read more

More replies
Relevance 88.97%

Hi all... I just joined this forum as past threads have been of great assistance... But now I have a immediate issue... I have wrote some code to pull info from a Excel report and create an Outlook appointment base on various commitment dates... This report is sent out daily therefore the macro is used daily... An issue that has risen is that everytime the macro is ran it creates duplicate appoinments which quickly bogg down the server... I saw an example on a closed thread and could not quite figure it out... Can anyone assist??? I am running 2003 versions of both Excel and Outlook and my code is as follow and thanks...

Sub ExceltoOutlookCommitDateSmithRenewalReport()
'
' ExceltoOutlookCommitDate Macro
' Load Excel Commit Dates into Outlook
'
' Keyboard Shortcut: Ctrl+o
'
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")

If MsgBox(prompt:="OK if YES / CANCEL if NO", _
Buttons:=vbOKCancel + vbQuestion, Title:="HAS PAA BEEN FILTERED?") = vbOK Then
MsgBox "MACRO WILL NOW UPDATE OUTLOOK CALENDAR WITH PROCLAIM COMMIT DATES."

Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste

' Start at row 5
r = 5
Do Until Trim(Cells(r, 1).Value) = ""
If (Cells(r, 20).Value) = "" Then
' Create the AppointmentItem
Set myApt = myOutlook.createitem(1)
' Set the appointment properties
myApt.Subject = Cells(r, 2).Value
If (Cells(r, 5).Va... Read more

Answer:2003 Excel to Outlook Calendar appoinment VB macro creates duplicates appointments

Hi jayJay,

I'm not going to look through the whole code but what I think happens is that sinec you run the macro every time it will just add an appoitment, I think an options should be added, something like an extra column namen DONE and when the macro is run the cell in the column DONE should be given a value True() or a 1 to indicate it as done, so that when you run the macro again the column DONE cells where the valkue is not True() or is not 1 (dependign on waht you choose) is carried out.
This way you will avoid duplication.
Just an idea, happy coding.
 

1 more replies
Relevance 77.49%

For some reason there is a problem with this programme I have built (with help from Computerman) now I have transferred it to another excel file.

When I try to set a Callback Reminder (my terminology), once I have entered the details an error comes up for some reason. The programme has been tried and tested by itself and worked brilliantly but for some reason it doesn't now.

Please use password lfc1979 on the login screen
 

Answer:Solved: Excel outlook calendar reminder

9 more replies
Relevance 76.26%

I have several deadlines on an Excel application that I would like to be linked to the company's Lotus Notes calendar with email reminders of the deadlines.

Am at a loss as to which and how to write the macro code(s) in.

Could someone provide some light on this please? Need it asap.

Thanks!
 

More replies
Relevance 76.26%

Hi, I am looking for a macro to import contacts from excel. We are a team of 25 who all use the same contact list (which contains addresses, organisation name, tel numbers, emails, contact names etc for approx 600 organisations). We regularly have to bcc all these contacts in emails. We previously all held the distribution lists (sorted alphabetically by email) and had to let the rest of the team know when details changed. The problem with this was that the distribution lists do not hold all the information needed (e.g. addresses, organisation names) and contact information was not always updated as it should have been. I would like to create the macro for all the team so that they can periodicaly update their contacts. I trust this makes sense
Thanks
Moved to Business Applications.
 

More replies
Relevance 75.44%

Ok, just a bit of advice needed on this one for the time being...
I need a macro that will:

- Save all (Excel) attachments from the currently open email message (or a message selected in the Inbox) to a directory on the PC.

- Merge all the saved Excel files into a single file - they're all formated exactly the same - data in about 20 columns, no column headers or anything, just standardised data.

- Save the merged file with a standard name (i.e. every time this is ran, it needs to have the exact same filename).

- Delete all the files in the directory apart from the merged file.

I know there's probably a fair bit of code online for doing the individual parts of this. Can I do both from within Outlook? It doesn't particularly matter if I need to do this in 2 steps - an Outlook macro to save the attachements, and an Excel macro to do the file merge etc.

Cheers.
 

Answer:Solved: MS Outlook / Excel macro - extract attachments and merge

16 more replies
Relevance 74.62%

I have an excel macro that we use to create Outlook 2003 calendar invites. Basically, it spins thru cells in the spreadsheet, picking up names and making them optional or required. Now we are trying to move this functionality to a common mailbox, so I have one outlook account and I also have access to another mailbox. I have been unable to determine how to tell outlook to add the invitation to that mailbox.
Here is the code I use....
-----------------------------------------
Dim objOutlook As Outlook.Application
Dim objAppt As Outlook.AppointmentItem
Dim objRecurPattern As Outlook.RecurrencePattern
Dim strHold As String

Set objOutlook = CreateObject("Outlook.Application")

On Error GoTo Add_Err

Set objAppt = objOutlook.CreateItem(olAppointmentItem)
objAppt.MeetingStatus = Outlook.OlMeetingStatus.olMeeting
strHold = ""
With objAppt
For Each cell In ActiveWorkbook.Sheets("Team Review Blank").Range(gblAttRange)
If UCase(Left(cell.Offset(0, 1), 1)) = "R" Then
If strHold = cell.Value Then
Else
strHold = cell.Value
Set oRecipt = .Recipients.Add(cell.Value)
oRecipt.Type = olTo
End If
ElseIf UCase(Left(cell.Offset(0, 1), 1)) = "O" Then
If strHold = cell.Value Then
Else
strHold = cell.Value
Set oRecipt = .Recipients.Add(cell.Value)
oRecipt.Type = olOptional
End If
ElseIf cell.Value > "" Then
If strHold = cell.Value Then
Else
strHold = cell.Value
Set oRecipt = .Recipients.Add(cell.Value)
oRecipt.Type = olTo
End If
End I... Read more

Answer:Excel Macro to build Calendar invites for second mailbox

10 more replies
Relevance 67.24%

I need a calendar program for my computer since I recently upgraded my computer with student office 2007, which doesn't have outlook like the 2003 edition. Can I have any suggestions? I don't want a web based program that needs that internet to function.

thanks
 

Answer:Solved: Need a calendar program (not outlook or gmail calendar)

Thunderbird with Lightning:
http://www.mozilla.org/projects/calendar/lightning/
 

3 more replies
Relevance 66.83%

I need a way to create a calendar that is able to perform recurring events that happen like.. The first two Mondays of the month, Once every quarter, Daily, etc.

The trick is that if the event falls on a holiday, it needs to be moved to the same day of the following week. However, this would happen only for some events.

Should I attempt to create this in Excel or Outlook?
 

Answer:Calendar - Should it be done in Excel or Outlook

16 more replies
Relevance 66.42%

Hey guys, I had some great assistance from computerman29642 earlier on this macro. It works perfectly on my own calendar; however, I need to target a specific shared calendar. Hopefully it could be user imput by a msg box. I am struggling to find information on how to do this.

The first thread was this
http://forums.techguy.org/business-applications/835748-solved-excel-macro-outlook-calendar.html

So, as for now the example works. But before it writes to the calendar I want a msg box pops up asking for the desired calendar(Sometimes more than one word), and then target that calendar and write the data. A simple little box that confirms or denies progress (I am pretty sure I can figure that one out) Any suggestions or directions where I can learn more fire away please. Ill attach a copy of my test again.
 

Answer:Excel to Outlook Macro

16 more replies
Relevance 66.01%

Dear all,
I've an Excel WorkSheet similar belowe:
________________________
First Row | 1 | 2 | 3 ......
...(day of the month)
________________________
Second Row |Mr. Smith| Mr. Simpson|Miss.......

I want import my appointment from this worksheet
in Outloock calendar.
I'm not interested to import appointment time ....
Only Appointment.
Any Solution?

Thank's.
 

Answer:Importing to Outlook Calendar From Excel

Can you post your sample Excel file? Just change any sensitive info to dummy data. What you want to accomplish can be done using VBA code.

Regards,
Rollin
 

2 more replies
Relevance 66.01%

Hi,

I've been trying to put together a macro in excel that will loop through my Outlook Calendar to see if an appointment already exists, and if it doesn't exist, to create it.

The Macro works to create a new appointment. The problem arises when I try to loop through my calendar. When I try to run the macro via Excel>Tools>Macro>Run Maco I receive an error message, "Out of Memory"; However while Debugging in VB, I receive the error message, "Run-Time Error" followed by a series of numbers that changes every time I try to debug it, then "Automation Error"

If anyone has any insight as to what I should do, or how to fix my problem, I'd really appreciate it. Thank you very much.
Code:

Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim olAptS1 As AppointmentItem
Dim olAptS2 As AppointmentItem
Dim olAptS3 As AppointmentItem
Dim blnCreated As Boolean
Dim olCalendarFolder As Outlook.MAPIFolder 'use MAPI to loop through folder
Dim olMessage As Object
Dim olInboxMessages As Object
Dim olNS As Outlook.Namespace
Dim strSubject As String

Application.ScreenUpdating = False

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "F").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp I... Read more

Answer:Excel VB to Loop Outlook Calendar

Well, I would think - and highly recommend - you NOT loop through your entire calendar. I would say try to think of some other criteria you can use. Can you explain the project you're trying to do here? What would be the criteria for finding an appointment? Can't you just look on the day you're referring to? Or how about a specific date range? Looping like that will probably make your machine smoke.

Edit: And btw, you're not looping, you're using multiple/nested If/Then statements. Much better than a loop if you have a specific number of conditions to check for, but loops should be avoided when they can be.
 

2 more replies
Relevance 66.01%

Yes I am another user whom wishes they had the time to learn basic programming but life keeps getting in the way.
I would like to export info from an excel roster workbook and automatically create appointments in outlook calendar.
From reading previous posts, this looks achievable through the use of a macro.
THE CHALLENGE:
Create appointments in outlook from the attached roster- Row 29 (M.Willis) for the appropriate dates and shift start and finish times.
A description in the subject line such as 'F shift' followed by '06:00 - 17:00' would be great.
Pls feel free to flame me if this is a ridiculous ask. But after the abuse perhaps you could point me in the right direction on what it is I need to learn.
 

Answer:Anyone up for a excel to outlook calendar challenge?

"Pls feel free to flame me"

OK. You're an idiot.

Just kidding. "From reading previous posts, this looks achievable through the use of a macro". A link to those would've been handy.

I know zero about Outlook. Never use it. But if I run this code it does create a test appt -- provided I set a reference to the Outlook Object Library in Excel's VBE beforehand. I believe that way is known as "Early Binding", whereas having the code itself set the reference would be "Late Binding".

So it's possibly just a matter of incorporating your appt(s) details (where on the worksheet to get e.g. "Start" and "End" from).

Welcome to the board.
 

3 more replies
Relevance 66.01%

Hi Is it possible to import an excel document such as an annual shift roster into the calendar on outlook so that the shared calendar on outlook will show others the shift being worked by the individual on a particular week?Thanks

Answer:excel document into outlook calendar ?

Just adding to my postings as if someone has the answer it could be useful for me.

9 more replies
Relevance 66.01%

I have a long list of appointments that I need to import into Outlook Calendar from Excel. I have exported my regular calendar to find out what fields I need. I have formatted all columns to be the same as what Outlook exports. When I import only the first two items import. Any ideas? Thanks for any help you can give?
 

Answer:Importing to Outlook Calendar From Excel

9 more replies
Relevance 66.01%

Hi, I am trying to make an excel file link to an Outlook Calendar. Basically I have three columns: Subject, Date, Body, Contact. Ideally I would like to make an appointment for every row. The subject would be the subject of the appointment, the date is when the reminder would go off, the body will read the same thing every time "This proposal was submitted 60 days ago," and the contact is the person who should receive the reminder. Just like how you invite people to an event or meeting in Outlook. This list will be actively added to, so I have included a button to run the macro to update the calendar. Unfortunately I have only have gotten Outlook to pop up and then it runs into errors when trying to create the appointment. Any help would be greatly appreciated. I am running in Excel 2010, and have a bit of experience in VBA but have never done something quite like this. Thanks!
 

More replies
Relevance 66.01%

Hi all... I just joined this forum as past threads have been of great assistance... But know I have a immediate issue... I have wrote some code to pull from a Excel report and create Outlook appointment base on various commitment dates... This report is sent out daily therefore the macro is used daily... An issue that has risen is that everytime the macro is ran it creates duplicate appoinments which quickly bogg down the server... I saw an example on a closed thread and could quite figure it out... Can anyone assist??? also I need the appointments to be set for 8:00 AM everytime they are added... My code is as follow and thanks...

Sub ExceltoOutlookCommitDateSmithRenewalReport()
'
' ExceltoOutlookCommitDate Macro
' Load Excel Commit Dates into Outlook
'
' Keyboard Shortcut: Ctrl+o
'
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")

If MsgBox(prompt:="OK if YES / CANCEL if NO", _
Buttons:=vbOKCancel + vbQuestion, Title:="HAS PAA BEEN FILTERED?") = vbOK Then
MsgBox "MACRO WILL NOW UPDATE OUTLOOK CALENDAR WITH PROCLAIM COMMIT DATES."

Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste

' Start at row 5
r = 5
Do Until Trim(Cells(r, 1).Value) = ""
If (Cells(r, 20).Value) = "" Then
' Create the AppointmentItem
Set myApt = myOutlook.createitem(1)
' Set the appointment properties
myApt.Subject = Cells(r, 2).Value
If (Cel... Read more

Answer:Excel to Outlook Calendar Duplicates

Also I am running the 2003 version of Microsoft Excel and Outlook...
 

1 more replies
Relevance 66.01%

Case name__________ Type of report__________ date received ______date due (85 days from receipt)
Hogan, Hulk___________CAN____________________ 5/7/12___________7/31/12
Jolie, Angelina ________Prevention_______________ 5/15/12 _________8/8/12
Stark, Tony__________ CAN ____________________6/19/12__________9/12/12
My excel doc already calculates the due date. What I desire is for the case name to populate to my outlook calendar to the date that it is due. Is this possible, and if so, when posting a solution, treat me as if I were 3 years old, I am not computer saavy, but am a social worker looking to cut corners anywhere I can.

I want to be able to enter the info on excel, open my calendar and immediately find Hulk Hogan's name in 7/31/12
 

More replies
Relevance 66.01%

I need to set up a spreadsheet for loaning out products, but what I want is for Excel to email me or alternatively put an entry in my Outlook Calendar when the product is due to be returned. I have attached a simple example.

What I would like is to have either a Calendar entry that says XYZ is due back from Joe Bloggs today or an email sent on the due date to remind me.

Any ideas would be welcomed
 

Answer:Excel to outlook calendar or email

Panmanz said:

I need to set up a spreadsheet for loaning out products, but what I want is for Excel to email me or alternatively put an entry in my Outlook Calendar when the product is due to be returned. I have attached a simple example.

What I would like is to have either a Calendar entry that says XYZ is due back from Joe Bloggs today or an email sent on the due date to remind me.

Any ideas would be welcomedClick to expand...

This may be an alternative...I don't have Outlook, so I couldn't test this. You can assign due dates to tasks, so this may work for you- hope this helps.

This is from Microsoft Help-

Create an Outlook task for a workbook

These steps allow you to create a task for yourself. If you want to e-mail a task to someone else, use Microsoft Outlook rather than Microsoft Excel. For more information, see Outlook Help.

Open the workbook for which you want to add the task.

On the Reviewing toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, click Customize on the Tools menu, and then click the Toolbars tab.), click Create Microsoft Outlook Task .

On the Task tab, enter the subject, dates, status, priority, and reminder for the task.

For more information about task options, see Outlook Help.

Click Save and Close .

Note Once you create the task, you can open it in Outlook. For more information, see Outlook Help.
 

3 more replies
Relevance 66.01%

Hi,

I am wanting to add a button on an excel worksheet which will then produce a pop up box which you can enter details into which will then input into outlook calendar. Anybody know how to do this?

Thanks in advance
 

Answer:Inputting from excel to outlook calendar

Please do not start more than one thread for the same issue.

Closing duplicate.
 

1 more replies
Relevance 66.01%

How do I have dates from an excel spreadsheet posted or flagged onto my outlook calendar?

More replies
Relevance 66.01%

Hi,

I am wanting to add a button on an excel worksheet which will then produce a pop up box which you can enter details into which will then input into outlook calendar. Anybody know how to do this?

Thanks in advance
 

More replies
Relevance 66.01%

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

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

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

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

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

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

The following is the start of the macro code:

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

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

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

7 more replies
Relevance 66.01%

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

Hi,

I am an extremely basic user who has managed to get a basic macro together through multiple web searches and am now stuck...

I have dates entered into the following columns in excel: 14,15,16,26,27,28

I have managed to make the macro create appointments for 14,15,16 but not the rest. Also, every time i run the macro it creates a new appointment regardless if one already exists. I saw on this forum about adding a word to another column which the macro searches for and if found doesnt create a new appointment - or something to that extent. Here is what i have:

Sub POATEST()
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")

' Start at row 7
r = 7

Do Until Trim(Cells(r, 1).Value) = ""
For Each cell In Range(Cells(r, 14), Cells(r, 16))
If cell.Value <> "" Or 0 Then

' Create the AppointmentItem
Set myApt = myOutlook.createitem(1)

' Set the appointment properties
myApt.Subject = Cells(r, 1).Value & " " & "Update Due"
myApt.Start = cell.Value
myApt.categories = "Yellow Category"
myApt.ReminderSet = True
myApt.Body = "blah blah blah"
myApt.Save
End If
Next cell
r = r + 1
Loop
End Sub

Please can someone assist by:
1. making it apply for all the columns originally mentioned
2. making it search if an appointment for that item (ie that row) exists - if not, create appt, if so, do not create appt and insert "Yes" in column 29

Thank... Read more

Answer:Excel Macro - Add Appointment To Outlook

HI,

Check this code you may be able to modify it.
Have not used it in a long time but....

Code:

Sub AddAppointmentsToCalendar()
Dim OLF As Outlook.MAPIFolder, objItem As Outlook.AppointmentItem
Dim i As Long, lngItemCount As Long, r As Long
On Error Resume Next
Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderCalendar)
On Error GoTo 0
If OLF Is Nothing Then Exit Sub

Application.StatusBar = "Adding appointments to Outlook..."
With wsSheet1
.Activate
r = Range("NewAppointments").Row + 1
Do While Len(Range("A" & r).Formula) > 0
On Error Resume Next
Set objItem = OLF.Items.Add(olAppointmentItem)
On Error GoTo 0
If Not objItem Is Nothing Then
With objItem
.Start = Range("A" & r).Value
.End = Range("B" & r).Value
.Subject = Range("C" & r).Value
.Body = Range("D" & r).Value
.Categories = Range("E" & r).Value
.ReminderSet = False
.Save
End With
Set objItem = Nothing
End If
r = r + 1
Loop
End With
Application.StatusBar = False
Sheets("Appointments").Select
Range(&... Read more

1 more replies
Relevance 65.19%

I am a delegate of someones calendar and would like to export the contents into Excel. when I got to Import / Export im only seeing my local calendar in the list but not the shared. Can someone tell me how to export? thanks
 

Answer:Exporting a Shared Calendar into Excel using Outlook 2K3

ttt
 

1 more replies
Relevance 65.19%

Hi,

Thought I would say hi first as I'm now here.

I have been looking at thread 541487 and I was wondering if it could be expanded to cover a couple of items:

1. Check for duplicate entries already in Outlook, or maybe to set a flag against each row once created in Outlook (The spreadsheet I'm using will be continuously added to)

2. If a column has a an AD username to check the username against the calendar being created in and to only create items that match

I have attached an example.

Thanks in advance for your help.
 

More replies
Relevance 65.19%

Hello.

I am currently optimizing the workflow for the ventilation company i work for. (I am a young student worker). And one of my ideas is to synchronize dates of service that has been done to the customers ventilation systems (those dates are listed in Excel with all the other service data) with the offices Outlook Calendar.
Our clients sign up for yearly service for their ventilation service, however the service engineers are so busy that even though we have a service date (1 year from last service obviously) they might do the job 1 month later than expected. Therefor the dates in the calendar will not be up to date with the last service done.
The service engineers do a service report in Excel after finishing a job. Here they enter the date for when the work has been done. I want this cell where the date has been entered to synchronize with a Outlook Calendar appointment of this particular vent. service. Remember that there are many service reports in different Excel documents.
Is this possible? And how? If not, what if i made a new Excel document which automaticly updates the dates of all the other documents, and then made this document synchronize either automaticly or manually?
I have of course searched the web, and i found this thread which is very close to what i want: http://forums.techguy.org/business-applications/541487-merge-excel-dates-into-outlook.html

The problem is; in that thread they talk about some code like everyone would know. Sadly I have no id... Read more

Answer:Synchronize Excel dates with Outlook Calendar

12 more replies
Relevance 65.19%

Any ideas of how to copy checkbox data from Outlook 10 Calendar to Excel?

I created a large number of custom controls in Outlook Calendar. These include a number of checkboxes. I created a custom view with all of the fields (it's in list format so it looks like a spreadsheet). I used CTRL-A and CTRL-C to select all and copy. When I pasted into Excel none of the checkbox controls transferred. Everything else is there. I tried the same thing with Access with the same results.
 

Answer:Copying data from Outlook Calendar to Excel

Controls are not data, you cannot copy them elsewhere.
 

1 more replies
Relevance 65.19%

Hey. First time posting and I couldn't find a definitive answer from searching through the forums...

I've seen many ways to export dates from an excel spreadsheet as outlook calendar appointments both using VBA and just the manual export feature. However, before I search myself into oblivion, is it possible for it to be true 'sync'? What I mean is the VBA code can check if an appointment has already been created and edit the date/time/etc. if it has changed?

All the examples I've seem like they would just create a duplicate appointment in this example.

Thanks for you help!
 

Answer:Excel-Outlook Calendar True Sync

6 more replies
Relevance 65.19%

I need to set up an Excel spreadsheet that includes dates that will automatically link to an Outlook calendar. Is there any way to do this?
 

More replies
Relevance 65.19%

I have an Excel spreadsheet with dates of tasks to accomplish that I would like to merge into Outlook. Column A has the due date and column B has the task that needs to be completed.

Thanks for any help.
 

Answer:Merge Excel dates into Outlook Calendar

11 more replies
Relevance 65.19%

Hello There,

I have followed this thread to create a script in order to sent the dates from a excel sheet to Outlook.

Since on the excel file that I am saving the data will have many changes, I don't want each time all the instances to be copied to outlook. Because this ends up with many repeats of the same tasks.
For example I want the script to check if the data with this Subject is on my calendar and then to compare the date in order to change it or not.

And as secondary I want to join 2 cells as the subject.

This is the code that I use:
Code:
Sub ExportTasksToOutlook()
Dim olApp As Outlook.Application
Dim blnCreated As Boolean
Dim arrTasks() As Variant, i As Long
arrTasks = Range("A2", Cells(Rows.Count, "B").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
For i = LBound(arrTasks) To UBound(arrTasks)
With olApp.CreateItem(olTaskItem)
.DueDate = arrTasks(i, 1)
.Subject = arrTasks(i, 2)
.Body = "Created By Michalis Papadopoulos Excel Tool"
.ReminderSet = True
.ReminderTime = arrTasks(i, 1) - 3
.Save
' .Close
End With
Next i
If blnCreated = True Then
... Read more

More replies
Relevance 64.78%

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

Hi

This is a tuffy........

I want a macro so that when i type a message into a cell it will put it into outlook as a reminder, or even a simple macro button that opens outlook in the scheduler.

Any ideas?

Thanks
 

Answer:Excel macro links to Outlook Reminder

Try this link.
 

1 more replies
Relevance 64.78%

Moved from Software Development
Hi, We are a team of 25 who all use the same contact list (which contains addresses, organisation name, tel numbers, emails, contact names etc for approx 600 organisations). We regularly have to bcc all these contacts in to emails. We previously all held the distribution lists (sorted alphabetically by email) and had to let the rest of the team know when details changed so they could be updated by all. The problem with this was that the distribution lists do not hold all the information needed (e.g. addresses, organisation names) and contact information was not always updated as it should have been. I have updated an excel spreadsheet containing all the information and I would like to create a macro for all the team to periodicaly import this data in to Outlook Contacts and possibly create distribution lists from these. I trust this makes sense I am using Outlook 2003.
Thanks
 

More replies
Relevance 64.78%

Hi all,

I'm looking to create an Excel document and I need to put cells with dates in that are linked to outlook calendar reminders.

I.e I need a macro that when I click the link it sets the reminder on outlook for the date allocated in the cell.

could anyone please help me with this as i have never created a macro and would greatly appreciate any help you could give me.

Thanks

More replies
Relevance 64.78%

I need a macro that will 1) create a new outlook folder, then 2) create new Outlook distribution lists from data in an Excel sheet.

The Excel sheet has columns:
A - ID
B - DEPARTMENT_DESC
C - STUDENT_LEVEL_DESC
D - MAJOR_DESC
E - PROGRAM_DESC
F - EMAIL_ADDRESS
G - LAST_NAME
H - FIRST_NAME

The first entry begins in row 2 with row 1 being the column title.

I need a new distribution list to be created for each department (column B) and each student level (column C). (Column B will only ever be one of 6 values, column C will only ever be one of 2 values. Therefore, the macro should create 8 distribution lists.)

I have looked at threads similar to this question but I can't seem to get anything working.
 

Answer:Macro from Excel to New Outlook Distribution List

I saw something you might could work with here: http://www.helenfeddema.com/CodeSamples.htm
scroll down for sample 61. I haven't used it but marked it one day. I'm assuming your wanting to make local distb list? Not in AD.
 

1 more replies
Relevance 64.78%

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

Hello,
I am learner, who wants to automatize his repetition through combination of Excel and Outlook. I've read some posts on this forum and I see, that it is possible. However, I am totally lame in both Excel and Outlook and script writing. Yup, I am total tech noob.

I want it to look like this (behold, paint-edited picture):

I learn something, then I write down what I learned in excel spreadsheet ('WHAT?' in the spreadsheet) and give it a CODE, which would be shown in Outlook calendar. I write down the in which I learned and my outlook calendar syncs with every one of six repetitions.

I found some VBA (?) codes on this message board, but I have no idea how to implement them to my excel (and even less idea how to change the code). I would really appreciate if you would give me some hints or link (except for google.com ) where this process is described.

Here is link to my excel spreadsheet, if you want.

Thanks in advance.
Essay

http://speedy.sh/QtzQ5/Repetitions.xlsx
 

Answer:Syncing Outlook calendar with multiple dates in Excel

Trying again...
Can anyone help me to incorporate script like this, to sync my excel spreadsheet with my outlook calendar?
Code:
Sub ExportAppointmentsToOutlook()

Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0

'Create the outlook item for the table entries:
'Rows:
' Row 1 = date
' Row 2 = starttime
' Row 3 = endtime
' Row 4 = Description
' Row 5 = Location

For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)

With olApt
.Start = arrAppt(i, 1) + arrAppt(i, 2)
.End = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = "Created by excel tool"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.Save
End With
Next i


Set olApt = Nothing
Set olApp = Nothing

End Sub
In my case there is less variables, as date = column F, nam... Read more

1 more replies
Relevance 64.78%

Hi everyone,

I am beginner in excel and have used it only for creating tables etc. but never used it details like using macros. I need your help in merging due dates frome excel 2003 to outlook. details are as under;

I have a worksheet with some internal office circulars / procedures record i.e. type of circular, date of issue, circular no., issuing department, issuing person and review date. I am responsible to inform the issuing department that a review date of the circular is just reaching preferrably 15 days before. This is a large database and this is one of my assignments. so I can't do it by checking excel sheet everyday. I want to auto update my outlook calendar from review date column less 15 days. Or can excel sen a message to outlook regarding this particularly without opening that excel sheet?
 

Answer:merging of due dates from excel 2003 to outlook calendar

You can't do anything unless Excel is running. The macro has to run from some app that is running.
 

3 more replies
Relevance 63.96%

Hi there!

So I've found the code below here: http://www.google.com/support/forum/p/Calendar/thread?tid=5e4909ca64cffe90&hl=en - Full credit goes to user 'tpuiatti'

Code:
Sub Generate_ICS()
Dim rng1 As Range, X, i As Long, v As Long
Dim objFSO, objFile
Dim FilePath As String
FilePath = "G:\Service.ics"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(FilePath)
Set rng1 = Range([a5], Cells(Rows.Count, "H").End(xlUp))
X = rng1

objFile.write "BEGIN:VCALENDAR" & vbCrLf
For i = 1 To UBound(X, 1)
objFile.write "BEGIN:VEVENT" & vbCrLf & "DTSTART:" & Format(X(i, 2), "yyyymmdd") & vbCrLf & "DTEND:" & Format(X(i, 2), "yyyymmdd") & vbCrLf & "RRULE:FREQ=YEARLY" & _
vbCrLf & "SUMMARY:" & X(i, 1) & vbCrLf & "END:VEVENT" & vbCrLf
Next i
objFile.write "END:VCALENDAR"
End Sub

This code takes the info from 2 columns in Excel: A which is the event title and B which is the event date. Then it export these informations to an .ics file which will add the event dates to the Oulook Calendar.

The macro and the .ics work perfectly, however i wan't to make a few changes/additions to fit my needs. Additionally I wan't this macro to:

- Pick up dates from column &... Read more

Answer:Additional code to Excel macro - export to Outlook

7 more replies
Relevance 63.96%

Hi there,

I have to prepare several daily emails which are prepared using a range of data (for example B3122) from Excel 2003 and then have to be sent using Outlook 2003. Unfortunately, due to various management requirements I am unable to merely attach the Excel file as an attachment or paste the Excel data into the email as a straight paste - I have to paste as Unformatted text and then manually change various lines of the email to Bold and Underlined text.
I am trying to write a macro that will do this automatically but am unable to get the Excel data pasted into Outlook (as unformatted text or otherwise). I am assuming that for the font changes to bold I will have to use a Find function in the macro.

The code that I have got so far is as follows:

Range("B3122").Select
selection.Copy

Dim olApp As Object, olMail As Object
Set olApp = CreateObject("Outlook.Application")

Set olMail = olApp.CreateItem(0)
olMail.To = "[email protected]"
olMail.Subject = Range("B3")
olMail.Display

I am new to VBA and so I may be missing a really obvious solution but after trawling the net I have been unable to find an answer.

Many thanks to anyone that can help and make my work a lot less tedious!
 

More replies
Relevance 63.96%

Hi, I have a macro in excel that automatically updates my outlook calender - I use it to keep track of actions/deadlines/etc...

The macro runs beautifully except... whenever I run it it creates duplicates of appointments it has previously made in my outlook calendar. Is there any thing that I can add to the macro below to ensure it doesn't duplicate?

Really appreciate any help on this one. Cheers.
Sub outlookexport()

Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0

'Create the outlook item for the table entries:
'Rows:
' Row 1 = date
' Row 2 = starttime
' Row 3 = endtime
' Row 4 = Description
' Row 5 = Location

For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)

With olApt
.Start = arrAppt(i, 1) + arrAppt(i, 2)
.End = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = "Created by excel tool"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.Save
End With
Next i
Set olApt = Nothing
Set olApp =... Read more

More replies
Relevance 63.55%

Hey everyone,

I was wondering if anybody on these forums could help me with the below task?

I run a backup program that generates email reports to a subfolder in my Outlook 2010 every 6-8 hours to tell me the status of every backup job that I have set up and have running. The email details the job name, the job start time, and the job status (running, completed successfully, failed, missed, cancelled etc).

At the moment, I sift through each and every one of these emails (I have over 100 backup jobs per day) and enter the relevant data manually into my excel spreadsheet. The spreadsheet looks like the following:

Job name Monday 1st Oct Tuesday 2nd Oct Wednesday 3rd Oct Thursday 4th Oct
Job_xxx SUCCESS SUCCESS FAILED FAILED
Job_yyy SUCCESS SUCCESS SUCCESS RUNNING
Job_zzz MISSED MISSED CANCELLED SUCCESS

What I&#8217;d like is to automate this process, so that each day, perhaps every 4 hours, a script would run that would search for certain values within each email I receive and update the excel sheet accordingly.

Example:

- Monday 1st Oct, 10am &#8211; email comes into inbox with subject/body containing &#8220;Job_xxx has started &#8220; >>>> update Excel sheet to record RUNNING under column &#8216;Monday&#8217; in row titled 'job_xxx'
- Monday 1st Oct, 1pm &#8211; email comes into inbox with subject/body containing &#8220;job_xxx completed successfully&#8221; >>>> update Excel sheet to record SUCCESS, ... Read more

More replies
Relevance 62.73%

I am currently trying to create appointments in a calender i have created in outlook using information from an excel spreadsheet. I have copied several codes from other forums to help with this Macro but have only been able to add the appointments to my calender.

The code i am currently using is:

Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
'Create the outlook item for the table entries:
'Rows:
' Row 1 = date
' Row 2 = starttime
' Row 3 = endtime
' Row 4 = Description
' Row 5 = Location
For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt
.Start = arrAppt(i, 1) + arrAppt(i, 2)
.End = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = "Created by excel tool"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.Save
End With
Next i

Set olApt = Nothing
Set olApp = Nothing
End Sub
I also have the calender location but have no idea where it would need to fit into... Read more

Answer:Macro/VBE to create appointments in Outlook from an Excel Spreadsheet (Office 2003)

16 more replies
Relevance 62.73%

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

Hi,

Is it possible for an email to trigger an Outlook macro and then for that Outlook macro to trigger a macro, which is external to Outlook (and Office)?

I'm using Outlook 2007
 

More replies
Relevance 61.09%

Hi Friends,

I'm very much new to Macro in excel. I wanted to Run Macro in the following situation :
I have 2 Excel Book having same header but some data which to be consolidated in a 3rd Book. I recorded macro in 3rd Book the macro function is to " to open Book 1 & Book 2 & consolidate in Book 3" on running the recorded macro.

But after saving the macro and running tha macro again with some new data in Book 1 & Book 2 it gives the following error message "Run-time error '9':
Script out of range

and on debug the following macros are displayed.
---------------------------------------------
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+l
'
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Book2.xlsx"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Book1.xlsx"
Windows("Report.xlsx").Activate

Selection.Consolidate Sources:=Array( _
"'C:\Documents and Settings\Administrator\Desktop\[Book1.xlsx]Sheet1'!R1:R1048576" _
, _
"'C:\Documents and Settings\Administrator\Desktop\[Book2.xlsx]Sheet1'!R1:R1048576" _
), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
End Sub

--------------------

can anyone help. I'll be very much grateful to you.

I'm using excel 2007 but I dont think it has got to do something with macro!

thank you in advance.

Hemen
&nbs... Read more

Answer:Solved: Help in macro (Excel)

This message usually occurs when the code is trying to call a workbook or worksheet that is not available. Your code opens two workbooks from your desktop (Book2.xlsx, Book1.xlsx) and then is trying to activate a workbook called Report.xlsx. If this workbook is not already open then you will get the error. You need to add some code to open the workbook prior to activating it.

Regards,
Rollin
 

2 more replies
Relevance 61.09%

i have created a spreadsheet that has a list of names down the left, days of the week and dates across the top and a "user' choose in the data fields.The spreadsheets are in one workbook and i have a sheet for each month.

What i need to do is create a form that will enable users to choose a "Date" period ie start and end dates and choose a "name". i then need this form to have a button on it that when clicked would generate an email to the relative person named which would have all of the relevant data in it.

I know how to create the actual form, it's linking everything to it thats the problem, so would really appreciate some help.

Hope this all makes sense !!!
 

Answer:Solved: Excel macro help req

15 more replies
Relevance 61.09%

I need to write a macro to look down all the cells of an entire column of one worksheet and whenever the value of a cell is greater than zero, copy that value and paste to the next empty cell along a different column. For instance, I'm looking down the entire column and D5 is greater than 0, so I paste it to N1,. Next, D8 is greater than 0, so i need to be able to paste this to N2 and so on. Can someone help? Thanks
 

Answer:Solved: Excel Macro

Hi,

Copy and paste the code to a "Module"
Code:

Sub Check_Zero()
Application.ScreenUpdating = False
Dim cel As Range
Dim lcel As Long
For Each cel In Range("D2:D" & Range("D65536").End(xlUp).Row)
If Cells(cel.Row, 4).Value > "0" Then
lcel = Range("N65536").End(xlUp).Row + 1
Cells(lcel, 14).Value = Cells(cel.Row, 4).Text
End If
Next
End Sub
 

3 more replies
Relevance 61.09%

I have an excel report (tab name is "sample report")that lists many rows of data (each row is a new case). In column "H" it lists who is working on each case. Column "I" lists dates. If a date is shown then that means the case is closed. If the cell is empty that means the case is still open.

What I need is a macro that will copy all of the "Open" cases from the "Sample Report" tab and put them into the other tab "Open Case Log" under the specific names in column "H". I have put 5-10 blank rows under each name on the "Open Case Log" tab but I am never sure excalty how many open cases each person will have at any given time. This is where I am having the problem. I have attached the sample excel report.

Any and All help would be greatly appreciated.

Thank you.
 

Answer:Solved: Need Help with Excel Macro

16 more replies
Relevance 61.09%

hi all i've never written a macro before and have been searching online for how to do it but am still pretty confused. i have 4 columns of data, the first of which has a number 1-8 or is blank. if its blank, i want my macro to fill in a number 1-8 based off the next 3 columns. below is how i've thought about how i want my macro to look, but i don't know how to translate this (b, c, d, and e are the columns). any help would be MUCH appreciated. thanks,
If b = "" Then
{If c > 0 Then

(If d > 0 Then

If e > 0 Then b = "1"

If e < 0 Then b = "5")

(If d < 0 Then

If e > 0 Then b = "2"

If e < 0 Then b = "6")}
{If c < 0 Then

(If d > 0 Then

If e > 0 Then b = "3"

If e < 0 Then b = "7")

(If d < 0 Then

If e > 0 Then b = "4"

If e < 0 Then b = "8")}
Else: b = b
 

Answer:Solved: excel macro help please

6 more replies
Relevance 61.09%

Hello,

I've used this site to create my current excel file; it works pretty good but now it need the evolve and I require some help.
I'm sure there are easier ways to get what I need; I am no expert at macro writting so please bare with me.

THe file current creates graph for me; 'data input' has a button where i input the locations i require a graph for and it simply selects info (from a master file database) and pastes it into 'sheet2' where a graphs exists.

the code is written so that it will go to a pdf print screen for me to input the name and save. this worked well but now there are so many locations that i would like to automate some functions.
what i would like the update to do is the following;

-'results table', column A, "Location" will have several locations listed (only in the white blank spots). I need the macro to generate a graph for each location listed.
-for this to occur, it makes sense that I would also require the pdf to automatically name each file ~ naming it the location that it was looking up (otherwise i will have a bunch of pdf save as files open and i wouldn't know which was which.
any and all input would be greatly appreciated. i also looked up the auto name pdf file; i did not include my attempts to add as it kept getting hung up at the 'save as' file box. i can share my pregress on that if you like.

below is my current code. Please email me so that i can send you the file a... Read more

Answer:Solved: Excel Macro help

15 more replies
Relevance 61.09%

Hello
I'm using Excel 2003 (on Win XP). I currently have an excel doc which I use for "form filling". There is a lot of questions, 40 or so on Tab1 and another 40 or so on Tab2. Both use "data, validation, list" for the questions (to sub sub level). Once the form is filled in it is saved as an excel file. Then I fill in the next excel doc, roughly about 50 excel docs are created for each job. I know I should probably use a database but I've never used one before.
What I'm looking for is a macro at the end of the form which I could click and it would collect data from tab1, cell A1, A2, B3, C7 and MOVE it into tab3 to a position A2 – all in a single row (i.e. B2, C2 etc). I can then amend the macro and use it for tab2, so the data is saved in tab4.
Here is the tricky part.
The first reference in cell A1 contains the “Question No” referenced as Q1, Q2, etc as a list box. Sometimes I will need to go back to a previous “question” and amend details but most of the time it’s used for printing purposes at the end of the day. Hence I will need a second macro which when clicked on will ask for “Question Number” and once I input it in, should retrieve the information from Tab2 and insert all the data back into the original cells. I suspect this is impossible as it would then paste the raw data over my “list boxes” making them unusable.
But maybe by having another tab, say “amendments” with a macro button on it, I could select the “questio... Read more

Answer:Solved: Need a Macro for Excel

16 more replies
Relevance 61.09%

Hello TECHGUYS.
We are using macro fro creating P.O.B. lis ( persons on board) , from the 3500 persons, copying only those who is on board at the moment , then grouping them by company and than sorting in alphabetical order in each group.
Now is the challenge : Is that posiible to modify macro or add another function to it to make it sort personnel of first company in the list by rank / position?
I am almost green in scriptin, but very interesting in it , but now my boss wants to see Himself on top of the list He's last name not starting from A
Thanks in advance...
 

Answer:Solved: Excel macro help

12 more replies
Relevance 61.09%

I am have a form in excel that when a certain person types his name at the end the following cell will put in date that he signed it. The problem is that when you e-mail the doc. the following day (or whatever day) the date changes to the current date and not the actual date that the doc was signed. (by signed i mean the person typing his name there) Here is the code that is currrently in the doc.
=+IF(F36="Persons Name",H43," ")

I'm not sure if there is some kind of formula for this or do i have to write a macro for it. If i do have to write a macro for it, could someone please help me out with it.
 

Answer:Solved: Excel Macro

6 more replies
Relevance 61.09%

I am running Excel 2003. I am creating a macro that open files from a external hard drive. I was hoping to find a way for the macro to find the Drive letter of the external hard drive instead of me having to statically assign the drive letter.

Any help with this would be great.
 

Answer:Solved: Excel Macro

9 more replies
Relevance 61.09%

here my situation: I have created 2 worksheets. Both are contact list.
the 1st list is of People who work in the same building as I. The second is
a list of nationwide remote users. My boss emailed me a third sheet
containing names from both list combined. the question I have is can I
write a macro to check the third sheet against the 1st two then seperate
them back out? if you need more info let me know
 

Answer:Solved: need help with excel macro

8 more replies
Relevance 61.09%

Ok not sure if this can be done or not, but hopefully it can.
What I want to create is a macro or something that would read the values in column A, if it finds a duplicate number it would insert a row under it

i.e.

5
5
6
7
8
It would insert a row between the 5 and 6.
Is this possible?
 

Answer:Solved: Excel Macro - Not sure if it can be done

16 more replies
Relevance 61.09%

I have 50+ excel workbooks and I need to extract 1 tab from each of them and create an individual file for each.

A girl who used to work in my office found a macro or program using excel, that only works on PCs, but I have one available, thatall you have to do is have the selected tab saved so that it opens up to the one you want and paste the entire thing as values and it will do it for you real fast. The alternative is to go in and copy and paste each tab or extract the sheet in each and every one. If anyone can help me find the name of this or where I can find it I would really appreciate it. Oh ya the girls who used to work in my office moved to India, canceled her cell and I do not have her personal email so contacting her is harder than I first thought.
 

Answer:Solved: Excel Macro?

14 more replies
Relevance 61.09%

hi... i am really having a problem with creating a macro, could you please help me.

i have to copy content from a auction website, paste it into excel.
this is what the content looks like

Auction - 125458
view bigger image --- name of item
ref number --- start date
end date --- price of item
page views
users tracking
no bids
relists remaining (this is sometimes left out and causes my stress)
copy or relist
edit
close
delete
the above content over 3 columns and over different rows.
i managed a simple macro to put these into column’s, but the problem being that sometimes there is not the same amount of rows with the relists remaining left out sometimes....
so what i need is a macro to put the data above into column’s,
eg... word starting with auction = c1
word starting with view bigger = d1
word starting with item name = e1
---------- then when the next word starting with auction = c2

could you pretty please help me with this
 

Answer:Solved: excel macro help please

9 more replies
Relevance 61.09%

Hello, I need some help with a macro. I am writing an If...Then...Else statement. The If is based on whether the current cell is blank. This seems simple enough from my experience with Lotus 1-2-3 macros but I can't make it happen. Here is where I am -

If ActiveCell.Value.Isnull = True Then
Blah...Blah...Blah
Else.... Help me! Thanks
 

Answer:Solved: Excel Macro (VB)

Welcome to the board.

If ActiveCell = "" Then
Do this
Else
Do that
End If

It's the same as IF formula, only in code.

Rgds,
Andy
 

2 more replies
Relevance 61.09%

Hello,

I've built an excel spreadsheet and am having trouble with a part of a macro. The relevant section of the macro that I am having trouble with is below:

Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$A$1:$A$1094"), _
ActiveSheet.Range("$B$1:$C$1094"), False, True, , "PS stats", False, _
False, False, False, , False

The spreadsheet is basically a template, so as I get new data, I open it up, dump the data in, run the macros and save. The problem is, the data is often different sizes, so one day I may have 1000 rows, and the next 1400. This is not a problem with generating charts, since I can just select that it plots cells 0-2000, and it will ignore cells without data, but if I do this for the above regression, the macro will not fully run and I get an error. It's not really a big deal, as I can open up the macro every time and just edit the range to fit the data I have, but other people often need to use it so I'd like to make it as simple as copy, paste, and run macro1.

Is there a way to insert a macro within the macro so excel automatically searches the column and then runs the regression?

Something like this

Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("Only rage of cells with data in them"), _
ActiveSheet.Range("$B$1:$C$1094"), False, True, , "PS stats", False, _
False, False, False, , False

I've seen a macro here on how to find cell... Read more

Answer:Solved: Help with excel macro

9 more replies
Relevance 61.09%

Hi,

I have following data with me and this data extends till 1000 rows.

IDSYSTEMASSOCIATED_ABC_PROCESSASSOCIATED_XYZ_PROCESS9ABC Stage IIIABC.101-1XYZ.103-459ABC Stage IIABC.101-2XYZ.103-459ABC Stage IABC.101-3XYZ.103-459XYZ Stage IABC.101-4XYZ.103-459ABC Stage IABC.102-2XYZ.103-469ABC Stage IABC.103-2XYZ.103-479XYZ Stage IABC.103-4XYZ.103-4810ABC Stage IABC.104-1XYZ.105-4-310ABC Stage IIIABC.104-2XYZ.105-4-410ABC Stage IIIABC.104-6XYZ.105-4-510XYZ Stage IIABC.105-1-1XYZ.105-4-610ABC Stage IVABC.106-1XYZ.107-3

I am looking for a macro which will give me results as mentioned below.

IDABC / XYZ METRICABC XYZ STAGES9ABC.101-1
ABC.101-2
ABC.101-3
ABC.101-4
ABC.102-2
ABC.103-2
ABC.103-4
XYZ.103-45
XYZ.103-45
XYZ.103-45
XYZ.103-45
XYZ.103-46
XYZ.103-47
XYZ.103-48
ABC Stage I
ABC Stage II
ABC Stage III
ZYZ Stage I
10ABC Stage I
ABC Stage III
XYZ Stage II
ABC Stage IVABC.104-1
ABC.104-2
ABC.104-6
ABC.105-1-1
ABC.106-1
XYZ.105-4-3
XYZ.105-4-4
XYZ.105-4-5
XYZ.105-4-6
XYZ.107-3



In Column 'ABC / XYZ METRIC' the values from coulmns 'ASSOCIATED_ABC_PROCESS' and 'ASSOCIATED_XYZ_PROCESS' are combined for the respective ID.

Thanks in advance,

Regards,
Akki
 

Answer:Solved: Excel Macro Help

I don't know how the table are disorganized after submmiting the querry. Submmiting new query with attachment.

Regards,
Akki
 

1 more replies
Relevance 61.09%

I have added a pop up calendar control to my spreadsheet (Excel 2003) - is there a way of limiting the control to be active in certain columns in cetain sheets?
i.e. columns 11 and 12 on sheet 2 and columns 4 and 5 on sheet 4
Currently you can add a date to any field regardless of formatting...
Many thanks
 

Answer:Solved: Excel Calendar Control

8 more replies
Relevance 61.09%

Hello, I discovered this forum when searching for a way to create a search macro to copy rows of data based on values in certain columns. The post resolved by bomb#21 has given me code that does 90% of what I need:
http://forums.techguy.org/business-applications/559825-solved-excel-search-macro-needed.html
Sub Search()
Range("E11").CurrentRegion.ClearContents: Range("E11") = "Results"
FindWhat = UCase(Range("E5"))
For Each Cell In Range("B2", Range("B" & Rows.Count).End(xlUp))
If InStr(Cell, FindWhat) <> 0 Then
Cell.Offset(, -1).Resize(, 3).Copy Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next Cell
End Sub

As an absolute newbie to macros (I've been using them for 3 days, and it took me an hour to figure out why I was getting errors when changing from column B to column A in the above code - for any other newbies it is the Cell.Offset (, -1) part of the code), I was wondering if it is possible to have this code in a Sheet2, to return data from Sheet1 (I had assumed it would be as easy as changing the code to <For Each Cell In Range("Sheet1!B2", Range("Sheet1B" & Rows.Count).End(xlUp))>) and whether it would be possible to have a search popup box instead of just entering text into a cell (E5 in the above code).

Apologies, I know that this is probably a very basic question, that I could learn with some excel training. Unfortunately this is a smalli... Read more

Answer:Solved: Excel search macro almost solved by an earlier bomb#21 post

9 more replies
Relevance 60.68%

I have:
A worksheet with 11 columns, about 400 rows, with mostly text data.

I want:
To create a "Search" button on the spreadsheet that opens a form with a text box to enter search criteria, with a "Search" button and a "Cancel" button. When the user enters criteria and clicks "Search," rows with cells matching the data return in a different worksheet.

The problems:
Say the user wants to search for "Texas." Texas could be in several cells within the same rows - B7=Texas, C7=Texas Study, D7=Go Texas, E7=Texas, Texas, Texas, etc. So I don't want it to return the same row 6 times since it has 6 different instances of "Texas."

As shown above, the specific keyword they're looking for may be buried in other text within the same cell, so the search function needs to sort through and find all the instances.

I'm a beginner to this macro stuff, but I have the basics down. I just need help with a code for the search function. Any help would be appreciated!
 

Answer:Solved: Excel Search Macro

12 more replies
Relevance 60.68%

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

sort using macro in excel vb

I want to be able to do a sort in excel VB,
the area I'm having trouble with is selecting the range
the spreadsheet header is in row3

so I want to select from A3 to - whatever the active used range is & then sort that range back to have the header in the sameplace A3.

I cant work out the code ???
please help thanks
 

Answer:Solved: sort using macro in excel vb

13 more replies
Relevance 60.68%

Hi guys

I was searching through the forum and found this post:
http://forums.techguy.org/business-applications/851934-excel-search-macro.html

I was looking to use the same code for the search function.

Sub test()
If ActiveSheet.Name <> "Sheet1" Then Exit Sub
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Sheets("Sheet2").Range("2:1000").Delete
SearchTerm = Application.InputBox("What are you looking for?")
Application.ScreenUpdating = False
Range("L1") = SearchTerm
Range("L2:L" & LastRow).FormulaR1C1 = _
"=IF(ISERR(SEARCH(R1C12,RC[-11]&RC[-10]&RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1])),0,1)"
If WorksheetFunction.CountIf(Columns(12), 1) = 0 Then
Columns(12).Delete
Application.ScreenUpdating = True
MsgBox "None found."
Else
For Each Cell In Range("A2:A" & LastRow)
If Cell.Offset(, 11) = 1 Then
Cell.Resize(, 11).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
x = x + 1
End If
Next Cell
Columns(12).Delete
Application.ScreenUpdating = True
If x = 1 Then
MsgBox "1 matching record was copied to Sheet2."
Else
MsgBox x & " matching records were copied to Sheet2."
End If
End If
End Sub

Can anyone please help me to extend this search function over 24 columns of data instead of the 11 as above?
I have very little knowledge of VBA.

Thanks ... Read more

Answer:Solved: Excel Search Macro

6 more replies
Relevance 60.68%

Hi there,

Could anyone help me with a formula or macro that will read the data in column A and then display a value in column C in the attached .xls?

EZ-Links would display 5
Hutton would display 3

Many thanks!
 

Answer:Solved: Excel Formula or Macro help please

Place the following formula in C11 and copy down for all rows. Will there ever be any other values that you need to check for?

=IF(A11="EZ-Links",5,IF(A11="Hutton",3,""))

Regards,
Rollin
 

3 more replies
Relevance 60.68%

I know this should be very simple, but for some reason I cannot recall how to select last row in Excel.
 

Answer:Solved: Excel Macro Select Last Row

I believe I have figured it out.
Code:

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
Range("A" & LastRow2).Select
End With
 

3 more replies
Relevance 60.68%

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

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

Here is the code I am using:
Code:

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

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


 

Answer:Solved: Excel Macro -> Save As PDF

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

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

1 more replies
Relevance 60.68%

This is just the basics of what I have. For my Macro I am really just recording my actions. I have an equation which I currently copy down. Problem is when I get this report it does not usually end at O1900. It varies every time. Is there an entry I can change to the code which will allow me to range the Final Commission down to the very last cell in column O???? Thanks!!

Company Cost Comm Trade Final Commission
ABC COMPANY $1,500.00 .0100 $.00 $ 15.00
ZDYSD COMPANY $9,301.00 .0200 $.00 $ 186.02
12312 COMPANY $18,700.00.0300 $.00 $ 561.00
ADVANCED COMPANY $2,321.43 .0400 -$124.47 $ 87.88
Columns("O:O").ColumnWidth = 9.14
Range("O2").Select
ActiveCell.FormulaR1C1 = "=(RC[-6]+RC[-1])*RC[-5]"
Range("O2").Select
Selection.Style = "Currency"
Selection.AutoFill Destination:=Range("O2:O1900"), Type:=xlFillDefault
Range("O2:O1900").Select
LastNumber = Range("b1").End(xlDown)
Range("b1").Select
 

Answer:Solved: Excel Macro, I'm Stuck!!!

7 more replies
Relevance 60.68%

This post is related to an earlier one, which has now been solved and closed:http://forums.techguy.org/business-applications/955009-excel-2007-lookup-formula.html. The macro outlined in this thread works fine as long as there are 4 values in each row the macro references. If there are less than 4 values the formula errors when trying to return the

Code:
WorksheetFunction.Large
value. Could somebody please edit the macro so that if it causes an error due to too few values, that it will then break out of the code instead of keep running? The ideal situation would be that it outputs any values it finds, and skips past and carries on if the function errors.
 

Answer:Solved: Excel Macro Error

No problem - all sorted now A bit of out of the box thinking was all that was needed!
 

2 more replies
Relevance 60.68%

Hi,

I'm a very new VBA user and have spent the better part of 2 day on this great website looking for a similar problem i've run into.

My problem is that i want information from "test" workbook~"results" worksheet to automatically update the "Tech Data" worksheet as well as export an update to the "Master" workbook.

FYI, two separate people input data: iron&manganese and Corrosion&scale inhibitor residuals. This data is exported to the 'results' worksheet and the same simple formula can be used to export to "tech data" worksheet. I am having a hard time getting the 'tech data' worksheet to export to the master workbook. I'm hoping someone can help me ensure the master file compiles all informaiton being exported to it. Note that the master file has several other headings as i was hoping to expand on the starter macro.

I created the tech data worksheet so data placement matched the master workbook format.
 

Answer:Solved: Excel-macro code help

15 more replies
Relevance 60.68%

Hi everyone,
does anyone know how can I accomplish the following:
I would like to pop a message when I enter a qty. this field will add a amount time the qty then the result will be added for a total of rows. If the result is greater a 100 then I need to popup a message to warm the user.
I know data validation do something like that but only when you are in that cell.
If anyone have any idea of how to do this please let me know

Thanks,
AMD2800
 

Answer:Solved: Excel question Macro or VB ?

10 more replies
Relevance 60.68%

I have a VBscript that I would like to run with an Excel macro. Can anyone tell me how this can be done?

Is there a way to add the VBscript code to the Excel macro?
 

Answer:Solved: Excel Macro and VBScript

Sure, it's really the same syntax, paste it and try it.
Do you have the code?
 

3 more replies
Relevance 60.68%

I need an excel macro to move fields from two rows to one row for about 5000 fields.

Example
Current Excel File:

Col1 Col2 Col3 Col4
Row1 ID# Address Ph1 Ph2
Row2 IDName city,st,zip ven#
Row3 <<<BLANK>>>
Row4 <<<BLANK>>>
Row5 ID# Address Ph1 Ph2
Row6 IDName city,st,zip ven#
Row7 <<<BLANK>>>
Row8 <<<BLANK>>>
This continues for about 5000 more fields
Need the file to be
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Row1 ID# IDName Address city,st,zip ph1 ph2 ven#
Row2 ID# IDName Address city,st,zip ph1 ph2 ven#
<<<<Continued for all vendors>>>>

They don't necessarily have to be in that specific order I just want the each vendor and their information on one row.

Any help you can provide is much appreciate.
 

Answer:Solved: Excel Macro Needed!!!

Welcome to the board.

As long as Row1 means Row1 (not first row with data), then:

Sub test()
Application.ScreenUpdating = False
x = Range("A" & Rows.Count).End(xlUp).Row
For Each Cell In Range("A1:A" & x)
If Cell.Row Mod 4 = 1 Then
Cell.Resize(, 4).Copy Cell.Offset(, 5)
Cell.Offset(1).Resize(, 3).Copy Cell.Offset(, 9)
End If
Next Cell
Columns("F:L").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
Application.ScreenUpdating = True
End Sub

Try it in the attached, then on a copy (for safety) of your real data.

HTH
 

2 more replies
Relevance 60.68%

I need a macro that will prompt the user for a number, then will search a column of data and give the user a green light if the number is already entered or a red light if the number is not already in the data base. Preferably I would like the data base as a separate workbook so it could be updated remotely while the program is running at an operator station. I am new to excel and was wondering if there is a clean simple way to do this.

Thanks,
Allen
 

Answer:Solved: Excel Search Macro

16 more replies
Relevance 60.68%

I'm trying to populate blank fields with data from above. For example, in a column, there will be one cell containing data, I'd like to populate the blank cells below until another populated cell appears etc...

I think this is a "Run a For Each...next Loop" macro but do now know VBA language well enough to build.

Any ideas?
 

Answer:Solved: Excel 2003 macro

16 more replies
Relevance 60.68%

Hi,
I am in some major need of help.

I'd like a macro -- actually a plug in/add-in if that can be set up -- that finds the maximum DPI print quality setting of a worksheet within an Excel workbook (this is the setting under File | Page Setup | Page tab | Print Quality), and will take that max value and make it the default DPI setting for all of the pages in that same workbook.

At the same time, I'd like to have a custom footer placed on all of the pages of the workbook that reads something along the lines of "My footer text - Page x of y".

If possible, I'd like this plug-in to run upon saving of the Excel file.

Thanks very much!
 

Answer:Solved: Excel Macro help needed, please!

16 more replies
Relevance 60.68%

Hi all, I need some help please with excel 2003 vba code. I have recorder the macro which I want to look at a cell and copy the value, select the custom filter, paste the cell value into the custome filter and then sort the results in Z-A order.

The code however (generated by macro recorder is inserting the text value rather than the cell reference.

Can anyone tell me how to edit the code please?? The part number 2000801990 is shown in red - this should be the cell reference.

Thanks

Nic

Sub engineer_consumption()
'
' engineer_consumption Macro
' Macro recorded 18/09/2008 by Nic Cunliffe
'
'
Range("B1").Select
Selection.Copy
Selection.AutoFilter Field:=2, Criteria1:="=2000801990", Operator:=xlAnd
Range("C177").Select
Application.CutCopyMode = False
Range("A8:C44630").Sort Key1:=Range("C177"), Order1:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortTextAsNumbers
End Sub
 

Answer:Solved: Excel 2003 macro help

8 more replies
Relevance 60.68%

I am trying to get make a macro/script to hide cells based on what cells on another sheet are filled.

If A1 is filled in sheet 1 then I want 2 columns to show on sheet 2
If A2 is filled in sheet 1 then I want 4 columns to show on sheet 2
Etc.

I currently have macros to hide the appropriate columns and have them assigned to buttons. I want to eliminate the buttons and have sheet 2 automaticly format based on which cells on sheet 1 are filled.

Any suggestions? I am sure I will hvae to use VB. I think I can get it to run a macro if a cell is filled but how do I get it to run based off of the highest cell thats filled. If A5 is filled then use macro for 10 columns ignoring cells A1-A4.

Hope this makes sense.
 

Answer:Solved: Excel, VB, IF Statement to Run Macro

11 more replies