PDA

View Full Version : Solved: macro to open all hyperlink files and save as different sheets in the workbook



aravindhan_3
10-23-2008, 05:23 AM
Hi,

I have some hyper links in Sheet 1 Column a
like
A
B
C
D
E
etc.,
When I click A, a csv file opens, when i clikc B, another csv file opens.

what I need is,
is there a macro which opens all the hyperlink files and saves them as A,B C etc..in the same file as different sheets.

Thanks in advance for the help?..

GTO
10-23-2008, 11:57 AM
Greetings aravindhan_3,

Well... after just being reminded of some rather delirious code I provided, you may wish to stand away from the computer when running first time, but...

The below assumes a standard three-sheet workbook to begin with, so of course adjust as necessary.

Option Explicit
Sub ImportHyperlinkedFiles()
Dim hlnk As Hyperlink, _
shtCSV As Worksheet, _
wbCSV As Workbook, _
intCount As Integer

'// Change "Sheet1" to the codename of the sheet that has your hyperlinks //
For Each hlnk In Sheet1.Hyperlinks
'//optional, see farther down. //
intCount = intCount + 1
hlnk.Follow NewWindow:=False, AddHistory:=False
Set shtCSV = ActiveSheet
Set wbCSV = shtCSV.Parent
'// Change "ThisWorkbook.Sheets.Count - 2" to where you want the sheet //
'// inserted. This example places the sheets in order starting after //
'// sheet(1) in a standard three sheet wb //
shtCSV.Copy Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count - 1)
wbCSV.Close False
'//optional in case you need to rename imported sheets. //
Set shtCSV = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count - 2)
shtCSV.Name = "Imported CSV_" & intCount
Next

Set shtCSV = Nothing
Set wbCSV = Nothing
End Sub

Hope that helps,

Mark

aravindhan_3
10-24-2008, 02:16 AM
Hello GTO great.. its working superb..... you are a genius..

I have a quick question on renaming part, Is there a way to rename the sheets as the text that display for hyperlinks
that ks
in sheet 1 Column A2 = A - when i click this the file opens
in sheet 1 Column A3 = B - when i clikc B the file opens
your macro is wokring wondefull as of now.. I just want to change the file name as A, B C etc.,..

thanks a lot once agian.. for your wonderfull code..

GTO
10-24-2008, 06:17 AM
You mean like(?):

Option Explicit
Sub ImportHyperlinkedFiles()
Dim _
hlnk As Hyperlink, _
shtCSV As Worksheet, _
wbCSV As Workbook
'// Change "Sheet1" to the codename of the sheet that has your hyperlinks //
For Each hlnk In Sheet1.Hyperlinks

hlnk.Follow NewWindow:=False, AddHistory:=False
Set shtCSV = ActiveSheet
Set wbCSV = shtCSV.Parent
'// Change "ThisWorkbook.Sheets.Count - 2" to where you want the sheet //
'// inserted. This example places the sheets in order starting after //
'// sheet(1) in a standard three sheet wb //
shtCSV.Copy Before:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count - 1)
wbCSV.Close False

'//optional in case you need to rename imported sheets. //
Set shtCSV = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count - 2)
shtCSV.Name = hlnk.Name
Next

Set shtCSV = Nothing
Set wbCSV = Nothing
End Sub

aravindhan_3
10-29-2008, 12:03 AM
Hi,

its perfect.. thanks a lot buddy...

GTO
10-29-2008, 01:03 AM
Hi aravindhan_3,

Thank you for marking it solved, and of course, happy to help.

Mark