Over 1 million tech questions and answers.

Solved: Hyperlink Macro not working in Excel 1013

Q: Solved: Hyperlink Macro not working in Excel 1013

Hi there

John Walkenbach, the Excel guru who wrote many Excel VBA books, gave me this hyperlink macro that finds Excel workbooks. The macro is complex but I'm sure that someone out there can figure it out. This macro works great in Excel 2000 but does not work in Excel 2013. When I run the macro in Excel 2013 I get a message that says "File not found" and I can't figure out why. I believe that the line "FileType = msoFileTypeExcelWorkbooks" could be causing the problem but I'm not sure.

Thanks

Mario

Below is the code.

Sub Hyperlink1()

Dim i As Long
Dim Folder As String
Dim FileToFind As String

Set objShell = CreateObject("Shell.Application")
On Error Resume Next

Folder = "C:\"
FileToFind = ActiveCell
Select Case ActiveCell
Case ActiveCell
Case Else
MsgBox "The file was not found"
End Select

With Application.FileSearch
.NewSearch
.LookIn = Folder
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 1 Then
If .FoundFiles.Count = 0 Then
MsgBox "File not found.", vbCritical

Else
For i = 1 To .FoundFiles.Count
If Right(.FoundFiles(i), Len(FileToFind)) = FileToFind Then

Workbooks.Open .FoundFiles(i)
End If
Next i
End If
End If
End With
Set objShell = Nothing
End Sub

RELEVANCY SCORE 200
Preferred Solution: Solved: Hyperlink Macro not working in Excel 1013

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

A: Solved: Hyperlink Macro not working in Excel 1013

Apparently MS has degrade the FileSearch over time.

This looks like something to try Microsoft.com Thread

Read other 14 answers
RELEVANCY SCORE 89.6

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.
 

A:Solved: Excel Hyperlink Macro to File

Read other 16 answers
RELEVANCY SCORE 80.8

Here is a challenge for all you Excel Gurus. Attached is a macro that when you run it, searches for the file that you have selected in an Excel worksheet. For example, I have a file called testing.cells. I have typed this file in Cell A1. I then select cell A1 and run the macro and voila, the Excel file called testing.cells appears. The reason I do not do a normal Excel hyperlink is that the hyperlink at times does not work if I e-mail it my colleagues since the file is on a server. This is due to how the paths are configured in different computers.

The attached macro works great if I'm hyperlinking to another Excel workbook. I have tried to customize the macro to bring up a pdf file but without success. I'm Excel macro guru. As a matter of fact, the attached macro was given to me by John Walkenback, the author of many Excel books.

Is it possible to do this or not?? I do not want to waste anybody's time if this is not do able

Mario
 

A:Excel Hyperlink Macro

I guess no one out there can help me on this issue. Surely to God someone must have a clue.

Mario
 

Read other 2 answers
RELEVANCY SCORE 80.8

Hello

I am very new to using Macros but I am trying to create a new one that creates a new hyperlink and new file when ever a cell in column A has a value put in it.

What I would like is for the MAcro to take the Value of A1 and impose that value into the name of the new document (which would be created as part of the Macro), which is then shown as a hyperlink in A1.

This process could then repeat down the rows.

Would this be possible?

Thank you
 

A:Excel Macro Hyperlink

I made this a few years back, you will need it to convert to hyperlink, and you can use the references to adapt to to your code.

you should use the recorder first to establish a base of what you are trying to do, then adapt.

Code:
Sub hyperlink()
'
' hyperlink Macro
' 7/18/2007 by Ziggy
'
Application.ScreenUpdating = False

Dim hyper As String ' hyperlink variable
'
Dim endrow As Integer
Dim RowX As String ' Cell to change
Dim RowC As Integer 'row Counter
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet

endrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row


RowC = 1



Do Until RowC > endrow

RowX = "A" & RowC 'Modify Letter for Column

Range(RowX).Select

If Range(RowX).Value <> "" Then ' skips cell if blank

hyper = Range(RowX).Value
Range(RowX).Hyperlinks.Add Anchor:=Selection, Address:= _
hyper _
, TextToDisplay:= _
hyper

End If


RowC = RowC + 1

Loop


Application.ScreenUpdating = True


