Typical Updated Data File, "Raghu.xlsx" after first consolidation
Checked Library Infomation Excel 2003 Excel 2007 Excel 2010
Some sample data for other Posts and Threads:
http://www.eileenslounge.com/viewtopic.php?f=30&t=29652
Using this code:
Code:
Sub Its() ' snb 2017
Dim It As Variant
For Each It In ThisWorkbook.VBProject.References
Dim strIts As String
Let strIts = strIts & "Description:" & vbTab & It.Description & vbCr & "Name:" & vbTab & vbTab & It.Name & vbCr & "Buitin:" & vbTab & vbTab & It.BuiltIn & vbCr & "Minor:" & vbTab & vbTab & It.minor & vbCr & "Major:" & vbTab & vbTab & It.major & vbCr & "FullPath:" & vbTab & vbTab & It.fullpath & vbCr & "GUID:" & vbTab & vbTab & It.GUID & vbCr & "Type:" & vbTab & vbTab & It.Type & vbCr & "Isbroken:" & vbTab & vbTab & It.isbroken & vbCr & vbCr
Next It
Debug.Print strIts ' From VB Editor Ctrl+g to get Immediate Window from which info can be copied
End Sub
Here some results. ( If anyone passing has other Excel versions and would like to pass on what the code above gives, then that would be nice, thanks :) )
Excel 2007
Code:
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 0
Major: 4
FullPath: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Excel 12.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 6
Major: 1
FullPath: C:\Program Files\Microsoft Office\Office12\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Office 12.0 Object Library
Name: Office
Buitin: Falsch
Minor: 4
Major: 2
FullPath: C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch
Description: Microsoft Word 12.0 Object Library
Name: Word
Buitin: Falsch
Minor: 4
Major: 8
FullPath: C:\Program Files\Microsoft Office\Office12\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Excel 2003
Code:
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 0
Major: 4
FullPath: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Excel 11.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 5
Major: 1
FullPath: C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\system32\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Office 11.0 Object Library
Name: Office
Buitin: Falsch
Minor: 3
Major: 2
FullPath: C:\Program Files\Common Files\Microsoft Shared\OFFICE11\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch
Description: Microsoft Word 12.0 Object Library
Name: Word
Buitin: Falsch
Minor: 4
Major: 8
FullPath: C:\Program Files\Microsoft Office\Office12\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Excel 2010
Code:
Description: Visual Basic For Applications
Name: VBA
Buitin: Wahr
Minor: 1
Major: 4
FullPath: C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7\VBE7.DLL
GUID: {000204EF-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Excel 14.0 Object Library
Name: Excel
Buitin: Wahr
Minor: 7
Major: 1
FullPath: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
GUID: {00020813-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: OLE Automation
Name: stdole
Buitin: Falsch
Minor: 0
Major: 2
FullPath: C:\Windows\SysWOW64\stdole2.tlb
GUID: {00020430-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Description: Microsoft Office 14.0 Object Library
Name: Office
Buitin: Falsch
Minor: 5
Major: 2
FullPath: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\MSO.DLL
GUID: {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
Type: 0
Isbroken: Falsch
Description: Microsoft Word 14.0 Object Library
Name: Word
Buitin: Falsch
Minor: 5
Major: 8
FullPath: C:\Program Files (x86)\Microsoft Office\Office14\MSWORD.OLB
GUID: {00020905-0000-0000-C000-000000000046}
Type: 0
Isbroken: Falsch
Test data supplied by Thainguyen
To support solution to this Thread:
http://www.excelfox.com/forum/showth...and-send-email
Test data supplied by Thainguyen for this Thread :
http://www.excelfox.com/forum/showth...and-send-email
Code:
Using Excel 2007 32 bit
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
N |
|---|
1 |
Equipment PM |
|
|
|
|
|
|
|
|
|
|
|
2 |
Machine EQ.ID |
Manufacture |
Model |
Description |
Serial Number |
Weekly
Date of Service |
Weekly
Next Service |
Monthly
Date of Service |
Monthly
Next Service |
Quarterly
Date of Service |
Quarterly
Next Service |
Softwear |
3 |
|
|
|
|
|
|
|
|
|
|
|
|
4 |
1 |
JUKI |
GKG GL |
GL SCREEN PRINTER |
A123 |
06.04.2018 |
13.04.2018 |
15.03.2018 |
12.04.2018 |
N/A |
N/A |
|
5 |
2 |
JUKI |
KE-1070L |
SMT Placement Machine |
A124 |
11.04.2018 |
18.04.2018 |
28.03.2018 |
25.04.2018 |
N/A |
N/A |
|
6 |
9 |
ACE Production |
KISS-101B |
Selective Wave Solder |
A125 |
06.04.2018 |
13.04.2018 |
15.03.2018 |
12.04.2018 |
N/A |
N/A |
|
7 |
59 |
Heller |
1826 MK5 |
Reflow Oven |
A126 |
N/A |
N/A |
16.03.2018 |
13.04.2018 |
N/A |
N/A |
|
8 |
62 |
Exit Sign -- N/A -- Exit Lights |
N/A |
N/A |
A127 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
9 |
69 |
South-Tek System |
N2-Gen 35ST |
Nitrogen Generator |
A128 |
10.04.2018 |
17.04.2018 |
N/A |
N/A |
09.03.2018 |
06.04.2018 |
|
10 |
75 |
ACE Production |
KISS-102 |
Selective Wave Solder |
A129 |
16.04.2018 |
23.04.2018 |
N/A |
N/A |
N/A |
N/A |
|
11 |
101 |
FKN system |
N100 Nibbler |
Dispensing |
A130 |
N/A |
N/A |
N/A |
N/A |
04.04.2018 |
02.05.2018 |
|
12 |
109 |
Mycronic |
MY200sx |
SMT Machine |
A131 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
13 |
112 |
X-TEK |
XTV-160 |
X-Ray System |
A132 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
14 |
113 |
MIRTEC |
MV-6 OMNI |
AOI |
A133 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
15 |
116 |
JUKI |
KE-2060RL |
SMT Placement Machine |
A134 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
16 |
127 |
ELGI |
EG22-150 |
Air Compressor |
A135 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
17 |
128 |
Juki |
KE-2050 |
SMT |
A136 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
18 |
137 |
Juki |
K3 |
Screen printer |
A137 |
06.04.2018 |
13.04.2018 |
N/A |
N/A |
N/A |
N/A |
|
19 |
141 |
Heller |
1826 MK5 |
Reflow Oven |
A138 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
20 |
142 |
NISSAN |
MCU-112A331.V |
Forklift |
A139 |
N/A |
N/A |
N/A |
N/A |
15.02.2018 |
15.03.2018 |
|
21 |
142 |
NISSAN/yearly oil change and lube |
MCU-112A331.V |
Forklift |
A140 |
N/A |
N/A |
N/A |
N/A |
N/A |
N/A |
|
22 |
|
|
|
|
|
|
|
|
|
|
28.01.1900 |
|
23 |
|
|
|
|
|
|
|
|
|
|
|
|
Worksheet: Equipment PM
1 Attachment(s)
Re Post code with Code tags
To support this Thread
http://www.excelfox.com/forum/showth...0679#post10679
Re post code in Code tags, Like ....
Please use CODE TAGS if you are writing codes in your post.
To use code tags,
either
select your entire code and press the code tag button # in the editor below,
or
simply type your code as below
[Code]Your Code Here[/Code]
[Code]
Your Code Here
[/Code]
[Code]
Private Sub cmdNot_Click()
Dim OutApp As Object
Dim OutMail As Object
…………………….
……………..
End Sub
[/Code]
BBCodeCodeTags.JPG : https://imgur.com/4HunNcs
Attachment 2060
_.__________________
If you post using Code tags, then it will come out in the final post in a Code Window, like this:
Code:
Private Sub cmdNot_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim fileName As String
Dim mSubject As String
Dim signature As String
Dim fname As String
Dim mBody As String
Dim rng As Range
Dim rng1 As Range
Dim ws As Worksheet
Dim mailTo As String
Set ws = Sheets("MRO")
fname = ws.Range("B4")
mSubject = "MRO " & " For " & Range("C6").Value
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'mBody = "2-SO\Material Request Form .xlsm"
Dim Path As String
mBody = "<font size=""3"" face=""Calibri"">" & _
"Dear Team,<br><br>" & _
"Please open the file from below link and put your signature on the respective cell after you completed your task.<br><B>" & _
fileName & ".xlsm" & "</B> is created.<br>" & _
"Click on this link to open the file : " & _
"<A HREF=""file://" & Path & fileName & ".xlsm" & _
""">Files are saved here</A>" & "-->" & Range("C6").Value & _
"<br><br>Best Regards," & _
"<br><br></font>"
With OutMail
.display
End With
signature = OutMail.body
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With OutMail
'.To = "email"
.To = ""
.CC = ""
.BCC = ""
.Subject = mSubject
'.body = "Dear Team," & vbCrLf & vbCrLf & "Please open the file from below link and put your signature on the respective cell and save the sheet"
'.htmlbody = RangetoHTML(rng)
.htmlbody = mBody
'.Attachments.Add fileName
.display
End With
'ws.PageSetup.RightHeader = "&""Calibri,italic""&11& " & ws.Range("A1")
ActiveWorkbook.Close False
ActiveWorkbook.Close
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Share account for testing file access from a hyperlink in a received EMail
Share account for testing file access from a hyperlink in a received EMail
In support of a possible solution to this post in this Thread:
http://www.excelfox.com/forum/showth...0724#post10724
It is required to have a simple hyperlink to an Excel File appear in the received Email sent to members of a team.
I am not sure currently how to get a link directly to the File.
An second alternative involves storing the file at a File sharing site and using the link to the file as the URL part of a hyperlink.
This post discusses the setting up of such an account to allow storing of, and sharing via a supplied link to, the file.
As an example of a file sharing site we consider the free version of box.net
Some googling my be needed to finally get at the free version which may go under the name of “free” , “Individual rate”, “Personal free”
Currently you need to find your way to the free 10GB offer. This is currently at this link:
https://account.box.com/signup/n/personal#fbms6
Free10GB box net account register.JPG : https://imgur.com/NB3GThi
Note , by registering, you can choose a language to suit you.
Free10GB Select language .JPG : : https://imgur.com/aNzW1kq
( You can change the language to a different one after registering also
Free10GB Change language .JPG : https://imgur.com/IosqbAI )
For this registering , I use the created gmail account used for experiments in the current thread which this post supports, excellearning12@gmail.com ( excelfox Thread : http://www.excelfox.com/forum/showth...and-send-email )
The password I pass on privately to those needing
Free10GB box net account register 2.JPG : https://imgur.com/Y2pLogO
Free10GB box net account register 3.JPG : https://imgur.com/QhCR8fP
Free10GB box net account register Verify Email 4.JPG : https://imgur.com/ffG7erw
Various steps are then gone through, they may be slightly different to the following:
At some point you should you should see the possibility to upload a file, following steps similar to these:
Free10GB box net 5 .JPG : https://imgur.com/lNWvQwF
To upload a file and get a URL link to use in a hyperlink to it:
Upload Files:
Free10GB box net 6 .JPG : https://imgur.com/rTU1Xbk
Select a file:
Free10GB box net 7 .JPG : https://imgur.com/wKKlqoO
Select share to obtain a URL link to the file :
Free10GB box net 8 .JPG : https://imgur.com/R3VbyhR
Copy link to be used in Hyperlink :
Free10GB box net 9 .JPG : https://imgur.com/8yaYwaK
Testing Hyperlinks in received EMail
Testing codes in support of this Thread
http://www.excelfox.com/forum/showth...0727#post10727
Codes for Alf and sandy666
Code:
Option Explicit
Sub SendfromExcelVBAExpgmail()
Rem 6 EMail send 'For info see: http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once#post10519
'Working at my end With my With End With Created LCDCW Library, (LCD 1.0 Library ) (Linking Configuration Data_Cods Wollups)
'6a(i)
With CreateObject("CDO.Message") ' -Linking Cods Wollups--------
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/"
.Configuration(LCD_CW & "smtpusessl") = True '
.Configuration(LCD_CW & "smtpauthenticate") = 1 '
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = "smtp.gmail.com" ' "smtp.office365.com" ' "smtp.live.com" ' "smtp-mail.outlook.com" ' "smtp.live.com" ' "smtp.gmail.com" ' "securesmtp.t-online.de" "smtp.mail.yahoo.com" "smtp.live.com" "pod51017.outlook.com" "smtp-mail.outlook.com" "smtp.live.com" "securesmtp.t-online.de"
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = 2 ' Based on the LCD_OLE Data Base of type DBTYPE_I4
.Configuration(LCD_CW & "smtpserverport") = 465 ' 465 or 25 for gmail '587 ' 25 ' 465 or 25 for t-online.de 'or 587 'or 25
'
.Configuration(LCD_CW & "sendusername") = "ExcelVBAExp@gmail.com" '
.Configuration(LCD_CW & "sendpassword") = "xxxxxxxxxxxxxx" ' '
' .Configuration(LCD_CW & "sendusername") = "YourEMailAddress"
' .Configuration(LCD_CW & "sendpassword") = "YourEMailPassword"
' Optional - How long to try
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 '
' Intraction protocol is Set/ Updated
.Configuration.Fields.Update '
'End With 6a(i)' ---------------------- my Created LCDCW Library
'6a(ii) With ' -- ' Data to be sent--- my Created LCDCW Library
Dim strHTML As String: Let strHTML = "<font size=""3"" face=""Calibri"">" & _
"This is sent from EMail account:" & _
"<br>Username: ""ExcelVBAExp@gmail.com""" & _
"<br>Password: ""xxxxxxxxxxxxxxxxxxxxxx""" & _
"<br><br>" & _
"<br>Please click on the 5 links below and tell me what happens, thanks!" & _
"<br>1 <A HREF=""https://app.box.com/s/x01liz3ralbdrt152i52fpwb0wx40ff3"">link to box net cloud free file sharing</A>" & _
"<br>2 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received EMail.htm"""">htm file on your computer</A>" & _
"<br>3 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received EMail.htm"""">htm file on your computer</A>" & _
"<br>4 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls"""">empty xls file on your computer</A>" & _
"<br>5 <A HREF=""file:///" & ThisWorkbook.Path & "\" & "Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls"""">empty xls file on your computer</A>"
.To "xxxxxxxxxxxxxx"
.CC "xxxxxxxxxxxxxa"
.BCC = ""
.from = """ExcelVBAExp@gmail.com"" <ExcelVBAExp@gmail.com>"
.Subject = "Sent from EMail address: ExcelVBAExp@gmail.com"
.htmlbody = strHTML
.Send ' Do it
End With ' 6a(ii) CreateObject("CDO.Message") ---my Created LCDCW Library
End Sub
Sub SendfromFahrradprinzessinunterwegsgmail()
Rem 6 EMail send 'For info see: http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once#post10519
'Working at my end With my With End With Created LCDCW Library, (LCD 1.0 Library ) (Linking Configuration Data_Cods Wollups)
'6a(i)
With CreateObject("CDO.Message") ' -Linking Cods Wollups--------
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/"
.Configuration(LCD_CW & "smtpusessl") = True '
.Configuration(LCD_CW & "smtpauthenticate") = 1 '
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = "smtp.gmail.com" ' "smtp.office365.com" ' "smtp.live.com" ' "smtp-mail.outlook.com" ' "smtp.live.com" ' "smtp.gmail.com" ' "securesmtp.t-online.de" "smtp.mail.yahoo.com" "smtp.live.com" "pod51017.outlook.com" "smtp-mail.outlook.com" "smtp.live.com" "securesmtp.t-online.de"
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = 2 ' Based on the LCD_OLE Data Base of type DBTYPE_I4
.Configuration(LCD_CW & "smtpserverport") = 465 ' 465 or 25 for gmail '587 ' 25 ' 465 or 25 for t-online.de 'or 587 'or 25
'
.Configuration(LCD_CW & "sendusername") = "Fahrradprinzessinunterwegs@gmail.com" '
.Configuration(LCD_CW & "sendpassword") = "xxxxxxxxxxxxx" ' '
' .Configuration(LCD_CW & "sendusername") = "YourEMailAddress"
' .Configuration(LCD_CW & "sendpassword") = "YourEMailPassword"
' Optional - How long to try
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 '
' Intraction protocol is Set/ Updated
.Configuration.Fields.Update '
'End With 6a(i)' ---------------------- my Created LCDCW Library
'6a(ii) With ' -- ' Data to be sent--- my Created LCDCW Library
Dim strHTML As String: Let strHTML = "<font size=""3"" face=""Calibri"">" & _
"This is sent from EMail account:" & _
"<br>Username: ""Fahrradprinzessinunterwegs@gmail.com""" & _
"<br>Password: ""xxxxxxxxxxxxxxxxxxx""" & _
"<br><br>" & _
"<br>Please click on the 5 links below and tell me what happens, thanks!" & _
"<br>1 <A HREF=""https://app.box.com/s/x01liz3ralbdrt152i52fpwb0wx40ff3"">link to box net cloud free file sharing</A>" & _
"<br>2 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received EMail.htm"""">htm file on your computer</A>" & _
"<br>3 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received EMail.htm"""">htm file on your computer</A>" & _
"<br>4 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls"""">empty xls file on your computer</A>" & _
"<br>5 <A HREF=""file:///" & ThisWorkbook.Path & "\" & "Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls"""">empty xls file on your computer</A>"
.To "xxxxxxxxxxxxxxxxxxxx"
.CC "xxxxxxxxxxxxxxxxxxx"
.BCC = ""
.from = """Fahrradprinzessinunterwegs@gmail.com"" <Fahrradprinzessinunterwegs@gmail.com>"
.Subject = "Sent from EMail address: Fahrradprinzessinunterwegs@gmail.com"
.htmlbody = strHTML
.Send ' Do it
End With ' 6a(ii) CreateObject("CDO.Message") ---my Created LCDCW Library
End Sub
Sub SendfromDocAlnsteinGermanTelekom()
Rem 6 EMail send 'For info see: http://www.excelfox.com/forum/showthread.php/2233-Urgent-support-needed-Multiple-emails-multiple-Excel-workbooks-at-once#post10519
'Working at my end With my With End With Created LCDCW Library, (LCD 1.0 Library ) (Linking Configuration Data_Cods Wollups)
'6a(i)
With CreateObject("CDO.Message") ' -Linking Cods Wollups--------
Dim LCD_CW As String: Let LCD_CW = "http://schemas.microsoft.com/cdo/configuration/"
.Configuration(LCD_CW & "smtpusessl") = True '
.Configuration(LCD_CW & "smtpauthenticate") = 1 '
' ' Sever info
.Configuration(LCD_CW & "smtpserver") = "securesmtp.t-online.de" ' "smtp.gmail.com" ' "smtp.office365.com" ' "smtp.live.com" ' "smtp-mail.outlook.com" ' "smtp.live.com" ' "smtp.gmail.com" ' "securesmtp.t-online.de" "smtp.mail.yahoo.com" "smtp.live.com" "pod51017.outlook.com" "smtp-mail.outlook.com" "smtp.live.com" "securesmtp.t-online.de"
' The mechanism to use to send messages.
.Configuration(LCD_CW & "sendusing") = 2 ' Based on the LCD_OLE Data Base of type DBTYPE_I4
.Configuration(LCD_CW & "smtpserverport") = 465 ' 465 or 25 for gmail '587 ' 25 ' 465 or 25 for t-online.de 'or 587 'or 25
'
.Configuration(LCD_CW & "sendusername") = "Doc.Alnstein@t-online.de" '
.Configuration(LCD_CW & "sendpassword") = "xxxxxxxxx" ' '
' .Configuration(LCD_CW & "sendusername") = "YourEMailAddress"
' .Configuration(LCD_CW & "sendpassword") = "YourEMailPassword"
' Optional - How long to try
.Configuration(LCD_CW & "smtpconnectiontimeout") = 30 '
' Intraction protocol is Set/ Updated
.Configuration.Fields.Update '
'End With 6a(i)' ---------------------- my Created LCDCW Library
'6a(ii) With ' -- ' Data to be sent--- my Created LCDCW Library
Dim strHTML As String: Let strHTML = "<font size=""3"" face=""Calibri"">" & _
"This is sent from EMail account:" & _
"<br>Username: ""Doc.Alnstein@t-online.de""" & _
"<br>Password: ""xxxxxxxxxxx""" & _
"<br><br>" & _
"<br>Please click on the 5 links below and tell me what happens, thanks!" & _
"<br>1 <A HREF=""https://app.box.com/s/x01liz3ralbdrt152i52fpwb0wx40ff3"">link to box net cloud free file sharing</A>" & _
"<br>2 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received EMail.htm"""">htm file on your computer</A>" & _
"<br>3 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received EMail.htm"""">htm file on your computer</A>" & _
"<br>4 <A HREF=""file://" & ThisWorkbook.Path & "\" & "Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls"""">empty xls file on your computer</A>" & _
"<br>5 <A HREF=""file:///" & ThisWorkbook.Path & "\" & "Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls"""">empty xls file on your computer</A>"
.To "xxxxxxxxxxxxxxxxxxxxxxxx"
.CC "xxxxxxxxxxxxxxxxxxxxxxxxx"
.BCC = ""
.from = """Doc.Alnstein@t-online.de"" <Doc.Alnstein@t-online.de>"
.Subject = "Sent from EMail address: Doc.Alnstein@t-online.de"
.htmlbody = strHTML
.Send ' Do it
End With ' 6a(ii) CreateObject("CDO.Message") ---my Created LCDCW Library
End Sub
Instructions:
Three files are attached. Please download them and store them all somewhere on your computer. They can be stored anywhere, but important is that they are all stored in the same Folder :
All 3 files stored in same place.JPG : https://imgur.com/rFu0TML
Please open only one file “Test File xxxx to send EMail containing Hyperlinks to Files.xlsm”
Enable macros.
There are three codes in file “Test File xxxx to send EMail containing Hyperlinks to Files.xlsm”.
The codes are very similar, differing only in the Email account used as the .Sender:
Sub SendfromDocAlnsteinGermanTelekom()
Sub SendfromFahrradprinzessinunterwegsgmail()
Sub SendfromExcelVBAExpgmail()
Please try to run those codes.
Each code should send you an Email which on arrival will look something similar to this:
Typical received EMail.JPG : https://imgur.com/4oNXNtW
Please click on the 5 Hyperlinks and tell me what happens.
My final goal is to get a Hyperlink which when clicked opens an Excel or Word File.
I have tested the codes sending to my gmail and German Telekom Email accounts.
But so far, only link 1 works. But link 1 does not open a file: It simply sends you to a file sharing site. So link 1 is a temporary solution for me.
Code for Thai in next post....
1 Attachment(s)
Attaching a File to a Thread post at excelfox
Attaching a File to a Thread post at excelfox
1 To get Manage Attachments Window dialogue box
First you must get up the Manage Attachments Window dialogue box.
_(i) For a new Thread
Either
_1_(i) _a) Select Paper clip icon
Or
_1_(i) _b) Scroll down and select manage attachments
a)PaperClipIcon or b)ManageAttachmants.JPG : https://imgur.com/YFEUDUh
(ii) For a Reply or when Editing an existing post
_ Hit Reply button or Edit Post Button
Reply or Edit Post.JPG : https://imgur.com/Bm1Zy6T
_ Hit Go Advanced button
GoAdvancedReplyWindow.JPG , GoAdvanced1.JPG : https://imgur.com/QLhHBGl , https://imgur.com/WXoKcoF
_ Scroll down and select manage attachments
Scroll down to Hit manage Attachments.JPG : https://imgur.com/uNkr6Eq
Finally you should see the Manage Attachments Window dialogue box
Manage Attachments Window dialogue box.JPG : https://imgur.com/BFFUIuG
Attachment 2103
Using this dialogue box window you can manage your attachments
2 To add a File to the current post:
Steps like the following are needed to attach a file to the current post. It may look a little bit different on your computer
_ Add Files.JPG : https://imgur.com/hIdo0Av
_ SelectFiles.JPG : https://imgur.com/9XZJuig
_ UploadFiles5.JPG : https://imgur.com/f0PXtVA
_ Done6.JPG : https://imgur.com/a6oFeIQ
That's it!...:)
The file should now have been attached.
_._______
Practice before posting in a main Thread:
You can practice uploading a file by starting a new test thread here:
http://www.excelfox.com/forum/forumd...p/17-Test-Area
Give the Thread a title such as …"Just testing. No Reply needed"
Test Area new Thread 1 .JPG , Test Area new Thread just testing .JPG https://imgur.com/S3uneWf , https://imgur.com/gUFHcBp
You can then practice uploading attachments or you can also practice any other posting and editing features, such as code tags ( http://www.excelfox.com/forum/showth...0690#post10690 )
_._____________________________
Alternative to attaching a file: post a link to your file held at a file share site:
See here for example:
http://www.excelfox.com/forum/showth...age8#post10725
Or if you are familiar with file sharing sites go direct here
https://account.box.com/signup/n/personal#58luf
Loop through closed workbooks without opening them
Code for Yasser, here: http://www.eileenslounge.com/viewtop...241152#p241148
Code:
Option Explicit
Sub SUMfromD14inClsdWkBksInFolder() ' Loop through closed workbooks without opening them ' http://www.eileenslounge.com/viewtopic.php?f=30&t=31150&p=241152#p241152
' Use Dir function with wildcards in full path and name search string to find file names you want
Dim FileName As String:
Let FileName = Dir("C:\Users\Elston\Desktop\YassersFolder\*record*", vbNormal) ' The Dir function uased the first time here, it will find the first file with "record" in its file name in the folder , "YassersFolder". If it does not find one, it will return "". If it finds one, then variable FileName will be given its name, ( just the name, not the entire file path and name)
'Do do Looping while you find the file names you want =========
Do While Not FileName = "" ' Dir Function will return "" if it finds no new File names of the ones looking for. If it does find a File name, then use that filename in the closed workbook reference which you put in a spare cell, for example, A1
Let ThisWorkbook.Worksheets.Item(1).Range("A1").Value = "=" & "'" & "C:\Users\Elston\Desktop\YassersFolder\" & "[" & FileName & "]Tabelle1'!$D$14"
Dim SomeTotal As Double ' A variable to hold the Sum total so far
Let SomeTotal = SomeTotal + ThisWorkbook.Worksheets.Item(1).Range("A1").Value
Let FileName = Dir ' an unqualified Dir will look again using the last search criteria, so the first time this line is used, Dir Function will try to find a second file with the string part "record" in its file name
Loop ' do while you find the file names you want ==========
Let ThisWorkbook.Worksheets.Item(1).Range("A10").Value = SomeTotal
End Sub
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195
https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062
https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909
https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313859#p313859
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313855#p313855
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313848#p313848
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313843#p313843
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313792#p313792
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313771#p313771
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313767#p313767
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313746#p313746
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313744#p313744
https://www.eileenslounge.com/viewtopic.php?f=30&t=40560&p=313741#p313741
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313622#p313622
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313575#p313575
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313573#p313573
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313563#p313563
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313555#p313555
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533
https://www.eileenslounge.com/viewtopic.php?f=39&t=40265&p=313468#p313468
https://www.eileenslounge.com/viewtopic.php?f=42&t=40505&p=313411#p313411
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313384#p313384
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313382#p313382
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313380#p313380
https://www.eileenslounge.com/viewtopic.php?f=30&t=40501&p=313378#p313378
https://www.eileenslounge.com/viewtopic.php?f=32&t=40473&p=313305#p313305
https://www.eileenslounge.com/viewtopic.php?f=44&t=40455&p=313035#p313035
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312889#p312889
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312886#p312886
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312752#p312752
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312734#p312734
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312727#p312727
https://www.eileenslounge.com/viewtopic.php?f=18&t=40411&p=312724#p312724
https://www.eileenslounge.com/viewtopic.php?f=44&t=40374&p=312535#p312535
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
Named Ranges scope defines the default path used for a named range, nothing else
Codes to support this
https://www.thespreadsheetguru.com/b...ent-4189507335
....
The main demo code is Sub NamedRangeScopes() , but that Calls the others, so copy them all to the same code module , and then run the main demo code, Sub NamedRangeScopes()
Code:
Sub NamedRangeScopes()
10 Call FukOffNames
20 Call getWbNames
30 Rem 1 Add 3 named ranges, 1(i) '_-in the Workbooks name object collection, and 1(ii) in the first worksheet name object collection and 1(iii) '_-in the second worksheet name object collection
40 '1(i) Add a Workbook names object in the Workbook name object collection of this workbook
50 ThisWorkbook.Names.Add Name:="Name1", RefersTo:=ThisWorkbook.Worksheets.Item(1).Range("A1") '_-in the Workbooks name object collection
60 'The form above is like ThisWorkbook.Names.Add Name:="Name1", RefersTo:=Worksheets(Sheet1).Range("A1")
70 '1(ii) Add a name object in the first worksheet's name object collection
80 ThisWorkbook.Worksheets.Item(1).Names.Add Name:="Name1", RefersTo:=ThisWorkbook.Worksheets.Item(1).Range("A1") '_-in the first worksheet name object collection
90 'The form above is like Worksheets("Sheet1).Names.Add Name:="Name1" , RefersTo:=Sheet1.Range("A1")
100 '1(iii) Add a name object in the second worksheet's name object collection
110 ThisWorkbook.Worksheets.Item(2).Names.Add Name:="Name2", RefersTo:=ThisWorkbook.Worksheets.Item(2).Range("A1") '_-in the second worksheet name object collection
120 'The form above is like Worksheets("Sheet2).Names.Add Name:="Name2" , RefersTo:=Sheet2.Range("A1")
130 Rem 2 Change the string name of a named range
140 Call GetChaNameObjects(140) ' Check out Info for all Name objects
150 '2a) Use Workbook names objects to Change the worksheet names object name that has the same name as the workbook names object name, change it twice, first using the workbook names object collection and then the worksheet names object collection
160 Let ThisWorkbook.Names(ThisWorkbook.Worksheets.Item(1).Name & "!" & "Name1").Name = "Name1_1"
170 ' The form above is like ThisWorkbook.Names("Sheet1!Name").Name = "Name1_1"
180 Call GetChaNameObjects(180)
190 Let ThisWorkbook.Worksheets.Item(1).Names(ThisWorkbook.Worksheets.Item(1).Name & "!" & "Name1_1").Name = "Name1_2"
200 Call GetChaNameObjects(200)
210 Let ThisWorkbook.Worksheets.Item(1).Names("Name1_2").Name = "Name1_3"
220 Call GetChaNameObjects(220)
230 '2b) use a Worksheet's (in this example the second worksheet's) name objects to Change the second worksheet's names object, ( we gave it "Name2", but Excel adds a bit so it looks like Sheet2!Name2" which you can get from a VBA code line like ThisWorkbook.Worksheets.Item(2).Name & "!" & "Name2" I do this just in case your second worksheet has a tab name other than Sheet2
240 Let ThisWorkbook.Worksheets.Item(2).Names("Name2").Name = "Name2_2"
250 ' Note: you could have equally done this: Let ThisWorkbook.Worksheets.Item(2).Names(ThisWorkbook.Worksheets.Item(2).Name & "!" & "Name2").Name = "Name2_2" , which is like Let ThisWorkbook.Worksheets.Item(2).Names("Sheet2!Name2").Name = "Name2_2"
260 Call GetChaNameObjects(260)
270 Rem 3 Change the string name of a named range, for example the one in the second worksheet names collection whichg we just renamed to "Name2_2" ,(which Excel holds as like "Sheet2!Name2_2")
280 '3a) Use Workbook names objects
290 Let ThisWorkbook.Names(ThisWorkbook.Worksheets.Item(2).Name & "!" & "Name2_2").RefersTo = ThisWorkbook.Worksheets.Item(2).Range("Z123")
300 Call GetChaNameObjects(300)
310 '3b) Use the second worksheets's names objects
320 Let ThisWorkbook.Worksheets.Item(2).Names("Name2_2").RefersTo = ThisWorkbook.Worksheets.Item(2).Range("X23")
330 Call GetChaNameObjects(330)
End Sub
Code:
Sub FukOffNames()
Dim Nme As Name
For Each Nme In ThisWorkbook.Names
Nme.Delete
Next Nme
End Sub
Code:
Sub GetChaNameObjects(ByVal CodLn As Long)
Dim Nme As Name, strOut As String
' Name objects belonging in Workbook Names Colection (Workbooks scope)
For Each Nme In ThisWorkbook.Names
If InStr(1, Nme.Name, "!", vbBinaryCompare) > 0 Then ' we will see that a name for a worksheet scope, has an extra bit added onto the name we gave it which includes a "!"
Let strOut = strOut & "Name object Name is """ & Nme.Name & """ (you gave """ & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """)" & vbCrLf & "It has worksheet scope and" & vbCrLf & "it refers to range """ & Nme.RefersTo & """" & vbCrLf & vbCrLf & vbCrLf
Else ' we will see that a name for a workbook scope, remains just as we gave it
Let strOut = strOut & "Name object Name is """ & Nme.Name & """ (the same as you gave)" & vbCrLf & "It has workbook scope and" & vbCrLf & "it refers to range """ & Nme.RefersTo & """" & vbCrLf & vbCrLf & vbCrLf
End If
Next Nme
MsgBox prompt:="Workbook names situation at Code Line " & CodLn & vbCrLf & vbCrLf & strOut, Title:="Name objects in Workbook """ & ThisWorkbook.Name & """ Names Colection are:-": Debug.Print "Name objects in Workbook """ & ThisWorkbook.Name & """ Names Colection are:-" & vbCr & strOut
' Name objects belonging in Workbooks Names Colection (Worksheets scope)
Dim Ws As Worksheet: Let strOut = ""
For Each Ws In ThisWorkbook.Worksheets
For Each Nme In Ws.Names
Let strOut = strOut & "Name object name is """ & Nme.Name & """ (you gave """ & Mid(Nme.Name, 1 + InStr(1, Nme.Name, "!", vbBinaryCompare)) & """)" & vbCrLf & "It has worksheets scope and" & vbCrLf & "it belongs to the Names collection of worksheet """ & Ws.Name & """" & vbCrLf & "and it refers to range """ & Nme.RefersTo & """" & vbCrLf & vbCrLf
Next Nme
Next Ws
MsgBox prompt:="Worksheets names situation at Code Line " & CodLn & vbCrLf & vbCrLf & strOut, Title:="Name objects in all the worksheets Names Colections are:-": Debug.Print "Name objects in all the worksheets Names Colections are:-" & strOut
End Sub
Code:
Sub getWbNames()
Dim Nme As Name, Cnt As Long
For Each Nme In ThisWorkbook.Names
Let Cnt = Cnt + 1
Dim strNames As String: Let strNames = strNames & Cnt & " "
If TypeOf Nme.Parent Is Worksheet Then ' https://stackoverflow.com/questions/8656793/progammatically-determine-if-a-named-range-is-scoped-to-a-workbook
Let strNames = strNames & """" & Nme.Name & """ refers to the range ref """ & Nme & """ and and can be referenced only from worksheet with tab Name """ & Nme.Parent.Name & """ ( Worksheet Scope ). ( That worksheet is in the workbook """ & Nme.Parent.Parent.Name & """ )" & vbCrLf & vbCrLf
Else
Let strNames = strNames & """" & Nme.Name & """ refers to the range ref """ & Nme & """ and can be referenced from any sheet in the Workbook """ & Nme.Parent.Name & """ ( Workbook Scope )" & vbCrLf & vbCrLf
End If
Next Nme
If strNames = "" Then
MsgBox prompt:="I don't think you have any Names at the moment luvy"
Else
MsgBox prompt:=strNames, Title:="Spreadsheet Named range objects in " & ThisWorkbook.Name & " are:-": Debug.Print strNames
End If
End Sub
Further Practice with using named ranges
First main Demo code in support of this Thread:
http://www.excelfox.com/forum/showth...-a-named-range
Posts from approximately here:
http://www.excelfox.com/forum/showth...814#post10814:
Code:
Sub FoxySingleCellNamedRanges()
10 Rem -2 Range Info etc.
20 Dim WbMain As Workbook, dataWb1xls As Workbook, dataWb2xlsx As Workbook
30 Set WbMain = Workbooks("MasturFile.xlsm") 'Set WbMain = ThisWorkbook
40 Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
50 Set dataWb1xls = Workbooks("Data1.xls")
60 Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
70 Set dataWb2xlsx = Workbooks("Data2.xlsx")
80 '
90 Dim LisWkBkPath As String: Let LisWkBkPath = "=" & "'" & ThisWorkbook.Path & "\"
100 '-2b) Some variables to hold a full reference string which we will use in places where we might need any of these variations for a cell reference Sheet7!B5 [myWorkbook.xlsm] Sheet4!B5 'G:\Desktop\MyFolder\[DataFile.xlsx]Tabelle1'!B5 The last one is the form we hold in the variables. Excel and Excel VBA , usually has no issues if you use the full reference in situations where one of the shorter versions may have been sufficient. But on the other hand, you may get unexpected problems if you used a shorter version , and Excel then guesses wrongly for the remaining part, which I believe it always adds internally, ( possibly at some compiling stage ) , before it uses it.
110 Dim MBkTab1B5 As String ' To hold full string reference to B5 in Master Workbook
120 Let MBkTab1B5 = "=" & "'" & ThisWorkbook.Path & "\" & "[" & "MasturFile.xlsm" & "]" & "Tabelle1" & "'" & "!" & "B5"
130 Dim Dat1Tab1B5 As String ' B5 in data1 workbook
140 Let Dat1Tab1B5 = "=" & "'" & ThisWorkbook.Path & "\" & "[" & "Data1.xls" & "]" & "Tabelle1" & "'" & "!" & "B5"
150
160 Rem -1 Error handler
170 On Error GoTo ErrorHandlerCodeSection:
180 GoTo PastErrorHandler
190 ErrorHandlerCodeSection:
200 MsgBox prompt:="Code errored at line " & Erl & " , error was:" & vbCrLf & vbCrLf & Err.Number & " " & Err.Description
210 Debug.Print Err.Number & " " & Err.Description
220 Resume Next
230 PastErrorHandler:
240 Rem 0 Clean up
250 Dim WkBk As Workbook
260 For Each WkBk In Workbooks
270 Call FukYaWkBkNames(WkBk)
280 'Call GeTchaNms(280, WkBk)
290 Next WkBk
300 Workbooks("Data1.xls").Close savechanges:=True
310 Workbooks("Data2.xlsx").Close savechanges:=True
312 '0b) clear the entire data ranges in the first worksheet in the main workbook, both headers and data
315 ThisWorkbook.Worksheets.Item(1).Range("B5:C12").ClearContents
320 Rem _1) Data1 "Food" header
330 '1a) Data1 cell Workbook Scoped to its workbook : Info needed for a range in that data file is held in the workbooks name objects collection object of that workbook
340 Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
350 Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
360 dataWb1xls.Names.Add Name:="Dta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5) ' A personal preference of mine is , once again, to use a full reference. This time it is in the Refers To range. This Refers To:= argument would never need the full file path reference, as the range referenced must be to a range in an open book. Never the less, as usual, VBA accepts the full reference
370 Call GeTchaNms(370, dataWb1xls)
380 dataWb1xls.Close savechanges:=True ' I don't need the workbook open for the next line to work, but I made Added a named range object so I must save the changes for the next line to work as that named range is referenced
390 Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' "Going" to Workbook Data1.xls
400 Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[Data1.xls]Tabelle4'!Dta1Foodheader" ' "Going" to any worksheet in Data1.xls
410 '1b) Data1 cell Worksheet Scoped to one of its worksheets: Info needed is held in the named objects object of its second worksheets
420 Rem _1 Add some named ranges
430 Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
440 Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
450 dataWb1xls.Worksheets.Item(2).Names.Add Name:="Ws2Dta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5)
460 Call GeTchaNms(460, dataWb1xls)
470 dataWb1xls.Close savechanges:=True ' I don't need the workbook open for the next line to work, but I made Added a named range object so I must save the changes for the next line to work as that named range is referenced
480 Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
490 '1b)(ii)
500 Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
510 Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
520 Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[Data1.xls]Tabelle2'!Ws2Dta1Foodheader"
530 dataWb1xls.Close savechanges:=False ' I made no changes intentionally , so save without changes in case I accidentally changed anything
540 '1c) Data1 cell Workbook Scoped to a different (open) workbook : Info needed for a range in the data 1 file is held in the workbooks name objects collection object of that workbook, the main file in this case
550 Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
560 Set dataWb1xls = Workbooks("Data1.xls") ' We need this open for the referred to range in the RefersTo:= range reference below
570 WbMain.Names.Add Name:="MainDta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5)
580 dataWb1xls.Close savechanges:=False ' I had this open for the Refers To:= above, but I did not change anything, for example, this time i was not doing anything to any of its named range objects, so just iin case I accidentally changed anything I will close without saving any changes
590 Call GeTchaNms(590, WbMain)
600 Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "MasturFile.xlsm'!MainDta1Foodheader" ' "Going" to Workbook MasturFile.xlsm
610 Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "[MasturFile.xlsm]Tabelle4'!MainDta1Foodheader" ' "Going" to any worksheet in MasturFile.xlsm
620 '1d) This is an attempt to get at the named range object in a roundabout sort of a way. Here the data 1 cell s scoped to the second data file, "Data2.xlsx" ( Workbooks scoped to workbook "Data2.xlsx" )
630 Workbooks.Open Filename:=ThisWorkbook.Path & "\Data1.xls"
640 Set dataWb1xls = Workbooks("Data1.xls")
650 Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
660 Set dataWb2xlsx = Workbooks("Data2.xlsx")
670 dataWb2xlsx.Names.Add Name:="Dta2Dta1Foodheader", RefersTo:=Application.Range(Dat1Tab1B5)
680 Let Application.Range(MBkTab1B5).Value = LisWkBkPath & "Data2.xlsx'!Dta2Dta1Foodheader" ' "Going" to Workbook MasturFile.xlsm
690 dataWb1xls.Close savechanges:=False ' I had this open for the Refers To:= above, but I did not change anything, for example, this time i was not doing anything to any of its named range objects, so just iin case I accidentally changed anything I will close without saving any changes
700 dataWb2xlsx.Close savechanges:=True ' A name object was Added, so I have a change to save
710 Let Application.Range(MBkTab1B5).Value = Application.Range(MBkTab1B5).Value ' I have done this here to "catch" the value put in, as it seems to vanish if I re enter the formula ??
720 Rem 2 Experiments with named ranges in the LHS , like in Range("rngNamed") =
730 '2a) scope to a data file
740 Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
750 Set dataWb2xlsx = Workbooks("Data2.xlsx") ' Open an arbritrary data file to use one if its names objects as the place to go to get the info about the named range
760 dataWb2xlsx.Names.Add Name:="Dta2MainFoodheader", RefersTo:=Application.Range(MBkTab1B5)
770 Call GeTchaNms(770, dataWb2xlsx)
780 Let Application.Range(LisWkBkPath & "Data2.xlsx'!Dta2MainFoodheader").Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' LHS is going to workbook Data2.xlsx RHS is "Going" to Workbook Data1.xls
790 dataWb2xlsx.Close savechanges:=True ' A name object was Added, so I have a change to save
800 Let Application.Range(LisWkBkPath & "Data2.xlsx'!Dta2MainFoodheader").Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' LHS is going to workbook Data2.xlsx RHS is "Going" to Workbook Data1.xls
810 '2b) Workbooks Scope to main workbook: Info for named range is in Name Objects collection of Main workbook
820 WbMain.Names.Add Name:="MainFoodheader", RefersTo:=Application.Range(MBkTab1B5)
830 Let Application.Range(LisWkBkPath & WbMain.Name & "'!MainFoodheader").Value = LisWkBkPath & "Data1.xls'!Dta1Foodheader" ' LHS is going to workbook Data2.xlsx RHS is "Going" to Workbook Data1.xls
840 Call GeTchaNms(840, WbMain)
850 Rem 3 Bring in Header "Suppliment" from data 2 workbook directly without named ranges
860 Workbooks.Open Filename:=ThisWorkbook.Path & "\Data2.xlsx"
870 Set dataWb2xlsx = Workbooks("Data2.xlsx") ' Needed for next line
880 Let Application.Range("=" & "'" & WbMain.Path & "\" & "[" & WbMain.Name & "]" & WbMain.Worksheets.Item(1).Name & "'" & "!" & "B10").Value = "=" & "'" & dataWb2xlsx.Path & "\" & "[" & dataWb2xlsx.Name & "]" & dataWb2xlsx.Worksheets.Item(1).Name & "'" & "!" & "B10"
890 dataWb2xlsx.Close savechanges:=False
End Sub
