Free Microsoft Excel 2013 Quick Reference

Use VBA in Excel to Open Word Doc and Create a list of Hyperlinks

I would like to use a routine in Excel to open a Word document from a list
and create a list of the hyperlinks in the document(s).

I can open the word document but I cannot extract the desired hyperlink
information.

Can anyone tell me what is wrong with this code or this methodology?

Sub OpenWordDoc()

Dim WordObj As Object
Dim Fpath As String
Dim LinksList() As Variant
Dim aHyperlink As Hyperlink

Err.Clear
On Error Resume Next

ReDim LinksList(4, 2)

' Set file path
Fpath = "C:Documents and SettingsUserRoutinesListHyperlinksTest.doc"

' Open the word document
Set WordObj = CreateObject("Word.Application")
WordObj.Documents.Open (Fpath)
WordObj.Visible = True

' This part works in Word but does not work in Excel
i = 0
For Each aHyperlink In ActiveDocument.Hyperlinks
i = i + 1
LinksList(i, 1) = aHyperlink.TextToDisplay
LinksList(i, 2) = aHyperlink.Address
Next aHyperlink
' End of section that works in Word but does not work in Excel

' Close the file
WordObj.Documents.Close
' Quit Word
WordObj.Quit

End Sub

Thanks in advance,
Raul


Post your answer or comment

comments powered by Disqus
Hello,

I was hoping someone would be able to help me with this formula since we are now using excel 2010 the With Application.FileSearch no longer exists and I am not sure on how to fix this formula now.


	VB:
	
 CREATEPDFDIRECTORY() 
    Dim path As String 
    Dim f As Integer 
    path = "INSERT FILEPATH HERE!" 
    f = 1 
     
    With Application.FileSearch 
        .LookIn = path 
        .FileType = msoFileTypeAllFiles 
        .Execute 
        For i = 1 To .FoundFiles.Count 
            If UCase(Right(.FoundFiles(i), 3)) = "PDF" Then 
                Range("A" & f).Value = .FoundFiles(i) 
                ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & f), _ 
                Address:=.FoundFiles(i), TextToDisplay:=Split(Dir(.FoundFiles(i)), ".")(0) 
                f = f + 1 
            End If 
        Next i 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This formula used to create a list of hyperlinks from a folder (pdf format)

Thanks

Office 2003

I've got a file thats created in excell which is to be loaded to a mainframe as a text file (tab delimited), but due to how excel sees the data it put speech marks in the file, i've created a macro within a word document to open the text file and remove them.

I've done some googling and I know that you can set an macro in excel to open a word doc and excute a macro from within it or have excel open a word document and manipulate it from within the excel macro.

Anyhow i've got as far as below but i keep getting debug errors on the lines in red. can anyone help

