Excel/EXCEL 2010

Advertisement


Question
SCHEMA _EXCEL 2010
SCHEMA _EXCEL 2010  
Dear sir,i come forward with the same issue as before with a SCHEMA attached.
Create a workbook G from another workbook E with hyperlink.

Please advise.

Thanks

Answer
solay,

You originally posted:
A:  D:\TMC.TX MAIN ACTIVITIES 2015
B:  D:\TMC.TX MAIN ACTIVITIES 2015\TMC.TX ACTIVITIES 2015
E:  FEB2015 TMC.TX ACTIVITIES   Since this will contain a macro, it must be named
FEB2015 TMC.TX ACTIVITIES.xlsm

C:  D:\TMC.TX MAIN ACTIVITIES 2015\TMC.TX E-FAULT DOCKET 2015
F:  TT15013001.xlsx   (I assumed the .xlsx)

So I put this code in FEB2015 TMC.TX ACTIVITIES.xlsm  by right clicking on the tab named Sheet1.   In the resulting code module I put in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
If Len(Trim(Target.Value)) <> 8 Then Exit Sub
sName = Trim(Target.Value)
sSourcePath = "D:\TMC.TX MAIN ACTIVITIES 2015\TMC.TX E-FAULT DOCKET 2015\"
sSourceName = "TT15013001.xlsx"
sDestPath = "D:\TMC.TX MAIN ACTIVITIES 2015\TMC.TX E-FAULT DOCKET 2015\"
sDestName = sName & ".xlsx"
Workbooks.Open SourcePath & sSourceName
ActiveWorkbook.Worksheets(1).Name = "Sheet1"
ActiveWorkbook.SaveAs sDestPath & sDestName
ActiveWorkbook.Close SaveChanges:=False
For Each hlnk In Me.Hyperlinks
 hlnk.Delete
Next
Me.Hyperlinks.Add Anchor:=Target, _
  Address:=sDestPath & sDestName, _
  SubAddress:="Sheet1!A1", _
  TextToDisplay:=sName
End Sub

when I put an 8 character string in cell A1 of Sheet1 of FEB2015 TMC.TX ACTIVITIES.xlsm

the code creates a new workbook in D:\TMC.TX MAIN ACTIVITIES 2015\TMC.TX E-FAULT DOCKET 2015 by opening  TT15013001.xlsx and saving it in the same path with a name of the 8 character string in A1 of Sheet1 of FEB2015 TMC.TX ACTIVITIES.xlsm and then create a hyperlink in cell A1 of sheet1 of FEB2015 TMC.TX ACTIVITIES.xlsm that opens the new workbook when clicked.  The hyperlink is to Sheet1, column A row 1 (A1) so I have assumed that TT15013001 has a worksheet named Sheet1.   

I believe that is what you asked for.  (Also, in your first request you said nothing about creating a hyperlink to the workbook).

Given the above description, I have tested this code and it worked fine for me.  So if it doesn't work for you, you will have to make any changed required.  (such as my assumption about the TT15013001 having .xlsx extension.  

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.