End Sub

 

Read other 1 answers
RELEVANCY SCORE 80

Hello!

I am new to this site and to the world of macros. I am creating a form in Excel for my company and cannot find any info for creating a macro for hyperlinked info.

Background of my form:
It's a time off request form. My dad (who knows a lot of Excel and macros (but not this kind)) has helped me get to where I am at this point.

Currently I have a dropdown for employee names. Once a name is selected, it automatically fills in their manager and department number. I have a Reset Form button to clear info out and would like to create a Submit Form button that would send the form to the manager. Is something like this possible?

I'm guessing it would need to pull from my Tables sheet.

Help! I hope I made some sense.

P.S. I will attach a sample of what I have so far.
 

A:How to have a macro add hyperlink to a cell in Excel

Read other 9 answers
RELEVANCY SCORE 80

Not sure if this is possible, but I'd like to get a macro for the following:

I've got a workbook with multiple tabs, let's call them "Summary", "Tab1", "Tab2", etc. On the summary tab, I'd have a value with a reference next to it that says "Tab1". I'd like that reference to be hyperlinked to a similar cell on worksheet "Tab1" where it finds the reference that says "Summary". So basically, if my summary sheet shows an amount of $50, I'd have a reference next to it that says which sheet shows the support for that number, and on the supporting sheet, it would reference that that amount shows up on the summary. I'd like to run a macro that would find those references and hyperlink them to the correct sheets (and if it can do it, to the specific cell on that sheet).

Anyone?
 

A:Excel hyperlink macro question

Hi there, and welcome to the board!

Can you define your requirements a little bit more? We need to know about your data structure of your workbook. What ranges are you talking about? Are there any other hyperlinks? What would you like them to say? Care to give a real-world example with your file? Can you upload it?
 

Read other 1 answers
RELEVANCY SCORE 80

Hi, I have got an Excel spreadsheet which has incident numbers in column A. For each of these incidents I need to put in hyperlinks which are very very similar. The first part of the hyperlink is the address to the database and the second part is the Incident number itself. I'm trying to create a macro which automates the process of putting in the hyperlink plus the incident number in. Can this be done?
 

A:Excel 2007 Hyperlink Macro

Read other 7 answers
RELEVANCY SCORE 80

I am not sure how to go about this macro. I am trying to produce a hyperlink in a column (equipment info) but as it goes down a row change the hyperlink to reflect its file name that is found in a different column.

so the hyperlink location is "\\SNOGP01\poshare\PoShare\North Omaha System Files\10 Year Mechanical Plan, major plant equipment\1AC-1A Info & Name Plate.docx"

The next one down would need to change the "1AC-1A" to "1AC-1A-M"

Any help is much appreciated.
 

Read other answers
RELEVANCY SCORE 79.2

Hi Again

Keballah gave me this macro that when I put the cursor in a cell that has for example WW1.xls and then execute this macro it opens up the Excel workbook named WW1. However, when I try to open a PDF file like WW1.pdf I get garbage and not the PDF file. Can someone out there take a look at the macro below as an example and create a new one so that it also brings up PDF files??

Thanks

Mario


Sub Demo()
Dim fso As Object 'FileSystemObject
Dim fldStart As Object 'Folder
Dim fld As Object 'Folder
Dim fl As Object 'File
Dim Mask As String
Dim thisFolder As String
Dim fullFilename As String

Set fso = CreateObject("scripting.FileSystemObject") ' late binding
'Set fso = New FileSystemObject 'or use early binding (also replace Object types)