Sub manip()
Dim wdApp As Word.Application
Set wdApp = New Word.Application
wdApp.Visible = True
wdApp.Documents.Open Filename:="P:IBM Calendar.doc"
With wdDoc
ChangeFileOpenDirectory "P:"
Documents.Open Filename:="calpot.txt", ConfirmConversions:=False, AddToRecentFiles:=False, Format:=wdOpenFormatAuto
Windows("calpot").Activate
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = """"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End With
ActiveDocument.save
ActiveWindow.Close
Set wdApp = Nothing
End Sub

Im trying to figure out how to email in excel using VBA. I can get it to work but what im using is limited. I would like to email an attachment to a large amount of people, have a CC line, subject, and body. The email addresses change each time i run my program. Some are added and some are dropped so the people i email this spreadsheet to change. Is there anyway to create a string of addresses and use that string on the To: line somehow?

Hi,

I have mergerd Word document into the excel cell (by Insert....)
as an link.
but Word doc stored on my local computer. I want to send
excel file to somebody else but he is not able to open word doc
from his computer.
So anybody help me out How to acess such kind of document from out
side the network.

Thank you

How to send post data using VBA in Excel 2007 with WinHTTP?
How can I send multiple post fields?

Hi: First thing to metion is that the result field I expect is a Memo Field
When I run the following (qry*)query in access I do get the expected one (row*)row as the result-However when I use this (modl*)code into an Excel VBA module-nothing happens- I tested for recorcound and it does show -1. Just wondering why I Am not getting the results when using VBA in Excel-Thanks in advance
****** IN ACCESS THIS WORKS FINE **********
(qry*)
SELECT Periods_Desc FROM FCST_Template_SGA GROUP BY Pull_Date, Template_Name, Periods_Desc HAVING Template_Name="AFTRHRS";
(row*)
JAN 08 ACTUAL-FEB 08 ACTUAL-MAR 08 ACTUAL-APR 08 ACTUAL-MAY 08 ACTUAL-JUN 08 ACTUAL-JUL 08 ACTUAL-AUG 08 ACTUAL-SEP 08 ACTUAL-OCT 08 6+6 FCST-NOV 08 6+6 FCST-DEC 08 6+6 FCST-
******HOWEVER IN EXCEL IT DOES NOT DISPLAY ANYTHING ****
(modl*)
Private Const cDir_Database As String = "C:Documents and SettingsBLABLAMRA_Database.mdb"

Public DB_Conn As ADODB.Connection 'Access connection
Public DB_RSet As ADODB.Recordset 'Access Record Set
Public DB_SQL As String 'SQL Commands
'======
Private Sub UserForm_Initialize()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Set wbBook = ActiveWorkbook
Set wsSheet = wbBook.Worksheets("SGA")
OPEN_DATABASE
'*** HEADER
DB_SQL = "SELECT Periods_Desc FROM FCST_Template_SGA GROUP BY Pull_Date, Template_Name, Periods_Desc HAVING Template_Name='" & "AFTRHRS_SF_UBH" & "';"
With DB_RSet
Application.StatusBar = "Checking Valid Source Codes..."
.Open DB_SQL, _
DB_Conn, _
adOpenForwardOnly, _
adLockReadOnly 'Open the list of Source Codes
MsgBox CStr(.RecordCount)

wsSheet.Range("A1").CopyFromRecordset DB_RSet
.Close
End With
CLOSE_DATABASE
End Sub

'===
Public Sub OPEN_DATABASE()
Application.StatusBar = "Connecting to the Database..."
Application.Cursor = xlWait
Set DB_Conn = New Connection 'Database Connection
Set DB_RSet = New Recordset 'Database RecordSet
DB_Conn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & cDir_Database
DB_Conn.Open
DB_Conn.BeginTrans

Application.StatusBar = False
End Sub
'=====
Public Sub CLOSE_DATABASE()
Application.StatusBar = "Disconnecting from the Database..."
DB_Conn.CommitTrans 'Commit All Work
DB_Conn.Close 'Close the connection
Set DB_RSet = Nothing

Application.Cursor = xlDefault
Application.StatusBar = False
End Sub

hi guys once again
so again i have question which might be very simple but for me its difficult so can any body tell me that how i create a new worksheet using VBA in Excel 2003
waiting for urgent response
wiz

I want to have a list of hyperlinks, one on each line in Word. I have the links as text and need to apply actual links to them, but there are too many to do manually.

I wrote an excel VBA script that does it in excel, but I can not figure out how to copy the links out into word without putting them in a table or losing the actual links. I either need to figure out how to do this or figure out how to convert the script for word.

I have never worked with word VBA, but I imagine the script would not be that hard, I just need to take every line and apply a link to it with the address based on the actual text. There will be nothing in the word file but link addresses and carriage returns.

Does anyone know how to do this either way?
Thanks

I am using excel 2003 to count my inventory in the following manor:

I have a "database sheet" that is comprised of possible inventory SKU numbers.

I have a "scanned sheet" that lists all the SKU's that I have scanned in the warehouse

I have a "count sheet" that counts the number of times each SKU in the database appears in the "scanned sheet"

Question 1: Am I going about this in the most efficient manor?

Question 2: Frequently, new Items appear in the warehouse that are not yet in my database. Is it possible to create a list of SKU numbers that do not have a match in the database? As it stands, any SKU numbers that are scanned but not already listed in my database - simply do not get counted.

Thank you,

-Bob

Hello All:

I have the following spreadsheet:

A----------- B----------------- C
Invoice------ Product Code------Classification
82001733----LX+150SVL0044----9010.90.9000
-------------SE+68763001-------8501.31.4000
-------------GJ+10005-----------
-------------SE+59163-----------8504.40.9580
-------------EB+98575-----------
-------------KF+MX09300--------9010.90.9000

I'd like to extract the rows with blank cells in Column C and product codes in Column B and create a list of product codes which don't have classifications on a separate list. This new list would need the ability to be revised as other codes will be added once a macro is run.

Can anyone help with writing code for this would be appreciated.

I need to create a list of worksheet names (tabs) or all the files (I'd
like the file names as well, but think I've already found the add in
from http://www.tushar-mehta.com/excel/software/index.html that does
that part)

the purpose is to create a list - from which I will use concatenate to
make formulas linking to all the worksheets (they are all formated the
same) so I can summarize financial information. (I use concatenate)
make massive page of formulas and past special values to another page
(creating text that upon adding an = sign in front will become
formuals) I've found this works well on pages with 8,000 or so
formulas.

so in the end I'd like to have at least a file name and all sheet names
for that file - I can add the drive and folder and any formula specific
formatting like "[" etc

D:FY05.Mar[Excel.Filename.xls]sheet1'

D:FY05.Mar[Excel.Filename.xls]sheet2'
D:FY05.Mar[Excel.Filename.xls]sheet3'
D:FY05.Mar[Excel.Filename.xls]sheet4'
D:FY05.Mar[Excel.Filename2.xls]sheet1'
D:FY05.Mar[Excel.Filename2.xls]sheet2'

thanks, Drew

I haven't used buttons much.
I would like to create a list of projects in column A.
Then the user would select one of the projects. This can be done by clicking on the corresponding cell in column B. So I would lkie to see buttons in column B for this. OR if someone can suggest another way, e.g. highlighting the project name in column A or putting an x in corresponding cell in column B.
I can make this last scenerio work, but I don't know how to assure that only one project can be selected at a time.
In other words if I have selected project in ROW 12 and then I want to see project in ROW 17, I should be able to select 17. And 12 should be deselected automatically.
Hope its clear enough to understand what I want.
Thanks,
modytrane

Okay that title doesn't make that much sense.

What I am tring to do is create a list of numbers from a corresponding cell where a value matches the given value.

So we have a table of numbers and peoples names, from this I want to create a list in a single field of the numbers currently assigned to each person, e.g.

Number Name
1 Mark
2 John
3 Mark
4 Mark
5 John

The desired output will be
Name Assigned
Mark 1, 3, 4
John 2, 4

I thought that by using an array and a concatenate function that I might be able to do this, but I am struggling at the moment, perhaps I will have to use VBA...

Any tips would be great...

Thanks,

Martin

I need to create a list of worksheet names (tabs) or all the files (I'd
like the file names as well, but think I've already found the add in
from http://www.tushar-mehta.com/excel/software/index.html that does
that part)

the purpose is to create a list - from which I will use concatenate to
make formulas linking to all the worksheets (they are all formated the
same) so I can summarize financial information. (I use concatenate)
make massive page of formulas and past special values to another page
(creating text that upon adding an = sign in front will become
formuals) I've found this works well on pages with 8,000 or so
formulas.

so in the end I'd like to have at least a file name and all sheet names
for that file - I can add the drive and folder and any formula specific
formatting like "[" etc

D:FY05.Mar[Excel.Filename.xls]sheet1'

D:FY05.Mar[Excel.Filename.xls]sheet2'
D:FY05.Mar[Excel.Filename.xls]sheet3'
D:FY05.Mar[Excel.Filename.xls]sheet4'
D:FY05.Mar[Excel.Filename2.xls]sheet1'
D:FY05.Mar[Excel.Filename2.xls]sheet2'

thanks, Drew

I need to create a list of production figures for different facilities from a folder saved on the common server.

The folder is titled 'Daily Production Reports' and it has three subfolders titled 'CPF' , 'Manzalai' and 'Makori'. Within each subfolders are folders for the year 2009, 2010 and 2011 which once opened has 12 folders for each month of the year. In each of these folders are the workbooks that contain daily production values for each day of the month separately.

I made a macro to pick up values for the 1st of Jan 2011 from each of these folders for product A and B.
The resulted row has the data in the following order:

1-Jan-2011, CPF Product A value, CPF Product B value, MGP Product A value, MGP Product B value, Makori Product A value, Makori Product B value.

(the comma inserted above is differentiating each cell in the row just for reference)

The macro goes into each of the respective folders and picks up both product values for each facility and inserts it into the same row.

Each row value has the following route:
Row 1 column 1,2,3:Daily Production Reports>CPF>2011>January>DPR_CPF_01_01_2011

Within this sheet, the date is in cell reference: J21
CPF Product A value is in cell: T31
CPF Product B value is in cell:T38

Row 1 column 4,5:
Daily Production Reports>Manzalai>2011>January>DPR_MGP_01_01_2011

Within this sheet, the Manzalai (MGP) Product A value is in cell: R29
Manzalai(MGP) Product B value is in cell: R33

Row 1 column 6, 7:
Daily Production Reports>Makori>2011>January>DPR_Mak_01_01_2011

Within this sheet, the Makori Product A value is in cell: R29
Makori Product B value is in cell: R33

This is the route for the year 2011 and month Jan, I need to do this now for all the months and dates in the 3 years 2009, 2010 and 2011 which would be listed down in order.
For all the other files though the name of the month and year changes but the cell reference remains the same for the values.

The macro I've made so far is as follows:

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            
            .LookIn = "datacenterCommonProduction ReportsProductionDaily Production ReportsDaily Produciton
Reports"
            .FileType = msoFileTypeExcelWorkbooks
            
            
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                        Workbooks.Open Filename:= _
        "datacenterCommonProduction ReportsProductionDaily Production ReportsDaily Produciton ReportsCPF2011January
2011DPR_CPF_01_01_2011.xls" _
        , UpdateLinks:=0
    ActiveCell.Offset(-107, 0).Range("A1").Select
    Selection.Copy
    Windows("macro.xlsm").Activate
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1").Select
    Windows("DPR_CPF_01_01_2011.xls").Activate
    Sheets("Partners Report ").Select
    ActiveWindow.SmallScroll Down:=-30
    ActiveCell.Offset(23, -2).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Windows("DPR_CPF_01_01_2011.xls").Activate
    ActiveCell.Offset(7, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Windows("DPR_CPF_01_01_2011.xls").Activate
    ActiveWindow.Close
    Workbooks.Open Filename:= _
        "datacenterCommonProduction ReportsProductionDaily Production ReportsDaily Produciton ReportsMGPDaily Production
Reports(Manzalai)2011January 2011DPR_MGP 01.01.2011.xls"
    Sheets("Partners Report ").Select
    ActiveWindow.SmallScroll Down:=-24
    ActiveCell.Offset(-31, 1).Range("A1").Select
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Windows("DPR_MGP 01.01.2011.xls").Activate
    ActiveCell.Offset(4, -1).Range("A1:B1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Windows("DPR_MGP 01.01.2011.xls").Activate
    ActiveWindow.Close
    Workbooks.Open Filename:= _
        "datacenterCommonProduction ReportsProductionDaily Production ReportsDaily Produciton ReportsMakori-EPFMakori
EPF Daily ReportsDaily Production Reports2011January 2011DPR_MAK_01.01.2011.xls"
    Sheets("Partners Report").Select
    ActiveWindow.SmallScroll Down:=-21
    ActiveCell.Offset(-18, -5).Range("A1").Select
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Windows("DPR_MAK_01.01.2011.xls").Activate
    ActiveCell.Offset(-7, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("DPR_MAK_01.01.2011.xls").Activate
    ActiveWindow.SmallScroll Down:=-6
    ActiveCell.Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("DPR_MAK_01.01.2011.xls").Activate
    ActiveCell.Offset(1, 4).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = ""
    ActiveCell.Offset(-1, -4).Range("A1").Select
    Selection.Copy
    Windows("macro.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, -1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Offset(0, -6).Range("A1:G1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.Offset(1, 0).Range("A1").Select
    Windows("DPR_MAK_01.01.2011.xls").Activate
    ActiveWindow.Close
    Next lCount
                End If
        End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
But this macro only picks up values for that one specific date. To do the same for all the files in the Daily Production Reports folder I either need to make the macro more generic? But how is that done? I'm not good with macros and this is my very first time. If there is any other solution to my problem I would be very grateful for your help.

Plus when a solution is given please explain the procedure in detail as Im an amateur in the field.

Hi, my question is pretty simple.

i just need to know how to i create a list of numbers in Column B range from B10 to B200.

i will key in a number in A1, lets say 50
once i key 50 in "A1"
excel will loop and input numbers to B1 to B200
from 1 to 50 and it will stop once it reach 50.
eg.

B1 will show 1
b2 will show 2
b3 will show 3
up to 50 and will stop there

can it be done, please advise. im quite new in the loop function

How do I create a list of file names within a folder in Microsoft Excel.

I was wondering if this can even be done in excel. I need to create a list of workers who are available or possibly available to work from master schedule. The list should include their name, job title and start and end of availability. Availability is determined according to a color (could be changed to a letter or number) and date on the master sheet.

I am creating a list of keywords for an advertising campaign and I was wondering if it is possible to insert a word such as cheap prior to a list of words. I have created a list of words in column A and would like to insert a word like "cheap" in front of each one of those words in column B. Thanks for any help.

I have 4 non-adjacent columns of numeric data. I would like to create a
list of each distinct entry that appears in those 4 columns. I would like to
produce something similar to that which appears in the dropdown menu after
you have applied a filter to a column [after (All) (Top 10…) (Custom…), and
before (Blanks) (NonBlanks)], but as applied to all 4 columns, and have it
appear as actual data I can enter into a separate column. How would you
suggest I proceed?

Mick

Hi

Can anyone please help me compare two columns of text to come up with a
list of items that are in both lists

Many thanks

I have a matrix, 6 columns x 2 rows, which contains letter codes. Example:

A C E R Q L
B D K S P N

Each column is a choice; that is, either A or B; either C or D, etc.

I need to create a list of all possible permutations.
There are 64 permutations, e.g.
ACERQL
ACERQN
ACERPL
ACERPN
etc.

The letters in my 12 cells sometimes change based on formulas elsewhere in
the document, so the list needs to change with them.

To make things even more complicated, some columns will sometimes have THREE
choices in them instead of two (again, changing based on formulas), so the
ideal answer would enable me to deal with those cases automatically.

I would like to create a list of the data in a column. For example: the data
in cell A1 is 92, the data in cell A2 is 76, the data in cell A3 is 112, etc.
I would like to create a formula that would list the data from the A column
into the formula cell (B1) like this: 1234, 1235, 1236. I need to have a list
of all the values in the column so that I can copy and paste it into another
program. Any help would be greatly appreciated.

A B
1 92 92, 76, 112, 32, 85
2 76
3 112
4 32
5 85

Hi All,

I have a spreadsheet that has a list of hyperlinks that are required to be saved in a designated folder. Also, I need to save these webpages in numerical order. I already have 4000 pages saved and are in order from 1 to 4000. I have many more pages to save and trying to locate a faster way to accomplish this task. These files are to saved in basic HTML format only...no pics or JS required.

Can any one provide some assistance on it please?

Thanks,
CS


No luck finding an answer? You could always try Google.