,ybm<yb
,ybm<yb
,ybm<yb
adljvlkdsajv
adljvlkdsajv
adjvalkjfad
adjvalkjfad
cvv
cvv
sddvlksnv
sddvlksnv
Page 4
https://www.excelfox.com/forum/showt...PI-stuff/page4
https://www.excelfox.com/forum/showt...ll=1#post17896
https://www.excelfox.com/forum/showt...age3#post24934
#post17896 , Thread2824 , Post 31
Some notes related loosely to these forum posts
https://eileenslounge.com/viewtopic....322955#p322955 https://eileenslounge.com/viewtopic....323065#p323065
https://eileenslounge.com/viewtopic.php?f=30&t=41659
https://www.excelfox.com/forum/showt...dows-API/page3
https://www.excelfox.com/forum/showt...ll=1#post24934
https://www.excelfox.com/forum/showt...age3#post24934
post24934, Thread2989
Theory Ælstein
This is not necessarily a final answer, ( an unlikely to be the final word on the issue ), but this is an attempt to get the thing out, and keep it simmering while I take a break to write my Xmas cards. I have only looked in depth at less than a third of the book chapter
API App message box renewed experiments
Because I am new and still very flaky with API stuff, and because whilst on Panzer Schokolade a few years back, I got to know this API MessageboxAA very well, almost as well probably as a computer professional, exceptionally for Layman me, and as part of my recent API revision I revisited there.
So let me see if I can apply some of my initial findings and solution/ Theory suggestions to that.
In my research and experiments I went off in a tangent into very basic Unicode and ANSI/Ascii associated with VB strings and windoew32 API. In the long run, that may not be such a bad thing. The detailed discussions will be continued in more detail in this page 2 ++
Some (Vague for now) thoughts / Clues gleaned from a week of research
In no particular order, yet….
( These thoughts are given in more detail and better ordered in this page 2 ++ But it’s not finished yet. I have only looked in depth at less than a third of that book chapter
_ Things windows API certainly where and in many ways still are, or efficiently use, ANSI. This does cause issues with String things
_ The issues have been known about for over 20 years and we have things like strPtr extra made to help us, along with things like LongPtr which compliment it in some ways
_ a VBA string variable is a pointer to a VB pointer which final points to some form or part of the final string you actually want. Pointing to a pointer is possibly not so bad, whereas mixing up a pointer with a string is more deadly.
_** At some point in that book chapter , the author is hinting that his attempts to explain what is going on with existing VB documentation are his ideas and that other smart people were thinking something extra might be going on,
_ Somewhat less known as it perhaps should be, … for almost, if not all, API functions/ Sub routines, there is two versions, denoted by the last character in the name of A and W. Very briefly W can be thought of as meaning Unicode, A as ANSI/Ascii. The W things are not so new….
_ I noticed some strange things with types and type documentation. When considering string arguments, they seem to be wording things sometimes as if they are not talking about the string, but the pointer. I got some strange feeling that talking pointers is more low level, or more fundamental
_ I notice some things giving me the string problem issues were actually, (not always), sometimes in documentation or in working API stuff, declared As Any when String was either more intuitive or more often used.
My brain went off in some lateral thinking that rarely produce anything other than time and resource loss, but when they do 1 in 10 times produce something, it is usually more than worth the "lost" investment.
What did I decide to look at, and why
_ (For one thing the computer Unicode ANSI/Ascii very basics and History thereof. But maybe I may not have needed that if I was not so doubly ignorant in this area….)
_ I decided to look in strings, and string types, and string related pointer things.
_ I went off in an absurd diverse direction, thinking to get a way to do something involving a string by avoiding the string.
** As I went on, I started thinking that this original throw away thought of mine was not so bad after all …
…....... the simple answer is that ByVal and ByRef behaviour in windows API Declare lines is different, at least with As String.. ….
.... no one could figure out a more ordered logical well organised interface so the nearest looking combination of Declare line, call line and associated coding is used as an enigma type coding that sets off some particular back end coding to do something….
As I went on, I came to an idea, which with hindsight later searching, some people, al be it very few, but computer Profis never the less, also thought…. As soon as VBA sees a Declare line, it decides to do some Unicode to ANSI transformations, in particular when it sees a string argument. I am thinking I don’t want that.**
For one thing, Excel spreadsheets and word documents, I have noticed seem very good with many exotic characters and languages. Even my preferred earlier Office versions.
I think I, (and likely some others, possibly even some Microsoft people ) , may have been mislead sometimes in code development by apparent failings of passing characters due to failures in the VB Editor, and possibly then even overlooked that we have some (less commonly known and less commonly used) in most cases an API function version that it is regarded as a Unicode version.
I want to see if I can get a more Unicode API thing, and perhaps try at the same time to stop VBA messing with things so much…..My brain started one of its lateral thinking that rarely produce anything other than time and resource loss, but when they do 1 in 10 times produce something, it is usually more than worth the "lost" investment. This time it may have got something interesting and useful. Maybe not
This is just a provisional initial theory:
_ Behold: I have seen a API World, where direct ANSI Strings stuff f is often avoided, and that World is good. In this regard, String is anything at a API Declare line or related main code lines. This will include anything in API related VBA code lines that include As String
_In VBA, VB and possibly in other computer things, much involving string manipulations involves the handling not directly of the string itself, but of numbers: addresses of a Long type. This may be able to be exploited, when the string manipulation involves simple transferring: We can try to pass a pointer. This may effectively being something like a ByRef, when it is ByVal declared. This can help reduce certain conflicts associated with an actual explicit ByRef usage (We note possible parallels here to hacks to arguments in VBA Application.Run)
Having done many empirical measurements, I may have found a way to do Unicode API things, and that may to soime extent explain issues which initiated this Thread https://eileenslounge.com/viewtopic.php?f=30&t=41659
Here we go for now (and a lot to be added to and edited later
MessageBoxÆ function https://www.excelfox.com/forum/showt...ge4#post417897
Page 4
https://www.excelfox.com/forum/showt...PI-stuff/page4
https://www.excelfox.com/forum/showt...ll=1#post17896
https://www.excelfox.com/forum/showt...age3#post24934
#post17896 , Thread2824 , Post 31
Some notes related loosely to these forum posts
https://eileenslounge.com/viewtopic....322955#p322955 https://eileenslounge.com/viewtopic....323065#p323065
https://eileenslounge.com/viewtopic.php?f=30&t=41659
https://www.excelfox.com/forum/showt...dows-API/page3
https://www.excelfox.com/forum/showt...ll=1#post24934
https://www.excelfox.com/forum/showt...age3#post24934
post24934, Thread2989
Theory Ælstein
This is not necessarily a final answer, ( an unlikely to be the final word on the issue ), but this is an attempt to get the thing out, and keep it simmering while I take a break to write my Xmas cards. I have only looked in depth at less than a third of the book chapter
API App message box renewed experiments
Because I am new and still very flaky with API stuff, and because whilst on Panzer Schokolade a few years back, I got to know this API MessageboxAA very well, almost as well probably as a computer professional, exceptionally for Layman me, and as part of my recent API revision I revisited there.
So let me see if I can apply some of my initial findings and solution/ Theory suggestions to that.
In my research and experiments I went off in a tangent into very basic Unicode and ANSI/Ascii associated with VB strings and windoew32 API. In the long run, that may not be such a bad thing. The detailed discussions will be continued in more detail in this page 2 ++
Some (Vague for now) thoughts / Clues gleaned from a week of research
In no particular order, yet….
( These thoughts are given in more detail and better ordered in this page 2 ++ But it’s not finished yet. I have only looked in depth at less than a third of that book chapter
_ Things windows API certainly where and in many ways still are, or efficiently use, ANSI. This does cause issues with String things
_ The issues have been known about for over 20 years and we have things like strPtr extra made to help us, along with things like LongPtr which compliment it in some ways
_ a VBA string variable is a pointer to a VB pointer which final points to some form or part of the final string you actually want. Pointing to a pointer is possibly not so bad, whereas mixing up a pointer with a string is more deadly.
_** At some point in that book chapter , the author is hinting that his attempts to explain what is going on with existing VB documentation are his ideas and that other smart people were thinking something extra might be going on,
_ Somewhat less known as it perhaps should be, … for almost, if not all, API functions/ Sub routines, there is two versions, denoted by the last character in the name of A and W. Very briefly W can be thought of as meaning Unicode, A as ANSI/Ascii. The W things are not so new….
_ I noticed some strange things with types and type documentation. When considering string arguments, they seem to be wording things sometimes as if they are not talking about the string, but the pointer. I got some strange feeling that talking pointers is more low level, or more fundamental
_ I notice some things giving me the string problem issues were actually, (not always), sometimes in documentation or in working API stuff, declared As Any when String was either more intuitive or more often used.
My brain went off in some lateral thinking that rarely produce anything other than time and resource loss, but when they do 1 in 10 times produce something, it is usually more than worth the "lost" investment.
What did I decide to look at, and why
_ (For one thing the computer Unicode ANSI/Ascii very basics and History thereof. But maybe I may not have needed that if I was not so doubly ignorant in this area….)
_ I decided to look in strings, and string types, and string related pointer things.
_ I went off in an absurd diverse direction, thinking to get a way to do something involving a string by avoiding the string.
** As I went on, I started thinking that this original throw away thought of mine was not so bad after all …
…....... the simple answer is that ByVal and ByRef behaviour in windows API Declare lines is different, at least with As String.. ….
.... no one could figure out a more ordered logical well organised interface so the nearest looking combination of Declare line, call line and associated coding is used as an enigma type coding that sets off some particular back end coding to do something….
As I went on, I came to an idea, which with hindsight later searching, some people, al be it very few, but computer Profis never the less, also thought…. As soon as VBA sees a Declare line, it decides to do some Unicode to ANSI transformations, in particular when it sees a string argument. I am thinking I don’t want that.**
For one thing, Excel spreadsheets and word documents, I have noticed seem very good with many exotic characters and languages. Even my preferred earlier Office versions.
I think I, (and likely some others, possibly even some Microsoft people ) , may have been mislead sometimes in code development by apparent failings of passing characters due to failures in the VB Editor, and possibly then even overlooked that we have some (less commonly known and less commonly used) in most cases an API function version that it is regarded as a Unicode version.
I want to see if I can get a more Unicode API thing, and perhaps try at the same time to stop VBA messing with things so much…..My brain started one of its lateral thinking that rarely produce anything other than time and resource loss, but when they do 1 in 10 times produce something, it is usually more than worth the "lost" investment. This time it may have got something interesting and useful. Maybe not
This is just a provisional initial theory:
_ Behold: I have seen a API World, where direct ANSI Strings stuff f is often avoided, and that World is good. In this regard, String is anything at a API Declare line or related main code lines. This will include anything in API related VBA code lines that include As String
_In VBA, VB and possibly in other computer things, much involving string manipulations involves the handling not directly of the string itself, but of numbers: addresses of a Long type. This may be able to be exploited, when the string manipulation involves simple transferring: We can try to pass a pointer. This may effectively being something like a ByRef, when it is ByVal declared. This can help reduce certain conflicts associated with an actual explicit ByRef usage (We note possible parallels here to hacks to arguments in VBA Application.Run)
Having done many empirical measurements, I may have found a way to do Unicode API things, and that may to soime extent explain issues which initiated this Thread https://eileenslounge.com/viewtopic.php?f=30&t=41659
Here we go for now (and a lot to be added to and edited later
MessageBoxÆ function https://www.excelfox.com/forum/showt...ge4#post417897
MessageBoxÆ functionThe standard/ old Declareation looks like this
Declare Function MessageBox Lib "user32" Alias "MessageBoxA" (ByVal hwnd As Long, ByVal lpText As String, ByVal lpCaption As String, ByVal wType As Long) As Long
In this case disappointingly, the string arguments are given As String
However, take a look at the moderner documentation. ( That is usually in C language , but translating them is usually straight forward. )
int MessageBoxA(
[in, optional] HWND hWnd,
[in, optional] LPCSTR lpText,
[in, optional] LPCSTR lpCaption,
[in] UINT uType
);
int MessageBoxW(
[in, optional] HWND hWnd,
[in, optional] LPCWSTR lpText,
[in, optional] LPCWSTR lpCaption,
[in] UINT uType
);
Encouraging we see the pointer stuff is there on the two string arguments.
So
We is trying to get something that very likely does a Unicode thing, as much as possible. That is to say the provisional Ælstein Theory proclamates something like:
_ Behold: I have seen a API World, where direct ANSI Strings stuff is often avoided, or at least cropped back / restrained a bit, and that World is good. In this regard, String is anything at a API Declare line or related main code lines. This will include anything in API related VBA code lines that include As String
_In VBA, VB and possibly in other computer things, much involving string manipulations involves the handling not directly of the string itself, but of numbers: addresses of a Long type. This may be able to be exploited, when the string manipulation involves simple transferring: We can try to pass a pointer. This may effectively being something like a ByRef, when it is ByVal declared. This can help reduce certain conflicts associated with an actual explicit ByRef usage (We note possible parallels here to hacks to arguments in VBA Application.Run)
Worked example
I got an interesting word, or rather interesting first character of a word, in the first cell of a worksheet
Whilst Excel seems very good with displaying a massive amount of exotic high up the Unicode list characters, initially both the inbuilt VBA MsgBox and initially the API MessageBoxA don’t do at all well, not even if it's an ANSI that is probably not on my code page .
later.....
MessageBoxÆ functionThe standard/ old Declareation looks like this
Declare Function MessageBox Lib "user32" Alias "MessageBoxA" (ByVal hwnd As Long, ByVal lpText As String, ByVal lpCaption As String, ByVal wType As Long) As Long
In this case disappointingly, the string arguments are given As String
However, take a look at the moderner documentation. ( That is usually in C language , but translating them is usually straight forward. )
int MessageBoxA(
[in, optional] HWND hWnd,
[in, optional] LPCSTR lpText,
[in, optional] LPCSTR lpCaption,
[in] UINT uType
);
int MessageBoxW(
[in, optional] HWND hWnd,
[in, optional] LPCWSTR lpText,
[in, optional] LPCWSTR lpCaption,
[in] UINT uType
);
Encouraging we see the pointer stuff is there on the two string arguments.
So
We is trying to get something that very likely does a Unicode thing, as much as possible. That is to say the provisional Ælstein Theory proclamates something like:
_ Behold: I have seen a API World, where direct ANSI Strings stuff is often avoided, or at least cropped back / restrained a bit, and that World is good. In this regard, String is anything at a API Declare line or related main code lines. This will include anything in API related VBA code lines that include As String
_In VBA, VB and possibly in other computer things, much involving string manipulations involves the handling not directly of the string itself, but of numbers: addresses of a Long type. This may be able to be exploited, when the string manipulation involves simple transferring: We can try to pass a pointer. This may effectively being something like a ByRef, when it is ByVal declared. This can help reduce certain conflicts associated with an actual explicit ByRef usage (We note possible parallels here to hacks to arguments in VBA Application.Run)
Worked example
I got an interesting word, or rather interesting first character of a word, in the first cell of a worksheet
Whilst Excel seems very good with displaying a massive amount of exotic high up the Unicode list characters, initially both the inbuilt VBA MsgBox and initially the API MessageBoxA don’t do at all well, not even if it's an ANSI that is probably not on my code page .
later.....
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
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
Code in code tags from here:
http://www.excelfox.com/forum/showth...0699#post10699
Code: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
fname = ws.Range("A1")
mSubject = "Equipment" & " For " & Range("A1").Value
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'mBody = "Z:\2\Form\\Manufacturing Order.xlsm"
Dim Path As String
ws.Protect ("Equipment")
Path = "\\Equipment- Maint RecordsThai1.xlsm"
mBody = "<font size=""3"" face=""Calibri"">" & _
"Dear Team,<br><br>" & _
"Please open the file from below link and change the date 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("A1").Value & _
"<br><br>Best Regards," & _
"<br><br></font>"
With OutMail
.display
End With
signature = OutMail.body
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Code:Private Sub cmdNot_Click()
If Application.UserName = "Thai Nguyen" Then
Dim ws As Worksheet: Set ws = Sheets("Name")
Dim rng As Range, rng1 As Range
Dim fileName As String, fname As String
Let fname = ws.Range("B4")
Let mSubject = "Name"
Dim OutApp As Object, OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Dim Subject As String, signature As String, mBody As String, mailTo As String
'mBody = "copy you link path in here"
Let mBody = "<font size=""3"" face=""Calibri"">" & _
"Hi 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>" & _
ActiveWorkbook.Name & "</B> is created.<br>" & _
"Click on this link to open the file : " & _
"<A HREF=""file://" & ActiveWorkbook.FullName & """>Link to the file</A>" & _
"<br><br>Regards," & _
"<br><br>Thai Nguyen</font> "
OutMail.display
Let signature = OutMail.body
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With OutMail
'.To = "email"
If ws.Range("EU16") = True Then
Let mailTo = mailTo + "Thai Nguyen;"
Else
End If
If ws.Range("EU17") = True Then
mailTo = mailTo + "email"
End If
If ws.Range("EU18") = True Then
Let mailTo = mailTo + "email"
End If
If ws.Range("EU19") = True Then
Let mailTo = mailTo + "email"
End If
.To = mailTo
.CC = "Thai Nguyen"
.BCC = ""
.Subject = mSubject
'.body = "Hi 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")
ws.Protect ("Name")
ActiveWorkbook.Save
ActiveWorkbook.Close
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Else
MsgBox "You are not authorised to send BOM form, please check with BOM owner"
End If
End Sub
Code in code tags from here:
http://www.excelfox.com/forum/showth...0699#post10699
Code: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
fname = ws.Range("A1")
mSubject = "Equipment" & " For " & Range("A1").Value
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'mBody = "Z:\2\Form\\Manufacturing Order.xlsm"
Dim Path As String
ws.Protect ("Equipment")
Path = "\\Equipment- Maint RecordsThai1.xlsm"
mBody = "<font size=""3"" face=""Calibri"">" & _
"Dear Team,<br><br>" & _
"Please open the file from below link and change the date 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("A1").Value & _
"<br><br>Best Regards," & _
"<br><br></font>"
With OutMail
.display
End With
signature = OutMail.body
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Code:Private Sub cmdNot_Click()
If Application.UserName = "Thai Nguyen" Then
Dim ws As Worksheet: Set ws = Sheets("Name")
Dim rng As Range, rng1 As Range
Dim fileName As String, fname As String
Let fname = ws.Range("B4")
Let mSubject = "Name"
Dim OutApp As Object, OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Dim Subject As String, signature As String, mBody As String, mailTo As String
'mBody = "copy you link path in here"
Let mBody = "<font size=""3"" face=""Calibri"">" & _
"Hi 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>" & _
ActiveWorkbook.Name & "</B> is created.<br>" & _
"Click on this link to open the file : " & _
"<A HREF=""file://" & ActiveWorkbook.FullName & """>Link to the file</A>" & _
"<br><br>Regards," & _
"<br><br>Thai Nguyen</font> "
OutMail.display
Let signature = OutMail.body
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
With OutMail
'.To = "email"
If ws.Range("EU16") = True Then
Let mailTo = mailTo + "Thai Nguyen;"
Else
End If
If ws.Range("EU17") = True Then
mailTo = mailTo + "email"
End If
If ws.Range("EU18") = True Then
Let mailTo = mailTo + "email"
End If
If ws.Range("EU19") = True Then
Let mailTo = mailTo + "email"
End If
.To = mailTo
.CC = "Thai Nguyen"
.BCC = ""
.Subject = mSubject
'.body = "Hi 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")
ws.Protect ("Name")
ActiveWorkbook.Save
ActiveWorkbook.Close
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Else
MsgBox "You are not authorised to send BOM form, please check with BOM owner"
End If
End Sub
Share account for testing file access from a hyperlink in a received EMailIn 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
Share account for testing file access from a hyperlink in a received EMailIn 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
MessageBoxÆ functionThe standard/ old Declareation looks like this
Declare Function MessageBox Lib "user32" Alias "MessageBoxA" (ByVal hwnd As Long, ByVal lpText As String, ByVal lpCaption As String, ByVal wType As Long) As Long
In this case disappointingly, the string arguments are given As String
However, take a look at the moderner documentation. ( That is usually in C language , but translating them is usually straight forward. )
int MessageBoxA(
[in, optional] HWND hWnd,
[in, optional] LPCSTR lpText,
[in, optional] LPCSTR lpCaption,
[in] UINT uType
);
int MessageBoxW(
[in, optional] HWND hWnd,
[in, optional] LPCWSTR lpText,
[in, optional] LPCWSTR lpCaption,
[in] UINT uType
);
Encouraging we see the pointer stuff is there on the two string arguments.
So
We is trying to get something that very likely does a Unicode thing, as much as possible. That is to say the provisional Ælstein Theory proclamates something like:
_ Behold: I have seen a API World, where direct ANSI Strings stuff is often avoided, or at least cropped back / restrained a bit, and that World is good. In this regard, String is anything at a API Declare line or related main code lines. This will include anything in API related VBA code lines that include As String
_In VBA, VB and possibly in other computer things, much involving string manipulations involves the handling not directly of the string itself, but of numbers: addresses of a Long type. This may be able to be exploited, when the string manipulation involves simple transferring: We can try to pass a pointer. This may effectively being something like a ByRef, when it is ByVal declared. This can help reduce certain conflicts associated with an actual explicit ByRef usage (We note possible parallels here to hacks to arguments in VBA Application.Run)
Worked example
I got an interesting word, or rather interesting first character of a word, in the first cell of a worksheet
Whilst Excel seems very good with displaying a massive amount of exotic high up the Unicode list characters, initially both the inbuilt VBA MsgBox and initially the API MessageBoxA don’t do at all well, not even if it's an ANSI that is probably not on my code page .
later.....
Page 4
https://www.excelfox.com/forum/showt...PI-stuff/page4
https://www.excelfox.com/forum/showt...ll=1#post17896
https://www.excelfox.com/forum/showt...age3#post24934
#post17896 , Thread2824 , Post 31
Some notes related loosely to these forum posts
https://eileenslounge.com/viewtopic....322955#p322955 https://eileenslounge.com/viewtopic....323065#p323065
https://eileenslounge.com/viewtopic.php?f=30&t=41659
https://www.excelfox.com/forum/showt...dows-API/page3
https://www.excelfox.com/forum/showt...ll=1#post24934
https://www.excelfox.com/forum/showt...age3#post24934
post24934, Thread2989
Theory Ælstein
This is not necessarily a final answer, ( an unlikely to be the final word on the issue ), but this is an attempt to get the thing out, and keep it simmering while I take a break to write my Xmas cards. I have only looked in depth at less than a third of the book chapter
API App message box renewed experiments
Because I am new and still very flaky with API stuff, and because whilst on Panzer Schokolade a few years back, I got to know this API MessageboxAA very well, almost as well probably as a computer professional, exceptionally for Layman me, and as part of my recent API revision I revisited there.
So let me see if I can apply some of my initial findings and solution/ Theory suggestions to that.
In my research and experiments I went off in a tangent into very basic Unicode and ANSI/Ascii associated with VB strings and windoew32 API. In the long run, that may not be such a bad thing. The detailed discussions will be continued in more detail in this page 2 ++
Some (Vague for now) thoughts / Clues gleaned from a week of research
In no particular order, yet….
( These thoughts are given in more detail and better ordered in this page 2 ++ But it’s not finished yet. I have only looked in depth at less than a third of that book chapter
_ Things windows API certainly where and in many ways still are, or efficiently use, ANSI. This does cause issues with String things
_ The issues have been known about for over 20 years and we have things like strPtr extra made to help us, along with things like LongPtr which compliment it in some ways
_ a VBA string variable is a pointer to a VB pointer which final points to some form or part of the final string you actually want. Pointing to a pointer is possibly not so bad, whereas mixing up a pointer with a string is more deadly.
_** At some point in that book chapter , the author is hinting that his attempts to explain what is going on with existing VB documentation are his ideas and that other smart people were thinking something extra might be going on,
_ Somewhat less known as it perhaps should be, … for almost, if not all, API functions/ Sub routines, there is two versions, denoted by the last character in the name of A and W. Very briefly W can be thought of as meaning Unicode, A as ANSI/Ascii. The W things are not so new….
_ I noticed some strange things with types and type documentation. When considering string arguments, they seem to be wording things sometimes as if they are not talking about the string, but the pointer. I got some strange feeling that talking pointers is more low level, or more fundamental
_ I notice some things giving me the string problem issues were actually, (not always), sometimes in documentation or in working API stuff, declared As Any when String was either more intuitive or more often used.
My brain went off in some lateral thinking that rarely produce anything other than time and resource loss, but when they do 1 in 10 times produce something, it is usually more than worth the "lost" investment.
What did I decide to look at, and why
_ (For one thing the computer Unicode ANSI/Ascii very basics and History thereof. But maybe I may not have needed that if I was not so doubly ignorant in this area….)
_ I decided to look in strings, and string types, and string related pointer things.
_ I went off in an absurd diverse direction, thinking to get a way to do something involving a string by avoiding the string.
** As I went on, I started thinking that this original throw away thought of mine was not so bad after all …
…....... the simple answer is that ByVal and ByRef behaviour in windows API Declare lines is different, at least with As String.. ….
.... no one could figure out a more ordered logical well organised interface so the nearest looking combination of Declare line, call line and associated coding is used as an enigma type coding that sets off some particular back end coding to do something….
As I went on, I came to an idea, which with hindsight later searching, some people, al be it very few, but computer Profis never the less, also thought…. As soon as VBA sees a Declare line, it decides to do some Unicode to ANSI transformations, in particular when it sees a string argument. I am thinking I don’t want that.**
For one thing, Excel spreadsheets and word documents, I have noticed seem very good with many exotic characters and languages. Even my preferred earlier Office versions.
I think I, (and likely some others, possibly even some Microsoft people ) , may have been mislead sometimes in code development by apparent failings of passing characters due to failures in the VB Editor, and possibly then even overlooked that we have some (less commonly known and less commonly used) in most cases an API function version that it is regarded as a Unicode version.
I want to see if I can get a more Unicode API thing, and perhaps try at the same time to stop VBA messing with things so much…..My brain started one of its lateral thinking that rarely produce anything other than time and resource loss, but when they do 1 in 10 times produce something, it is usually more than worth the "lost" investment. This time it may have got something interesting and useful. Maybe not
This is just a provisional initial theory:
_ Behold: I have seen a API World, where direct ANSI Strings stuff f is often avoided, and that World is good. In this regard, String is anything at a API Declare line or related main code lines. This will include anything in API related VBA code lines that include As String
_In VBA, VB and possibly in other computer things, much involving string manipulations involves the handling not directly of the string itself, but of numbers: addresses of a Long type. This may be able to be exploited, when the string manipulation involves simple transferring: We can try to pass a pointer. This may effectively being something like a ByRef, when it is ByVal declared. This can help reduce certain conflicts associated with an actual explicit ByRef usage (We note possible parallels here to hacks to arguments in VBA Application.Run)
Having done many empirical measurements, I may have found a way to do Unicode API things, and that may to soime extent explain issues which initiated this Thread https://eileenslounge.com/viewtopic.php?f=30&t=41659
Here we go for now (and a lot to be added to and edited later
MessageBoxÆ function https://www.excelfox.com/forum/showt...ge4#post417897
Code for Thai .
Testing files( sent privately ) :Code:Option Explicit
Sub Sendfromexcellearninggmail()
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") = "excellearning12@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: ""excellearning12@gmail.com""" & _
"<br>Password: ""xxxxxxxxxxxxx""" & _
"<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 = "THai xxxxxxxxx"
'.CC = "xxxxxxxxxxxxx"
.BCC = ""
.from = """excellearning12@gmail.com"" <excellearning12@gmail.com>"
.Subject = "Sent from EMail address: excellearning12@gmail.com"
.htmlbody = strHTML
.Send ' Do it
End With ' 6a(ii) CreateObject("CDO.Message") ---my Created LCDCW Library
End Sub
I have also posted 3 files to you using our share g mail account , ExcelVBAExp@gmail.com
Please can you also try out the test…
Please do the following.
_1) Download all three files , and important: All must be stored in the same Folder.
( the three files are:
Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received Email.htm
Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls
Test File Thai to send EMail containing Hyperlinks to Files.xlsm )
_2) Open only file Test File Thai to send EMail containing Hyperlinks to Files.xlsm
Run code Sub Sendfromexcellearninggmail()
You should receive an Email similar to these:
Alan 5 Links in German Telekom.JPG : https://imgur.com/LeASbhf
Attachment 2079
Alan 5 Links in gmail.JPG : https://imgur.com/0sdyZEj
Attachment 2080
_3) Please click on the links.
_4) Please reply and tell me what happens when you click each link
Thanks
Alan
Code for Thai .
Testing files( sent privately ) :Code:Option Explicit
Sub Sendfromexcellearninggmail()
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") = "excellearning12@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: ""excellearning12@gmail.com""" & _
"<br>Password: ""xxxxxxxxxxxxx""" & _
"<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 = "THai xxxxxxxxx"
'.CC = "xxxxxxxxxxxxx"
.BCC = ""
.from = """excellearning12@gmail.com"" <excellearning12@gmail.com>"
.Subject = "Sent from EMail address: excellearning12@gmail.com"
.htmlbody = strHTML
.Send ' Do it
End With ' 6a(ii) CreateObject("CDO.Message") ---my Created LCDCW Library
End Sub
I have also posted 3 files to you using our share g mail account , ExcelVBAExp@gmail.com
Please can you also try out the test…
Please do the following.
_1) Download all three files , and important: All must be stored in the same Folder.
( the three files are:
Test file DOThtm to be stored on your computer to try to open with a Hyperlink in a received Email.htm
Empty test file DOTxls stored on your computer to try to open from Hyperlink in arrived Email.xls
Test File Thai to send EMail containing Hyperlinks to Files.xlsm )
_2) Open only file Test File Thai to send EMail containing Hyperlinks to Files.xlsm
Run code Sub Sendfromexcellearninggmail()
You should receive an Email similar to these:
Alan 5 Links in German Telekom.JPG : https://imgur.com/LeASbhf
Attachment 2079
Alan 5 Links in gmail.JPG : https://imgur.com/0sdyZEj
Attachment 2080
_3) Please click on the links.
_4) Please reply and tell me what happens when you click each link
Thanks
Alan
First test code for solution to this thread:
http://www.excelfox.com/forum/showth...le-or-two-tabs
( Run code Sub TestieCalls() )
Code:Option Explicit
Sub TestieCalls()
Call Testie(Worksheets("Sheet1"), Worksheets("Sheet2"))
End Sub
Sub Testie(Ws1 As Worksheet, Ws2 As Worksheet)
Rem 1 Worksheet data info
'1a capture data
'1a(i) last data rows
Dim lr1_1 As Long, Lr1_2 As Long, Lr2_1 As Long, Lr2_2 As Long, Lr1 As Long, lr2 As Long
Let lr1_1 = Ws1.Cells(Rows.Count, 1).End(xlUp).row
Let Lr1_2 = Ws1.Cells(Rows.Count, 2).End(xlUp).row: Lr2_1 = Ws2.Cells(Rows.Count, 1).End(xlUp).row: Lr2_2 = Ws2.Cells(Rows.Count, 2).End(xlUp).row
If lr1_1 > Lr1_2 Then
Let Lr1 = lr1_1
Else
Let Lr1 = Lr1_2
End If
Let lr2 = Lr2_2: If Lr2_1 > Lr2_2 Then Let lr2 = Lr2_1
'1a(ii) capture data into arrays in one go
Dim arrSht1() As Variant, arrSht2() As Variant
Let arrSht1() = Ws1.Range("A1:B" & Lr1 & "").Value
Let arrSht2() = Ws2.Range("A1:B" & lr2 & "").Value
Rem 2 arrays for check and output
Dim arrSht1b() As String, arrOut() As String
'2a size arrays to that of sheet 2 data
ReDim arrSht1b(1 To UBound(arrSht2(), 1), 1 To UBound(arrSht2(), 2))
ReDim arrOut(1 To UBound(arrSht2(), 1), 1 To UBound(arrSht2(), 2))
'2b fill modified sheet 1 array, arrSht1b() , initially with sheet 1 data
Dim Cnt As Long
For Cnt = 1 To UBound(arrSht1(), 1) Step 1
Let arrSht1b(Cnt, 1) = arrSht1(Cnt, 1): Let arrSht1b(Cnt, 2) = arrSht1(Cnt, 2)
Next Cnt
Rem 3 main loop ' == Start main loop ==========
For Cnt = 1 To UBound(arrSht2(), 1) - 1 Step 1 ' Counting at each row
Dim DifCnt As Long 'Count of different cells
' Condition check
If (arrSht2(Cnt, 1) <> arrSht1b(Cnt, 1) Or arrSht2(Cnt, 2) <> arrSht1b(Cnt, 2)) And (arrSht2(Cnt + 1, 1) = arrSht1b(Cnt + 1, 1) And arrSht2(Cnt + 1, 2) = arrSht1b(Cnt + 1, 2)) Then ' condition for changed row but next row is as previous : row had data changed, but a row was not inserted
Let arrSht1b(Cnt, 1) = arrSht2(Cnt, 1): arrSht1b(Cnt, 2) = arrSht2(Cnt, 2) 'change any changed cell
If arrSht1b(Cnt, 1) <> arrSht1(Cnt, 1) Then
Let arrOut(Cnt, 1) = arrSht1b(Cnt, 1) & " <> " & arrSht1(Cnt, 1)
Let DifCnt = DifCnt + 1
Else: End If
If arrSht1b(Cnt, 2) <> arrSht1(Cnt, 2) Then
Let arrOut(Cnt, 2) = arrSht1b(Cnt, 2) & " <> " & arrSht1(Cnt, 2)
Let DifCnt = DifCnt + 1
Else: End If
' Condition check
ElseIf ((arrSht2(Cnt, 1) <> arrSht1b(Cnt, 1) Or arrSht2(Cnt, 2) <> arrSht1b(Cnt, 2)) And (arrSht2(Cnt + 1, 1) <> arrSht1b(Cnt + 1, 1) Or arrSht2(Cnt + 1, 2) <> arrSht1b(Cnt + 1, 2))) Then ' main condition suggesting added new row
Dim AdedRows As Long: Let AdedRows = AdedRows + 1
'3b we need to shift all data down to allow space for new row in arrSht2()
Dim CntIn As Long
For CntIn = (UBound(arrSht2(), 1) - 1) To Cnt Step -1 'loop for all but last from this row
Let arrSht1b(CntIn + 1, 1) = arrSht1b(CntIn, 1): arrSht1b(CntIn + 1, 2) = arrSht1b(CntIn, 2) ' This effectively pulls up each row by one
Next CntIn
'3c add the new data to the modified array, Let arrSht1b()
Let arrSht1b(Cnt, 1) = arrSht2(Cnt, 1): arrSht1b(Cnt, 2) = arrSht2(Cnt, 2)
If arrSht1b(Cnt, 1) = "" Then arrSht1b(Cnt, 1) = " " ' Just to make final output more neat
If arrSht1b(Cnt, 2) = "" Then arrSht1b(Cnt, 2) = " "
'3d add info to the output array
If Cnt > UBound(arrSht1(), 1) Then ' case of new lines
Let arrOut(Cnt, 1) = "An new extra line contains " & arrSht1b(Cnt, 1): arrOut(Cnt, 2) = "An new extra line contains " & arrSht1b(Cnt, 2)
Else
If arrSht1b(Cnt, 1) <> arrSht1(Cnt, 1) Then
Let arrOut(Cnt, 1) = arrSht1b(Cnt, 1) & " <> " & arrSht1(Cnt, 1)
Let DifCnt = DifCnt + 1
Else: End If
If arrSht1b(Cnt, 2) <> arrSht1(Cnt, 2) Then
Let arrOut(Cnt, 2) = arrSht1b(Cnt, 2) & " <> " & arrSht1(Cnt, 2)
Let DifCnt = DifCnt + 1
Else: End If
End If
'
Let Cnt = Cnt + 1 ' we need to skip the next row as that was just effectively added so we are done with it
Else ' row has not been added here
End If
Next Cnt ' ========= End main loop ==========
Rem 4 last row may be new
If arrSht2(lr2, 1) <> arrSht1(Lr1, 1) Or arrSht2(lr2, 2) <> arrSht1(Lr1, 2) Then ' either cell in last row is different
If arrSht2(lr2, 1) <> arrSht1(Lr1, 1) Then
Let arrOut(lr2, 1) = arrSht2(lr2, 1) & " on last row is new"
Let DifCnt = DifCnt + 1
Else: End If
If arrSht2(lr2, 2) <> arrSht1(Lr1, 2) Then
Let arrOut(lr2, 2) = arrSht2(lr2, 2) & " on last row is new"
Let DifCnt = DifCnt + 1
Else: End If
Else 'last row on sheet2 is as on sheet1
End If
Rem 5 Output in new file For testing purposes, I give the output in a third worksheet, Tabelle3
Dim Ws3 As Worksheet: Set Ws3 = ThisWorkbook.Worksheets("Tabelle3")
Ws3.Cells.ClearContents
Let Ws3.Range("A1:B1").Value = "Sheet1": Ws3.Range("C1:D1").Value = "Test Output": Ws3.Range("E1:F1").Value = "Sheet2"
Let Ws3.Range("A2").Resize(UBound(arrSht1(), 1), UBound(arrSht1(), 2)).Value = arrSht1()
Let Ws3.Range("C2").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut()
Let Ws3.Range("E2").Resize(UBound(arrSht2(), 1), UBound(arrSht2(), 2)).Value = arrSht2()
Ws3.Columns.AutoFit
Rem 6 MsgBoox output
MsgBox Prompt:="inserted lines is " & AdedRows & vbCrLf & "Changed cells is " & DifCnt
End Sub
First test code for solution to this thread:
http://www.excelfox.com/forum/showth...le-or-two-tabs
( Run code Sub TestieCalls() )
Code:Option Explicit
Sub TestieCalls()
Call Testie(Worksheets("Sheet1"), Worksheets("Sheet2"))
End Sub
Sub Testie(Ws1 As Worksheet, Ws2 As Worksheet)
Rem 1 Worksheet data info
'1a capture data
'1a(i) last data rows
Dim lr1_1 As Long, Lr1_2 As Long, Lr2_1 As Long, Lr2_2 As Long, Lr1 As Long, lr2 As Long
Let lr1_1 = Ws1.Cells(Rows.Count, 1).End(xlUp).row
Let Lr1_2 = Ws1.Cells(Rows.Count, 2).End(xlUp).row: Lr2_1 = Ws2.Cells(Rows.Count, 1).End(xlUp).row: Lr2_2 = Ws2.Cells(Rows.Count, 2).End(xlUp).row
If lr1_1 > Lr1_2 Then
Let Lr1 = lr1_1
Else
Let Lr1 = Lr1_2
End If
Let lr2 = Lr2_2: If Lr2_1 > Lr2_2 Then Let lr2 = Lr2_1
'1a(ii) capture data into arrays in one go
Dim arrSht1() As Variant, arrSht2() As Variant
Let arrSht1() = Ws1.Range("A1:B" & Lr1 & "").Value
Let arrSht2() = Ws2.Range("A1:B" & lr2 & "").Value
Rem 2 arrays for check and output
Dim arrSht1b() As String, arrOut() As String
'2a size arrays to that of sheet 2 data
ReDim arrSht1b(1 To UBound(arrSht2(), 1), 1 To UBound(arrSht2(), 2))
ReDim arrOut(1 To UBound(arrSht2(), 1), 1 To UBound(arrSht2(), 2))
'2b fill modified sheet 1 array, arrSht1b() , initially with sheet 1 data
Dim Cnt As Long
For Cnt = 1 To UBound(arrSht1(), 1) Step 1
Let arrSht1b(Cnt, 1) = arrSht1(Cnt, 1): Let arrSht1b(Cnt, 2) = arrSht1(Cnt, 2)
Next Cnt
Rem 3 main loop ' == Start main loop ==========
For Cnt = 1 To UBound(arrSht2(), 1) - 1 Step 1 ' Counting at each row
Dim DifCnt As Long 'Count of different cells
' Condition check
If (arrSht2(Cnt, 1) <> arrSht1b(Cnt, 1) Or arrSht2(Cnt, 2) <> arrSht1b(Cnt, 2)) And (arrSht2(Cnt + 1, 1) = arrSht1b(Cnt + 1, 1) And arrSht2(Cnt + 1, 2) = arrSht1b(Cnt + 1, 2)) Then ' condition for changed row but next row is as previous : row had data changed, but a row was not inserted
Let arrSht1b(Cnt, 1) = arrSht2(Cnt, 1): arrSht1b(Cnt, 2) = arrSht2(Cnt, 2) 'change any changed cell
If arrSht1b(Cnt, 1) <> arrSht1(Cnt, 1) Then
Let arrOut(Cnt, 1) = arrSht1b(Cnt, 1) & " <> " & arrSht1(Cnt, 1)
Let DifCnt = DifCnt + 1
Else: End If
If arrSht1b(Cnt, 2) <> arrSht1(Cnt, 2) Then
Let arrOut(Cnt, 2) = arrSht1b(Cnt, 2) & " <> " & arrSht1(Cnt, 2)
Let DifCnt = DifCnt + 1
Else: End If
' Condition check
ElseIf ((arrSht2(Cnt, 1) <> arrSht1b(Cnt, 1) Or arrSht2(Cnt, 2) <> arrSht1b(Cnt, 2)) And (arrSht2(Cnt + 1, 1) <> arrSht1b(Cnt + 1, 1) Or arrSht2(Cnt + 1, 2) <> arrSht1b(Cnt + 1, 2))) Then ' main condition suggesting added new row
Dim AdedRows As Long: Let AdedRows = AdedRows + 1
'3b we need to shift all data down to allow space for new row in arrSht2()
Dim CntIn As Long
For CntIn = (UBound(arrSht2(), 1) - 1) To Cnt Step -1 'loop for all but last from this row
Let arrSht1b(CntIn + 1, 1) = arrSht1b(CntIn, 1): arrSht1b(CntIn + 1, 2) = arrSht1b(CntIn, 2) ' This effectively pulls up each row by one
Next CntIn
'3c add the new data to the modified array, Let arrSht1b()
Let arrSht1b(Cnt, 1) = arrSht2(Cnt, 1): arrSht1b(Cnt, 2) = arrSht2(Cnt, 2)
If arrSht1b(Cnt, 1) = "" Then arrSht1b(Cnt, 1) = " " ' Just to make final output more neat
If arrSht1b(Cnt, 2) = "" Then arrSht1b(Cnt, 2) = " "
'3d add info to the output array
If Cnt > UBound(arrSht1(), 1) Then ' case of new lines
Let arrOut(Cnt, 1) = "An new extra line contains " & arrSht1b(Cnt, 1): arrOut(Cnt, 2) = "An new extra line contains " & arrSht1b(Cnt, 2)
Else
If arrSht1b(Cnt, 1) <> arrSht1(Cnt, 1) Then
Let arrOut(Cnt, 1) = arrSht1b(Cnt, 1) & " <> " & arrSht1(Cnt, 1)
Let DifCnt = DifCnt + 1
Else: End If
If arrSht1b(Cnt, 2) <> arrSht1(Cnt, 2) Then
Let arrOut(Cnt, 2) = arrSht1b(Cnt, 2) & " <> " & arrSht1(Cnt, 2)
Let DifCnt = DifCnt + 1
Else: End If
End If
'
Let Cnt = Cnt + 1 ' we need to skip the next row as that was just effectively added so we are done with it
Else ' row has not been added here
End If
Next Cnt ' ========= End main loop ==========
Rem 4 last row may be new
If arrSht2(lr2, 1) <> arrSht1(Lr1, 1) Or arrSht2(lr2, 2) <> arrSht1(Lr1, 2) Then ' either cell in last row is different
If arrSht2(lr2, 1) <> arrSht1(Lr1, 1) Then
Let arrOut(lr2, 1) = arrSht2(lr2, 1) & " on last row is new"
Let DifCnt = DifCnt + 1
Else: End If
If arrSht2(lr2, 2) <> arrSht1(Lr1, 2) Then
Let arrOut(lr2, 2) = arrSht2(lr2, 2) & " on last row is new"
Let DifCnt = DifCnt + 1
Else: End If
Else 'last row on sheet2 is as on sheet1
End If
Rem 5 Output in new file For testing purposes, I give the output in a third worksheet, Tabelle3
Dim Ws3 As Worksheet: Set Ws3 = ThisWorkbook.Worksheets("Tabelle3")
Ws3.Cells.ClearContents
Let Ws3.Range("A1:B1").Value = "Sheet1": Ws3.Range("C1:D1").Value = "Test Output": Ws3.Range("E1:F1").Value = "Sheet2"
Let Ws3.Range("A2").Resize(UBound(arrSht1(), 1), UBound(arrSht1(), 2)).Value = arrSht1()
Let Ws3.Range("C2").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut()
Let Ws3.Range("E2").Resize(UBound(arrSht2(), 1), UBound(arrSht2(), 2)).Value = arrSht2()
Ws3.Columns.AutoFit
Rem 6 MsgBoox output
MsgBox Prompt:="inserted lines is " & AdedRows & vbCrLf & "Changed cells is " & DifCnt
End Sub
Test runs from codeCode:Sub TestyCalls() ' http://www.excelfox.com/forum/showthread.php/2277-Vba-button-Comparison-data-between-two-file-or-two-tabs?p=10741#post10741
' Call Testie(Worksheets("Sheet1"), Worksheets("Sheet2"))
Call Testy(Worksheets("Sheet1"), Worksheets("Sheet2"))
End Sub
For support of this excelfox Thread:
http://www.excelfox.com/forum/showth...0741#post10741
Using Excel 2007 32 bit
Worksheet: Tabelle3
Sheet1 Sheet1 Test Output Test Output Sheet2 Sheet2 Customer Assembly Customer Assembly Nu Torque Nu Torque Blu Origin Spaceship Blu Origin Spaceship Jet Blue21 ABC Alaska Toyota Supra Emirate ABC12345 Jet Blue21 ABC Alaska Toyota Supra Emirate ABC12345 Dup 2 of Toyota Dup 2 of Supra Toyota Supra Dup 2 of Emirate Dup 2 of ABC12345 Emirate ABC12345 Spaceship 12 Spaceship
Using Excel 2007 32 bit
Worksheet: Tabelle3
Sheet1 Sheet1 Test Output Test Output Sheet2 Sheet2 Customer Assembly Customer Assembly Nu Torque Nu Torque Blu Origin Spaceship Alaska Jet Blue21 ABC Excel123 HiThai Excel123 HiThai Alaska Blu Origin Spaceship Toyota Supra Emirate ABC12345 Emirate ABC12345 Jet Blue21 ABC Toyota Supra
Test runs from codeCode:Sub TestyCalls() ' http://www.excelfox.com/forum/showthread.php/2277-Vba-button-Comparison-data-between-two-file-or-two-tabs?p=10741#post10741
' Call Testie(Worksheets("Sheet1"), Worksheets("Sheet2"))
Call Testy(Worksheets("Sheet1"), Worksheets("Sheet2"))
End Sub
For support of this excelfox Thread:
http://www.excelfox.com/forum/showth...0741#post10741
Using Excel 2007 32 bit
Worksheet: Tabelle3
Sheet1 Sheet1 Test Output Test Output Sheet2 Sheet2 Customer Assembly Customer Assembly Nu Torque Nu Torque Blu Origin Spaceship Blu Origin Spaceship Jet Blue21 ABC Alaska Toyota Supra Emirate ABC12345 Jet Blue21 ABC Alaska Toyota Supra Emirate ABC12345 Dup 2 of Toyota Dup 2 of Supra Toyota Supra Dup 2 of Emirate Dup 2 of ABC12345 Emirate ABC12345 Spaceship 12 Spaceship
Using Excel 2007 32 bit
Worksheet: Tabelle3
Sheet1 Sheet1 Test Output Test Output Sheet2 Sheet2 Customer Assembly Customer Assembly Nu Torque Nu Torque Blu Origin Spaceship Alaska Jet Blue21 ABC Excel123 HiThai Excel123 HiThai Alaska Blu Origin Spaceship Toyota Supra Emirate ABC12345 Emirate ABC12345 Jet Blue21 ABC Toyota Supra
Code in support of this Post
http://www.excelfox.com/forum/showth...0741#post10741
Code:Sub TestyCalls() ' http://www.excelfox.com/forum/showthread.php/2277-Vba-button-Comparison-data-between-two-file-or-two-tabs?p=10741#post10741
' Call Testie(Worksheets("Sheet1"), Worksheets("Sheet2"))
Call Testy(Worksheets("Sheet1"), Worksheets("Sheet2"))
End Sub
Sub Testy(Ws1 As Worksheet, Ws2 As Worksheet)
Rem 1 Worksheet data info
'1a capture data
'1a(i) last data rows
Dim lr1_1 As Long, Lr1_2 As Long, Lr2_1 As Long, Lr2_2 As Long, Lr1 As Long, lr2 As Long
Let lr1_1 = Ws1.Cells(Rows.Count, 1).End(xlUp).row
Let Lr1_2 = Ws1.Cells(Rows.Count, 2).End(xlUp).row: Lr2_1 = Ws2.Cells(Rows.Count, 1).End(xlUp).row: Lr2_2 = Ws2.Cells(Rows.Count, 2).End(xlUp).row
If lr1_1 > Lr1_2 Then
Let Lr1 = lr1_1
Else
Let Lr1 = Lr1_2
End If
Let lr2 = Lr2_2: If Lr2_1 > Lr2_2 Then Let lr2 = Lr2_1
'1a(ii) capture data into arrays in one go
Dim arrSht1() As Variant, arrSht2() As Variant
Let arrSht1() = Ws1.Range("A1:B" & Lr1 & "").Value
Let arrSht2() = Ws2.Range("A1:B" & lr2 & "").Value
Rem 2 arrays for check and output
Dim arrSht1b() As String, arrOut() As String, arrSht1Chk() As String, arrSht2Chk() As String
'2a size arrays to that of sheet 2 data
' ReDim arrSht1b(1 To UBound(arrSht2(), 1), 1 To UBound(arrSht2(), 2))
ReDim arrOut(1 To UBound(arrSht2(), 1), 1 To UBound(arrSht2(), 2))
ReDim arrSht1Chk(1 To UBound(arrSht1(), 1)): ReDim arrSht2Chk(1 To UBound(arrSht2(), 1)) ' Arrays for concatenated data
'2b make check arrays fill modified sheet 1 array, arrSht1b() , initially with sheet 1 data
Dim Cnt As Long
For Cnt = 1 To UBound(arrSht1(), 1) Step 1
' Let arrSht1b(Cnt, 1) = arrSht1(Cnt, 1): Let arrSht1b(Cnt, 2) = arrSht1(Cnt, 2)
Let arrSht1Chk(Cnt) = arrSht1(Cnt, 1) & "|" & arrSht1(Cnt, 2)
Next Cnt
For Cnt = 1 To UBound(arrSht2(), 1) Step 1
Let arrSht2Chk(Cnt) = arrSht2(Cnt, 1) & "|" & arrSht2(Cnt, 2)
Next Cnt
'2c make contents of array for output initially all dat from Sheet2
For Cnt = 1 To UBound(arrSht2(), 1) Step 1
Let arrOut(Cnt, 1) = CStr(arrSht2(Cnt, 1)): arrOut(Cnt, 2) = CStr(arrSht2(Cnt, 2))
Next Cnt
Rem 3 main loop ' == Start Main loop ================
For Cnt = 1 To UBound(arrSht1(), 1) Step 1 ' Counting at each row of Sheet2
Dim MtchRes As Variant
Let MtchRes = Application.Match(arrSht1Chk(Cnt), arrSht2Chk(), 0)
'3a action whilst match is found --Inner Loop------
Do While Not IsError(MtchRes) ' The 3a Loop
Dim DupyCnt As Long: Let DupyCnt = DupyCnt + 1
If DupyCnt > 1 Then
Let arrOut(MtchRes, 1) = "Dup " & DupyCnt & " of " & arrOut(MtchRes, 1): arrOut(MtchRes, 2) = "Dup " & DupyCnt & " of " & arrOut(MtchRes, 2)
Else
Let arrOut(MtchRes, 1) = "": arrOut(MtchRes, 2) = "" ' remove the found data from array for output so that next line can look again for a possible duplicate
End If
Let arrSht2Chk(MtchRes) = "" ' remove entry in check array so that next line can look for possible duplicate
Let MtchRes = Application.Match(arrSht1Chk(Cnt), arrSht2Chk(), 0)
Loop ' ----------------------------------------
Let DupyCnt = 0 ' reset the Duplicated data count for next row of data in Sheet1
Next Cnt ' ========= End main loop ================= effectively we go to next row of data in Sheet1 with this line
Rem 5 Output in new file For testing purposes, I give the output in a third worksheet, Tabelle3
Dim Ws3 As Worksheet: Set Ws3 = ThisWorkbook.Worksheets("Tabelle3")
Ws3.Cells.ClearContents
Let Ws3.Range("A1:B1").Value = "Sheet1": Ws3.Range("C1:D1").Value = "Test Output": Ws3.Range("E1:F1").Value = "Sheet2"
Let Ws3.Range("A2").Resize(UBound(arrSht1(), 1), UBound(arrSht1(), 2)).Value = arrSht1()
Let Ws3.Range("C2").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut()
Let Ws3.Range("E2").Resize(UBound(arrSht2(), 1), UBound(arrSht2(), 2)).Value = arrSht2()
Ws3.Columns.AutoFit
Rem 6 MsgBox output
' MsgBox Prompt:="Inserted lines is " & AdedRows & vbCrLf & "Changed cells is " & DifCnt
End Sub
Code in support of this Post
http://www.excelfox.com/forum/showth...0741#post10741
Code:Sub TestyCalls() ' http://www.excelfox.com/forum/showthread.php/2277-Vba-button-Comparison-data-between-two-file-or-two-tabs?p=10741#post10741
' Call Testie(Worksheets("Sheet1"), Worksheets("Sheet2"))
Call Testy(Worksheets("Sheet1"), Worksheets("Sheet2"))
End Sub
Sub Testy(Ws1 As Worksheet, Ws2 As Worksheet)
Rem 1 Worksheet data info
'1a capture data
'1a(i) last data rows
Dim lr1_1 As Long, Lr1_2 As Long, Lr2_1 As Long, Lr2_2 As Long, Lr1 As Long, lr2 As Long
Let lr1_1 = Ws1.Cells(Rows.Count, 1).End(xlUp).row
Let Lr1_2 = Ws1.Cells(Rows.Count, 2).End(xlUp).row: Lr2_1 = Ws2.Cells(Rows.Count, 1).End(xlUp).row: Lr2_2 = Ws2.Cells(Rows.Count, 2).End(xlUp).row
If lr1_1 > Lr1_2 Then
Let Lr1 = lr1_1
Else
Let Lr1 = Lr1_2
End If
Let lr2 = Lr2_2: If Lr2_1 > Lr2_2 Then Let lr2 = Lr2_1
'1a(ii) capture data into arrays in one go
Dim arrSht1() As Variant, arrSht2() As Variant
Let arrSht1() = Ws1.Range("A1:B" & Lr1 & "").Value
Let arrSht2() = Ws2.Range("A1:B" & lr2 & "").Value
Rem 2 arrays for check and output
Dim arrSht1b() As String, arrOut() As String, arrSht1Chk() As String, arrSht2Chk() As String
'2a size arrays to that of sheet 2 data
' ReDim arrSht1b(1 To UBound(arrSht2(), 1), 1 To UBound(arrSht2(), 2))
ReDim arrOut(1 To UBound(arrSht2(), 1), 1 To UBound(arrSht2(), 2))
ReDim arrSht1Chk(1 To UBound(arrSht1(), 1)): ReDim arrSht2Chk(1 To UBound(arrSht2(), 1)) ' Arrays for concatenated data
'2b make check arrays fill modified sheet 1 array, arrSht1b() , initially with sheet 1 data
Dim Cnt As Long
For Cnt = 1 To UBound(arrSht1(), 1) Step 1
' Let arrSht1b(Cnt, 1) = arrSht1(Cnt, 1): Let arrSht1b(Cnt, 2) = arrSht1(Cnt, 2)
Let arrSht1Chk(Cnt) = arrSht1(Cnt, 1) & "|" & arrSht1(Cnt, 2)
Next Cnt
For Cnt = 1 To UBound(arrSht2(), 1) Step 1
Let arrSht2Chk(Cnt) = arrSht2(Cnt, 1) & "|" & arrSht2(Cnt, 2)
Next Cnt
'2c make contents of array for output initially all dat from Sheet2
For Cnt = 1 To UBound(arrSht2(), 1) Step 1
Let arrOut(Cnt, 1) = CStr(arrSht2(Cnt, 1)): arrOut(Cnt, 2) = CStr(arrSht2(Cnt, 2))
Next Cnt
Rem 3 main loop ' == Start Main loop ================
For Cnt = 1 To UBound(arrSht1(), 1) Step 1 ' Counting at each row of Sheet2
Dim MtchRes As Variant
Let MtchRes = Application.Match(arrSht1Chk(Cnt), arrSht2Chk(), 0)
'3a action whilst match is found --Inner Loop------
Do While Not IsError(MtchRes) ' The 3a Loop
Dim DupyCnt As Long: Let DupyCnt = DupyCnt + 1
If DupyCnt > 1 Then
Let arrOut(MtchRes, 1) = "Dup " & DupyCnt & " of " & arrOut(MtchRes, 1): arrOut(MtchRes, 2) = "Dup " & DupyCnt & " of " & arrOut(MtchRes, 2)
Else
Let arrOut(MtchRes, 1) = "": arrOut(MtchRes, 2) = "" ' remove the found data from array for output so that next line can look again for a possible duplicate
End If
Let arrSht2Chk(MtchRes) = "" ' remove entry in check array so that next line can look for possible duplicate
Let MtchRes = Application.Match(arrSht1Chk(Cnt), arrSht2Chk(), 0)
Loop ' ----------------------------------------
Let DupyCnt = 0 ' reset the Duplicated data count for next row of data in Sheet1
Next Cnt ' ========= End main loop ================= effectively we go to next row of data in Sheet1 with this line
Rem 5 Output in new file For testing purposes, I give the output in a third worksheet, Tabelle3
Dim Ws3 As Worksheet: Set Ws3 = ThisWorkbook.Worksheets("Tabelle3")
Ws3.Cells.ClearContents
Let Ws3.Range("A1:B1").Value = "Sheet1": Ws3.Range("C1:D1").Value = "Test Output": Ws3.Range("E1:F1").Value = "Sheet2"
Let Ws3.Range("A2").Resize(UBound(arrSht1(), 1), UBound(arrSht1(), 2)).Value = arrSht1()
Let Ws3.Range("C2").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut()
Let Ws3.Range("E2").Resize(UBound(arrSht2(), 1), UBound(arrSht2(), 2)).Value = arrSht2()
Ws3.Columns.AutoFit
Rem 6 MsgBox output
' MsgBox Prompt:="Inserted lines is " & AdedRows & vbCrLf & "Changed cells is " & DifCnt
End Sub
djvsjj
later
spare for now
( this is original post 11279 https://excelfox.com/forum/showthrea...ll=1#post11279
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11279&viewfull=1#post11279 )
Attaching a File to a Thread post at excelfox1 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 https://imgur.com/owxsBy5
http://i.imgur.com/owxsBy5.jpg
Or
_1_(i) _b) Scroll down and select manage attachments
a)PaperClipIcon or b)ManageAttachmants.JPG : http://i.imgur.com/3VAkJO9.jpg https...ur.com/YFEUDUh
http://i.imgur.com/3VAkJO9.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 2103Attachment 3595
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
( this is copied post 15589 https://excelfox.com/forum/showthrea...ll=1#post15589
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11279&viewfull=1#post15589
Images may work better in this Post if you are using an older Operating System
)
Attaching a File to a Thread post at excelfox1 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 https://imgur.com/owxsBy5
http://i.imgur.com/owxsBy5.jpg
Or
_1_(i) _b) Scroll down and select manage attachments
a)PaperClipIcon or b)ManageAttachmants.JPG : http://i.imgur.com/3VAkJO9.jpg <br /...om/YFEUDUh.jpg
a)PaperClipIcon: http://i.imgur.com/3VAkJO9.jpg
b)ManageAttachmants.JPG: http://i.imgur.com/YFEUDUh.jpg
(ii) For a Reply or when Editing an existing post
_ Hit Reply button or Edit Post Button
Reply or Edit Post.JPG : http://i.imgur.com/Bm1Zy6T.jpg
_ Hit Go Advanced button
GoAdvancedReplyWindow.JPG , GoAdvanced1.JPG : http://i.imgur.com/QLhHBGl.jpg , http://i.imgur.com/WXoKcoF.jpg
_ Scroll down and select manage attachments
Scroll down to Hit manage Attachments.JPG : http://i.imgur.com/uNkr6Eq.jpg
Finally you should see the Manage Attachments Window dialogue box
Manage Attachments Window dialogue box.JPG : http://i.imgur.com/BFFUIuG.jpg
Attachment 2103Attachment 3594
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 : http://i.imgur.com/hIdo0Av.jpg
_ SelectFiles.JPG : http://i.imgur.com/9XZJuig.jpg
_ UploadFiles5.JPG : http://i.imgur.com/f0PXtVA.jpg
_ Done6.JPG : http://i.imgur.com/a6oFeIQ.jpg
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 http://i.imgur.com/S3uneWf.jpg , http://i.imgur.com/gUFHcBp.jpg
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
( this is original post 11279 https://excelfox.com/forum/showthrea...ll=1#post11279
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11279&viewfull=1#post11279 )
Attaching a File to a Thread post at excelfox1 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 https://imgur.com/owxsBy5
http://i.imgur.com/owxsBy5.jpg
Or
_1_(i) _b) Scroll down and select manage attachments
a)PaperClipIcon or b)ManageAttachmants.JPG : http://i.imgur.com/3VAkJO9.jpg https...ur.com/YFEUDUh
http://i.imgur.com/3VAkJO9.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 2103Attachment 3595
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
( this is copied post 15589 https://excelfox.com/forum/showthrea...ll=1#post15589
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11279&viewfull=1#post15589
Images may work better in this Post if you are using an older Operating System
)
Attaching a File to a Thread post at excelfox1 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 https://imgur.com/owxsBy5
http://i.imgur.com/owxsBy5.jpg
Or
_1_(i) _b) Scroll down and select manage attachments
a)PaperClipIcon or b)ManageAttachmants.JPG : http://i.imgur.com/3VAkJO9.jpg <br /...om/YFEUDUh.jpg
a)PaperClipIcon: http://i.imgur.com/3VAkJO9.jpg
b)ManageAttachmants.JPG: http://i.imgur.com/YFEUDUh.jpg
(ii) For a Reply or when Editing an existing post
_ Hit Reply button or Edit Post Button
Reply or Edit Post.JPG : http://i.imgur.com/Bm1Zy6T.jpg
_ Hit Go Advanced button
GoAdvancedReplyWindow.JPG , GoAdvanced1.JPG : http://i.imgur.com/QLhHBGl.jpg , http://i.imgur.com/WXoKcoF.jpg
_ Scroll down and select manage attachments
Scroll down to Hit manage Attachments.JPG : http://i.imgur.com/uNkr6Eq.jpg
Finally you should see the Manage Attachments Window dialogue box
Manage Attachments Window dialogue box.JPG : http://i.imgur.com/BFFUIuG.jpg
Attachment 2103Attachment 3594
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 : http://i.imgur.com/hIdo0Av.jpg
_ SelectFiles.JPG : http://i.imgur.com/9XZJuig.jpg
_ UploadFiles5.JPG : http://i.imgur.com/f0PXtVA.jpg
_ Done6.JPG : http://i.imgur.com/a6oFeIQ.jpg
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 http://i.imgur.com/S3uneWf.jpg , http://i.imgur.com/gUFHcBp.jpg
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
In support of this Post
http://www.excelfox.com/forum/showth...0771#post10771
Using Excel 2007 32 bit
Worksheet: Result Wanted
Row\Col Original Original Original Original Original Original Test Output Test Output Test Output Test Output Test Output NEW NEW NEW NEW NEW NEW Assembly #: Assembly Name: Assembly #: Assembly Name: Qty Per Ref/Designator Description Customer PN Internal PN Manufacture PN Qty Per Ref/Designator Description Customer PN Internal PN Manufacture PN Nu Torque ABC ABC < > 13456 Nu Torque Blu Origin Spaceship Blu Origin Spaceship Jet Blue21 ABC New: Jet Blue23 ABC New: DEF New: DEF Jet Blue23 ABC DEF DEF EXCELL123 ABC ABC EXCELL123 ABC ABC Toyota Supra Toyota Supra Emirate ABC12345 Emirate ABC12345 Angel ABC12346 MISSING: 1 MISSING: Angel MISSING: ABC12346 MISSING: 462 MISSING: 462 Jet Blue21 ABC
Using Excel 2007 32 bit
Worksheet: Result Wanted
Nu Torque ABC ABC < > 13456 Nu Torque Blu Origin Spaceship Blu Origin Spaceship Jet Blue21 ABC New: Jet Blue23 ABC New: DEF New: DEF Jet Blue23 ABC DEF DEF EXCELL123 ABC ABC EXCELL123 ABC ABC Toyota Supra Toyota Supra Emirate ABC12345 Emirate ABC12345 Angel ABC12346 MISSING: 1 MISSING: Angel MISSING: ABC12346 MISSING: 462 MISSING: 462 Jet Blue21 ABC
In support of this Post
http://www.excelfox.com/forum/showth...0771#post10771
Using Excel 2007 32 bit
Worksheet: Result Wanted
Row\Col Original Original Original Original Original Original Test Output Test Output Test Output Test Output Test Output NEW NEW NEW NEW NEW NEW Assembly #: Assembly Name: Assembly #: Assembly Name: Qty Per Ref/Designator Description Customer PN Internal PN Manufacture PN Qty Per Ref/Designator Description Customer PN Internal PN Manufacture PN Nu Torque ABC ABC < > 13456 Nu Torque Blu Origin Spaceship Blu Origin Spaceship Jet Blue21 ABC New: Jet Blue23 ABC New: DEF New: DEF Jet Blue23 ABC DEF DEF EXCELL123 ABC ABC EXCELL123 ABC ABC Toyota Supra Toyota Supra Emirate ABC12345 Emirate ABC12345 Angel ABC12346 MISSING: 1 MISSING: Angel MISSING: ABC12346 MISSING: 462 MISSING: 462 Jet Blue21 ABC
Using Excel 2007 32 bit
Worksheet: Result Wanted
Nu Torque ABC ABC < > 13456 Nu Torque Blu Origin Spaceship Blu Origin Spaceship Jet Blue21 ABC New: Jet Blue23 ABC New: DEF New: DEF Jet Blue23 ABC DEF DEF EXCELL123 ABC ABC EXCELL123 ABC ABC Toyota Supra Toyota Supra Emirate ABC12345 Emirate ABC12345 Angel ABC12346 MISSING: 1 MISSING: Angel MISSING: ABC12346 MISSING: 462 MISSING: 462 Jet Blue21 ABC
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
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