Set fldStart = fso.GetFolder("M:") ' <-- use your FileDialog code here
Mask = ActiveCell.Value
thisFolder = fldStart & IIf(Right(fldStart, 1) = "\", "", "\")
Debug.Print thisFolder, Mask
If Not IsError(Dir(thisFolder, vbReadOnly)) Then
If Dir(thisFolder & Mask, vbReadOnly) <> "" Then
Workbooks.Open Filename:=thisFolder & Mask
Else
fullFilename = ListFolders(fldStart, Mask)
If Len(Trim(fullFilename)) > 0 Then
Workbooks.Open Filename:=fullFilename
End If
End If
End If
End Sub

Function ListFolders(fldStart As Object, Mask As String) As String
Dim fld As Object 'Folder
Dim thisFolder As String
For Each fld In ... Read more

A:Hyperlink Macro in Excel VBA that finds PDF files

You're getting garbage as you're asking excel to open a pdf as a workbook:

Code:
Workbooks.Open
You'd need to open the PDF using a PDF viewer and code accordingly.
This might be helpful:
http://www.myengineeringworld.net/2012/07/vba-macro-to-open-pdf-file.html
 

Read other 2 answers
RELEVANCY SCORE 78

Hi,

I've used Tech support guy for quite a while and almost always find what I need.
I guess it's about time I join and post a question

So this is my problem.
I send out an excelreport every day.
In it I use the content in column X to populate the hyperlink address in column Y.
I've done this manually but now the report is getting bigger and a macro seems in place.
Problem is I just don't know enough about the syntax used in excel.

What I want to do is loop though each row - if there is data in column Y then check in column X.
If X is populated use the data as hyperlink address in column Y (info in Y should still be what is shown).
This should be done for each row where Y is populated.
Then the X column shall be deleted (but I guess I could do that manually )

It feels pretty straight forward and I could do it in RPG or SQL but the syntax in excel befuddles me
Any tips?
 

A:Excel macro - use info from one column as hyperlink address in another.

Assume Y2 and X2 cells
=if( Y2 = "", "", IF(X2="", "" , do Y2)

but you need to have the formula in Y and change Y - i think that may need a macro, as you are testing Y and also rewriting Y

a spreadsheet with dummy data would be useful to see
 

Read other 1 answers
RELEVANCY SCORE 72.4

Hi All

I have a trial excel 2007 version that came with my Vista Home Premium.
I had a spreadsheet which contains macros and I had no problems running with XP.

I changed some of the settings and completed the following:
http://www.howtogeek.com/howto/wind...nt-control-uac-the-easy-way-on-windows-vista/

I followed the following link from the developer of my particular spreadsheet and completed the following for 2007 version:
http://www.mdmproofing.com/iym/macros.php#excel2007

I still cannot use some of the functions that I had been using before such as Sort and doubling clicking a cell which is supposed to open a calendar and auto function add monetary figures.
Could it be the trial version? Any help is appreciated.

Thanks,
Transformer Man
 

A:Solved: Excel Macro Vista not working

Hi Transformer Man,

I was just wondering, have you solved the problem for Macros working on your Vista Machine?

As I currently working with a collegue to solve this problem, and would like to confer with someone else who has had a similar problem.

Regards,
Tom Griffiths.
 

Read other 3 answers
RELEVANCY SCORE 72.4

Hi.

I have built a macro that imports Data from another worksheet, and to add to the user experience, I want to add a progress bar.

I have added the code, and userform to make this happen, however, it wont update (i.e. show me the progress).

I have tried turning screenupdating on, setting the userforms properties ShowModal=True, and ShowModal=False.

When the ShowModal is set to true, the user form actually displays, but gets itself into a neverending loop and wont quit.
When the ShowModal is set to false, all I get is an outline of the form, but the middle of the box is white and works as normal.

The code is invoked from several different subs as I have different subs that import a different part of the worksheet.

I am using the following code:

Progress.Show
Progress.lblBreakoutNo.Caption = "Currently Importing"
PctDone = BreakoutNo / NoofClaims 'update Progress bar
With Progress
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
Any help would be appreciated.

Thanks

Lightingman2003
 

A:Solved: Excel Macro - Progress Bar - Not Working

I have now managed to solve it. It was user error, forgot to add "DoEvents" after the ProgressBar.

Hope this helps somebody else out though.

Will now mark as "Solved".

Thanks

Lightingman2003
 

Read other 1 answers
RELEVANCY SCORE 71.6

Hi

I started to use VBA recently. I have a macro, which use solver to get some solution. The worksheet contains about 14,000 rows of data and I need to use solver as many times. In the macro, I added Application.ScreenUpdating=False at the beginning and Application.ScreenUpdating=True at the end. But it seems to me that the screen kept updating. I can see the cells changing values one by one. It took more than one and a half hour to finish the macro. I guess the Application.ScreenUpdating=False doesn't really work in my macro. But I couldn't figure out the reason. Any help or suggestions are greatly appreciated.

Since my data is pretty large, I created a "vbtry.xls' and write the macro in a simple manner. But it uses the solver repeatedly. The macro name is "test". As you can see, the screen keeps flashing if you run the macro. My understanding is that: with Application.ScreenUpdating=False, the window should look like freezing and the data should be posted all at once at the end. Am I right on this? What's wrong with my macro?

Please see the attached file. Thanks.
 

A:Solved: EXCEL: Is ScreenUpdating=False working im my macro?

In your example you have not defined SolverSolve, Solverfinish, and SolverOK. When I run the macro the code bugs out. Are you missing some code? Explain how the macro is supposed to work and what these variables or procedures are. Is there an add-in missing from the project?

Regards,
Rollin
 

Read other 3 answers
RELEVANCY SCORE 63.6

I have Excel 2007. Today, (never previously), when I click on a hyperlink to send an email to the subject, I get a Microsoft Office Excel message "This operation has been canceled due to restrictions in effect on this computer. Please contact your system administrator".

To my knowledge I have done nothing to create this. The only remedial action I have tried is a reboot, but the problem persists.

Can anyone offer any solutions please?
 

A:Solved: Hyperlink error in Excel

Read other 6 answers
RELEVANCY SCORE 63.6

Running Excel 2003, I can't create a hyperlink because I am getting this error, "This operation has been canceled due to restrictions in effect on this computer. Please contact your system administrator". Since this is my personal computer what do I need to change to make my hyperlink work?

Thanks,

Upon further research, this is what I did to fix it. : See http://www.slipstick.com/problems/link_restrict.htm#reg2

Edit Registry, Part 2

Start, click Run, type Regedit in the Open box, and then click OK.
Browse to HKEY_CURRENT_USER\Software\Classes\.html
Right click the value for the .html key and select Modify...
Change the value from "ChromeHTML" to "htmlfile" (or from FireFoxHTML to htmlfile)

 

Read other answers
RELEVANCY SCORE 63.6

Hello All,

I was wondering whether any of you can help me. I am using the following hyperlink in excel. When a user clicks on it it open up another excel spreadsheet, in a different area on a shared drive.

here is the link i am using

=HYPERLINK("W:\Licensing\Gambling Act 2005\REGISTERS GA2005\AWP Premises Licence.xls")

Now this work fine. However I was wondering whether or not it is possible to also direct it to a cell or row of cells. I am using Excel 2003. I have tried the actual hyperlink icon and this works until I come to saving the spreadsheet and I get an error to say it cannot save certain elements and when I go back in to it the link no longer works.

If you need anything clarifying, please contact me.

Thanks in advance.
 

A:Solved: Help with excel HYPERLINK or formula

Read other 9 answers
RELEVANCY SCORE 63.6

Trying to make a spread sheet with some weblinks. Some links are very short and will fit on the page, others run off the page onto another one. Is there any secret to shortening the link. It's even too long for landscape
 

A:Solved: Insert hyperlink into Excel

Read other 16 answers
RELEVANCY SCORE 63.6

I need to add a Hyperlink from this formula =IF($D$3="","",VLOOKUP($D$3,Database!$A$2atabase!$Z$199,5,FALSE)). Everything I have tried I have not been able to get it to work. Please help!
 

A:Solved: Excel VLookup Hyperlink

Read other 8 answers
RELEVANCY SCORE 63.6

Hi all,
Using Excel 2010 and Acrobat X.

I have a spreadsheet with a list of names in one column and email addresses in another column. I want to combine them so that the name is displayed and the email address is a hyperlink. I can do this easily by using =hyperlink("mailto:"&a1,b1). The problem is that I then need to convert the document to PDF. The conversion will keep manual links, but not links created with the hyperlink function. I've seen a couple of suggestions such as saving the spreadsheet to HTML and then converting to PDF or a rather ingenious solution to copy the contents of the worksheet and then use Acrobat X to create a PDF from the clipboard. Both of those work, but they lose things like the borders around the cells and the repeating rows at the top of each page.

Any ideas? Thanks.
 

A:Solved: Excel Hyperlink Function to PDF

This should be the links as per Microsoft will create.
Not sure if this is what you are looking for.
 

Read other 2 answers
RELEVANCY SCORE 63.2

First time posting here. I'm somewhat stuck with a rather simple macro. I cannot get the hyperlink that is created with the macro to link back to the sheet I just created. I get a "Reference not valid" error.
Can anyone find any format errors in my hyperlink line? I've tried every combination I've found online but cannot get it to work.
Code:
Sub newFAIRcopy2()
'
' newFAIRcopy2 Macro
'
'
Sheets("FAIR").Select
Sheets("FAIR").Copy Before:=Sheets(2)
mynewsheet = ActiveSheet.Name
Sheets("Sheet1").Select
Selection.Copy
Sheets(mynewsheet).Select
Range("H4:I4").Select
ActiveSheet.Paste link:=True
Sheets("Sheet1").Select
Application.CutCopyMode = False
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=mynewsheet
End Sub
 

A:Solved: Excel VBA Hyperlink to Variable name worksheet

Hi

Try this line to substitute for your last line
Code:
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & mynewsheet & "'!A1", TextToDisplay:=mynewsheet

Because you are linking to a sheet that has a space in the name the hyperlink reference must be enclosed in single quotes. Also, you need to provide a cell reference for the link.

See if this does what you want and if you need any more help please upload the file so we can see what you are copying and pasting and what results you expect to see.
 

Read other 2 answers
RELEVANCY SCORE 62.8

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
 

A: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?
 

Read other 1 answers
RELEVANCY SCORE 62.8

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

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

Read other 7 answers
RELEVANCY SCORE 62.8

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
____________________________
 

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

Read other 7 answers
RELEVANCY SCORE 62.4

[Already posted on Mr Excel but received no replies.]

I've inserted a simple hyperlink in an excel 2003 spreadsheet that links to another excel 2003 workbook. The link works fine and does exactly what it's supposed to do: namely, when I click on it, the target workbook opens.

The minor, though irritating, problem I have is that the hyperlink overrides my custom settings for XP. In the advanced settings of My Computer's properties, I have set the Performance Settings to Custom. There, I have unchecked all the options except 'show windows contents while dragging'. Now, whenever I click on the hyperlink on my worksheet, either XP or Excel overrides my custom settings by checking the 'Animate windows when minimizing and maximizing'. If I go into the custom settings and uncheck the box, it gets re-selected again as soon as I click on the hyperlink.

I can't for the life in me understand why this should happen, as the one does not appear to have any relation to the other. I've tried googling the solution but have come up with nothing. Can anyone tell me what's going on and how I can prevent it from happening?

Thanks.
 

A:Solved: Excel hyperlink overrides XP custom settings: help!

Further to my post. I've since discovered that 'animated windows' only lasts until I switch off my pc. Next time I start it up, the custom settings have reverted to their original settings (i.e., with the animated windows option unchecked). In other words, the way the excel hyperlink overrides my custom settings in XP only lasts for the duration of the current session. Bizarre and pointless.
 

Read other 1 answers
RELEVANCY SCORE 62

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

A: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
 

Read other 1 answers
RELEVANCY SCORE 62

My clients have a macro that automatically generates an email .. however after our recent upgrade to Office 2003 the macro is producing an error on the send mail portion.

Here is the code:

Sub Send_Email()

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim Email1 As String
Dim Email2 As String
Dim Email3 As String
Dim Email4 As String
Dim Email5 As String
Email1 = Range("emailto1")
Email2 = Range("emailto2")
Email3 = Range("emailto3")
Email4 = Range("emailto4")
Email5 = Range("emailto5")
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = Email1 & ";" & Email2 & ";" & Email3 & ";" & Email4 & ";" & Email5
.Subject = "Deal List Update"
.Body = "A transaction requiring special approvals has been entered in the deal list." & _
vbCrLf & vbCrLf & "Trade Date: " & Range("trade_date") & vbCrLf & "Counterparty: " _
& Range("counterparty") & vbCrLf & "Deal Description: " & Range("description")
.Send
End With
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
Set OutMail = Nothing
cleanup:
Set OutApp = Nothing
End Sub

I am hoping it is a simple thing ... really appreciate any assistance.
&n... Read more

A:Excel macro not working after upgrade to 2003

Hi there, welcome to the board!

A couple reasons which may be of issue to you:

1) You will probably need another reference to Outlook 11.0 Object Model (version 2000 was 9.0), or use Late Binding (you are using Early)
2) Your specified ranges are not referenced via worksheet or workbook

