Create Links Reference

Lenard · Oct 4, 2011

Hi,

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5Conglt%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5Conglt%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5Conglt%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style>[FONT=&quot]Based on the following codes, it took a long time to fill up about 350 cells ( ie using "a" cell in the helper column ) in a Summary worksheet by creating the link reference instead of cell value from a closed workbook ( ie without opening the excel file )[/FONT].
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5Conglt%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5Conglt%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5Conglt%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style>[FONT=&quot][/FONT]
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CUsers%5Conglt%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CUsers%5Conglt%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CUsers%5Conglt%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page WordSection1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.WordSection1 {page:WordSection1;} --> </style>[FONT=&quot]Is there a better or faster codes that can help to accomplish the above purpose[/FONT] ?

<table style="border-collapse: collapse; width: 49pt;" width="65" border="0" cellpadding="0" cellspacing="0"><col style="width: 49pt;" width="65"> <tbody><tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt; width: 49pt;" width="65" height="19">Dim wb As Workbook</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> Dim TrgtFile As String</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> Dim directory As String</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> Dim fileName As String</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> Dim sheetName As String</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> Dim rNA As Range</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> Dim iLoop, i, j As Integer</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19">Set wb = ThisWorkbook</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> TrgtFile = wb.Sheets(1).Range("F11")</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> directory = wb.Sheets(1).Range("F18")</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> fileName = wb.Sheets(1).Range("F20")</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> sheetName = wb.Sheets(1).Range("J11")</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19">
</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19">With Workbooks(TrgtFile)</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> Workbooks(TrgtFile).Activate</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> .Sheets(sheetName).Select</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> .Sheets(sheetName).Columns("A").EntireColumn.Hidden = False</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> </td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> iLoop = WorksheetFunction.CountIf(.Sheets(sheetName).Columns(1), "a")</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> Set rNA = .Sheets(sheetName).Range("A1")</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> 'look for all the A's, don't start with 3.</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> For i = 1 To iLoop</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> Set rNA = .Sheets(sheetName).Columns(1).Find(What:="a", After:=rNA, _</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> SearchDirection:=xlNext, MatchCase:=True)</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> For j = 1 To 12 'columns</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> rNA.Offset(0, j + 1).Formula = rNA.Offset(0, j + 1).Formula & "+'" & directory & _</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> "[" & fileName & "]" _</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> & sheetName & "'!" & rNA.Offset(0, j + 1).Address(False, False)</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> Next j</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> Next i</td> </tr> <tr style="height: 14.4pt;" height="19"> <td style="height: 14.4pt;" height="19"> End With</td> </tr> </tbody></table>
Any help will be appreciated and thanks in advance

Regards
Len

Lenard · Oct 5, 2011

Hi,

Sorry, text not clear on first paragraph

Now, resend the text

Based on the following codes, it took a long time to fill up about 350 cells ( ie using "a" cell in the helper column ) in a Summary worksheet by creating the link reference instead of cell value from a closed workbook ( ie without opening the excel file ).
Is there a better or faster codes that can accomplish the above purpose ?

Any helps, please ?

Thanks & Regards
Len

Create Links Reference

Lenard

New Member

Excel Facts

Lenard

New Member

Similar threads

Forum statistics

Share this page

Create Links Reference

Lenard

New Member

Excel Facts

Lenard

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock