How to combine different worksheet in one master worktsheet in special condition?

[makhtoot]

Hello Dear Friends
I have an HTML file Listing of my Hard disk files that has a link to the files with no drive letter in the beginning of the addresses and as i click on the name of the file name in the html file, it opens the file. since this listing is in a column format and has different information of a file in various columns (like size, date, name, type, etc) i imported the file in an excel 2010 worksheet to use the filtering ability of the excel to better handle my list and find specific files faster and easier. allegedly each drive has it's own HTML list file and its has been placed in the root of the drive and since there is no drive letter in the addresses of each file in the HTML file, it will open the targeted file as i click on its name in the HTML and it has nothing to do with the drive letter. and as i opened the HTML file in the excel, it will import all the links as the hyperlinks and they will act same as the HTML file and it should be in the drive root too. the question is, how can i combine these excel files that are in each drive, in one master worksheet that it uses the files from its source, somehow embed it inside one file but i need to have access to all the filtering abilities of all the file at the same time, and the reason that i need the files to be accessed from their sources is because of this fact that if i combine them all in one worksheet regularly, it will save it in that single file and so all the linkings will be disordered because it doesn't know where to look at.
sorry for the long long question, but i'll be very appreciated if anyone could help me out here.
thanks in advance

 

[makhtoot]

anything

 

[cgcamal]

Hi makhtoot,

May you show a sample of how is your input data and the output desired. You may use Mr Excel HTML maker to show your samples.


I understand you need to combine many links in a single worksheet.

How do you get the path lists for each drive? with Dir in DOS or with another application?

Best regards

 

[makhtoot]

Thanks for kind consideration
as i described above, the sheets are not a big deal themselves but the problem is this:
assume i have different excel files in different drive routs like:

c:\1.xlsx
d:\2.xlsx
e:\3.xlsx
...

and the hyperlinks in each excel file look like this:

"Test\old\0152.JPG"

that opens the given file. as you see no drive letter in the beginning. if i combine them all together and save them it will give me a single file like for example: final.xlsx
and as you see above, as there is no drive letter in front of the pathes, it will assumes that the file exist in the drive that the final.xls file exist.
I want the final XLSX file to read the excel files from each drive seperately while embed them all in itself for filtering purposes.
and by the way, i generated the files list of each drive with list generator softwares and saved them as html with links to each file as you've seen above.
thank you for your help

 

[cgcamal]

Hi makhtoot,

I'm not sure if is close what you want, but and idea could be to copy all hyperlinks from all excel files into
a single sheet in a new file. If it is necessary the hyperlink address will change with including the correct drive.
(The hyperlink must have now correct the hyperlink address, because you say it open correctly the files).

It could be done in that way but for me at least is needed to have some samples to work. How is the
estructure of the sheets within eash file etc

Would be nice for anybody help you to upload samples of each xls file containing the links.

Regards

 

[makhtoot]

Hello dear friend
Thank you for your notification and excuse me that i couldn't response sooner. here is a sample of the designated excel file along with the html file:

http://www.mediafire.com/?sxcclq9p0sq0rq9

as you see, there is no driver letter in front of the addresses and it will be changed based on the drive that you place it in. so, assume that we have 5 of these files in 5 different drives. I'll be waiting for your helpful answer.
thanks in advance

 

[makhtoot]

any news my friends?

 

[cgcamal]

Hi makhtoot,

In a copy of your files execute the below code in each file within their corresponding drive. Once you
do that, join the links in each workbook in a single sheet.

The code below adds the drive letter to each hyperlinks. I think could be work.

Sub Add_Drive_Letter_To_Hyperlinks()
Dim Curr_Drive As String, Lr As Integer, Curr_Address As String

Curr_Drive = Left(ActiveWorkbook.Path, 3)
Lr = Range("A" & Rows.Count).End(xlUp).Row

With ActiveSheet
    For i = 4 To Lr
        Curr_Address = .Cells(i, 1).Hyperlinks(1).Address
        .Cells(i, 1).Hyperlinks(1).Address = Curr_Drive & Curr_Address
    Next
End With
End Sub

Hope this helps.

Regards

 

[makhtoot]

Thank you My Friend
the code you posted worked, but i considered to do this with no drive letter. i can add drive letter while making my html list, the problem is as i mentioned, the files that i'm taking a list from are in seperate hard disks and i can't combine the lists regulary. either i should embed them as an object in excel that is no good or have to do the way you showed me and would be useless too. i found a way to combine them in access, but there are two major issue with that. one that all the hyperlink won't work as i import the excel file in the access that this problem has been solved somehow, the other is that when i try to connect to of the list with a query, nevertheless that i have my hyperlinks all working, in the query it won't bring them in account thus there won't be any hyperlink.
hope that i could clear my point
than you big time