So, update the workbook and worksheet in this code and see if it works for you...
Code:
Sub Send_Email()

Dim OutApp As Object ' Outlook.Application
Dim OutMail As Object ' Outlook.MailItem
Dim wb As Workbook, ws As Worksheet
Dim Email1$, Email2$, Email3$, Email4$, Email5$
'// Alter these lines to suit...
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
'//
Email1 = Range("emailto1")
Email2 = Range("emailto2")
Email3 = Range("emailto3")
Email4 = Range("emailto4")
Email5 = Range("emailto5")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0) '(olMailItem)
With OutMail
.To = Email1 & ";" & Email2 & ";" & Email3 & ";" & Email4 & ";" & Email5
.Subject = "Deal List Update"
.Body = "A transaction requiring special approvals has been entered in the deal list." & _
vbCrLf & vbCrLf & "Trade Date: " & ws.Range("trade_date") & vbCrLf & "... Read more

Read other 2 answers
RELEVANCY SCORE 62

Hi, I was coding in VBA and it runs fine there but when I run the same function using a macro (which was working before), it will not do anything. I check that macros are enabled. Does anyone have any ideas as to why the macro button would not work?Hi, I was coding in VBA and it runs fine from there but when I do the same by clicking the macro button (which was working before), it will not do anything. I checked that macros are enabled. Does anyone have any ideas as to why the macro button would not work? Thank you.
- Jeff
 

