opening a file

tim64 · Jun 20, 2005

I have this code that changes words into hyperlinks, but it can't do it
to other files. So I want the program to be able to open the file it
self and then run the program. I want a message box to pop up to ask
for the file to open, but I want nothing else changed in the code

Code:
--------------------

Sub MakeHyperlink()

ActiveWorksheet.Select
Range("B7").Select
Dim strCellData As Variant

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" & Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("G7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" & Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("N7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" & Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("X7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" & Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("AG7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" & Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("AO7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" & Selection.Value, TextToDisplay:=Selection.Value
Loop

End Sub

anilsolipuram · Jun 20, 2005

Backup your workbooks before executing this macro

It will prompt for file, and then opens the workbook,selects Sheet1,
and apply the links to the opened workbook.

Sub MakeHyperlink()
Dim file_open As Variant
Dim org_workbook As Variant
org_workbook = ActiveWorkbook.Name
file_open = Application.GetOpenFilename()

Workbooks.Open Filename:=file_open
Worksheets("Sheet1").Select
Range("B7").Select
Dim strCellData As Variant

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("G7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("N7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("X7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("AG7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("AO7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

End Sub

End Sub

bhofsetz · Jun 20, 2005

Use GetOpenFilename command built into VBA. You can use this to let the
user select which file the code is going to operate on.
Then use Workbooks.Open to open the selected file.

At the end of your sub use Workbooks.Close

Check the VBA helpfile for more information about using the
GetOpeFilename command.

tim64 · Jun 20, 2005

it doesn't work (see error below )

Sub MakeHyperlink()

Dim file_open As Variant
Dim org_workbook As Variant
org_workbook = ActiveWorkbook.Name
file_open = Application.GetOpenFilename()

Workbooks.Open Filename:=file_open
Range("B7").Select <-------------------------there's a runtime error
here (and mabye in the other range selects)
Dim strCellData As Variant

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("G7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("N7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("X7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("AG7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

Range("AO7").Select

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

End Sub

anilsolipuram · Jun 20, 2005

What is the exact error messag

tim64 · Jun 20, 2005

Run-time error '1004':
Application-defined or Object-defined error

anilsolipuram · Jun 20, 2005

test this , i shortened the macro for testing purpose

Sub MakeHyperlink()

Dim file_open As Variant
Dim org_workbook As Variant
org_workbook = ActiveWorkbook.Name
file_open = Application.GetOpenFilename()

Workbooks.Open Filename:=file_open
Range("g7").Select
Dim strCellData As Variant

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

end sub

tim64 · Jun 20, 2005

it's the same error

Sub MakeHyperlink()

Dim file_open As Variant
Dim org_workbook As Variant
org_workbook = ActiveWorkbook.Name
file_open = Application.GetOpenFilename()

Workbooks.Open Filename:=file_open
Range("g7").Select <----------------------- it errors here
Dim strCellData As Variant

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

anilsolipuram · Jun 20, 2005

Trying testing by opening different excel fil

tim64 · Jun 20, 2005

still does i

anilsolipuram · Jun 20, 2005

let me know what message pops up
Sub MakeHyperlink()

Dim file_open As Variant
Dim org_workbook As Variant
org_workbook = ActiveWorkbook.Name
file_open = Application.GetOpenFilename()

Workbooks.Open Filename:=file_open
msgbox file_open
Range("g7").Select
Dim strCellData As Variant

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

end sub

tim64 · Jun 20, 2005

the first meesage says C:\working\project_test.xls

then theres the error message:

Run-time error '1004':
Application-defined or Object-defined error

anilsolipuram · Jun 21, 2005

I am not sure what is the error, it is working good for me.

I added one more debug, popups up the g7 cell value, let me know

Sub MakeHyperlink()

Dim file_open As Variant
Dim org_workbook As Variant
org_workbook = ActiveWorkbook.Name
file_open = Application.GetOpenFilename()

Workbooks.Open Filename:=file_open
msgbox file_open
msgbox Range("g7").value
Range("g7").Select
Dim strCellData As Variant

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

end sub

tim64 · Jun 21, 2005

it still doesn't work, and the new MsgBox is blank with an ok bo

anilsolipuram · Jun 21, 2005

I am not siure why command for selecting cell is not working, use
alternate selecting method like cells(7,7).select (which is selectin
g7)

try this macro now

Sub MakeHyperlink()

Dim file_open As Variant
Dim org_workbook As Variant
org_workbook = ActiveWorkbook.Name
file_open = Application.GetOpenFilename()

Workbooks.Open Filename:=file_open
msgbox file_open
msgbox cells(7,7).value
cells(7,7).select
Dim strCellData As Variant

Do Until ActiveCell.Value = ""

strCellData = ActiveCell.Value
ActiveCell.Value = strCellData
ActiveCell.Offset(1, 0).Select

'ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'0'!A1", TextToDisplay:="www.puritan.com"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http:\\" &
Selection.Value, TextToDisplay:=Selection.Value
Loop

end sub

opening a folder then creating links	15	Jun 29, 2005
Insert Hyperlink	4	Jan 9, 2006
Hyperlink macro for personal.xls	5	Dec 8, 2005
hyperlinking to charts in file	1	Mar 11, 2013
Hyperlinking cells and macros	2	Feb 1, 2008
Use Contents of a Cell to Create a Hyperlink	1	Jan 8, 2008
Opening New Work with Wedpage Content when address is in original workbook	4	Dec 1, 2008
Assigning a Hyperlink to A Cell that References a Worksheet	2	May 16, 2007

opening a file

tim64

anilsolipuram

bhofsetz

tim64

anilsolipuram

tim64

anilsolipuram

tim64

anilsolipuram

tim64

anilsolipuram

tim64

anilsolipuram

tim64

anilsolipuram

Ask a Question

Similar Threads