Read other answers
RELEVANCY SCORE 61.6

Hi,

I'm creating a procedure using Excel. Each cell is used to list an action step and includes reference to a procedure which resides on the business's website. I want to add a hyperlink to the reference text title only.

I can only seem to create a hyperlink for the entire cell, but some of the steps refer to two separate procedures, hence I need to create the link to the text.

PS. I've used Excel, rather than Word, as I'm using formulas to then sort and filter various steps.

Many thanks in advance!
 

A:Solved: Creating a hyperlink for text/icon WITHIN an Excel cell

 

Read other 3 answers
RELEVANCY SCORE 61.6

Hi folks,

Does anyone know how to change the purple default colour for a used hyperlink in Excel 2003?
I have managed to use Format/Style/Modify option to take off the underline and set my font size but would like the actual text not to change clour from blue to purple.
 

A:Solved: Changing visited hyperlink colour in Excel 2003

Make sure you have a hyperlink and a followed hyperlink in the sheet

Format > Style and in the name dropdown choose hyperlink / followed hyperlink and > modify. You should be able to change the colour
 

Read other 2 answers
RELEVANCY SCORE 61.6

I am following instructions, and the link appears in the cell, but when I click on it it only jumps to My Pictures and not to the actual photo jpg. What am I doing wrong?
 

A:Solved: Trouble inserting a hyperlink to a photo in Excel 2003

Read other 9 answers
RELEVANCY SCORE 61.2

Hi,
I have a monthly report I run which outputs single sheet "Invoices" from my workbook that is populated by a months worth of sales. The macro goes through a list of names, pulls the details for that person, populates the pivot table on the invoice,
then saves as both a single excel sheet and a pdf. up until today it has worked fine. the code is, and the error occurs when it reaches the date line. It states "Compile error, can't find project or library":
Sub Save_Sheets_To_New_Books() 'INVOICES
Const strWbPath As String = "D:\Accounts\EA Letters\2020 09 September\"
Dim strDate As String 'todays date
'save the sheets to new books within the active folder and print them
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
strDate = Format(Date, "yyyy.mm.dd")     '(Date, "yyyy.mm.dd")
THIS IS WHERE THE ERROR OCCURS the word DATE is highlighted
ActiveSheet.Copy
Call DeleteNamedRanges
Call PrintAreaAndPasteSpecial
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strWbPath & Name_of_Artist & "_" & strDate & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False         
ActiveWorkbook.SaveAs FileName:=strWbPath & Name_of_Artist & "_&quo... Read more

Read other answers
RELEVANCY SCORE 60

The site was built with MsFrontPage, but I now edit the site using SharePoint Designer. My hyperlink should be opening an Excel file, but instead it's opening a zip file. The type is listed as a Compressed (zipped) folder. How can I fix this issue. Here is the link: http://www.msrevenda.com/HTML Pages/free_lessons.htm / try to download the 2007 Excel Bill Tracker. Thanks in advance for your help.
 

A:Solved: Hyperlink Not Working

Change the program association to Microsoft Excel for the filetype .xls
On Windows 7 you can do that by clicking Start >> Default Programs >> Associate a filetype or protocol with a program
 

Read other 2 answers
RELEVANCY SCORE 59.2

To anyone who can assist with this and teach me something new in the process. I have an issue with the hyperlink not working where I'd like it to work in the attached spreadsheet.

I've named and created a hyperlink below the form attached in cell B196 titled Banned Countries Policy. In the form in cell L73 is where I would like to be able to actually click on the link, strictly for countries that fall on the banned countries list. Currently, the way the formula is set up I get an error stating, "cannot open specified file".

The cells that I'm working with are also highlighted in yellow in the attached spreadsheet. FYI, I'm working in Excel 2010 but needs to be compatible with 2003.Does anyone have any suggestions or remedies to this issue?

Thanks,

Mario
 

A:Solved: Hyperlink not working in form

Read other 6 answers
RELEVANCY SCORE 59.2

I ran a report from a database for a user which opens up to an html page with hyperlinks to pdf files. She she clicks on the hyperlink it attempts to open in IE and will not open. I have gone into file types and made sure pdf docs are to always open in Adobe, and if she clicks directly onto pdf file, the document will open in Adobe with no problem. However, when she clicks on the hyperlink, it still tries to default to IE and won't open. I can't duplicate this problem and it works fine for other computers. Any ideas on how to fix this problem please???
 

A:Solved: Adobe Hyperlink Not Working

Read other 7 answers
RELEVANCY SCORE 58.4

Currently Upgraded From Windows Xp to Vista 64Bit Currently Have sp2.

I did this as I bought this pc and wante'd to upgraded it.
After upgrading i have a folder called Windows.Old

I have currently delete every folder i can Except One " user Data "

Which contains To files

ЃϵϳЅЂϿϽϯІχϯπρϴϱЄϱЃϵϳЅ and

ЃϵϳЅЂϿϽϯІχϯπρЂϻϵЉЃϵϳЅ


Currently have Tried
Disk Clean Up = No Effect
Safe Mode Boot Up and Delete = Same error Code

I renamed the folder now to " lol " no specific reason to try and delete same error message.

" This is no longer Located in C:\Test. Verify The items location.
When tring to removed the selected " LOL " folder containng the two folders.

I did some research/ Alot of research and still have no effect please help me.

A:Currently Upgraded from Xp to Vista 64 Can't Delte Files ЃϵϳЅЂϿϽϯІχϯπρϴϱЄϱЃϵϳЅ

Hi and welcome to TSF this may help http://www.vista4beginners.com/Chang...take-ownership

Read other 4 answers
RELEVANCY SCORE 58

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.
 

A:Solved: Excel Macro

Read other 6 answers
RELEVANCY SCORE 58

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
 

A:Solved: need help with excel macro

Read other 8 answers
RELEVANCY SCORE 58

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.
 

A:Solved: Need Help with Excel Macro

Read other 16 answers
RELEVANCY SCORE 58

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
 

A:Solved: excel macro help please

Read other 9 answers
RELEVANCY SCORE 58

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

A:Solved: Help with excel macro

Read other 9 answers
RELEVANCY SCORE 58

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.
 

A:Solved: Excel Macro?

Read other 14 answers
RELEVANCY SCORE 58

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
 

A: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
 

Read other 2 answers
RELEVANCY SCORE 58

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

A:Solved: Excel macro help

Read other 12 answers
RELEVANCY SCORE 58

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

A:Solved: Excel Macro help

Read other 15 answers
RELEVANCY SCORE 58

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

A:Solved: Need a Macro for Excel

Read other 16 answers
RELEVANCY SCORE 58

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
 

A: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
 

Read other 3 answers