<?xml version='1.0' encoding='UTF-8'?><?xml-stylesheet href="http://www.blogger.com/styles/atom.css" type="text/css"?><feed xmlns='http://www.w3.org/2005/Atom' xmlns:openSearch='http://a9.com/-/spec/opensearchrss/1.0/' xmlns:blogger='http://schemas.google.com/blogger/2008' xmlns:georss='http://www.georss.org/georss' xmlns:gd="http://schemas.google.com/g/2005" xmlns:thr='http://purl.org/syndication/thread/1.0'><id>tag:blogger.com,1999:blog-3107383339698834899</id><updated>2023-09-23T10:29:08.895-07:00</updated><category term="Excel"/><category term="Vba"/><category term="Python"/><category term="Xlwings"/><category term="Add In"/><category term="Appearance"/><category term="Array Formulas"/><category term="C#"/><category term="Calendar"/><category term="Commandbutton"/><category term="Conditional Formatting"/><category term="Copy"/><category term="Count"/><category term="Email"/><category term="Excel 2010"/><category term="Excel 2016"/><category term="Excel Formula"/><category term="Excel Indirect"/><category term="File Search"/><category term="Find"/><category term="Google Sheets"/><category term="Internet Explorer"/><category term="Macros"/><category term="Match"/><category term="Methods"/><category term="Object"/><category term="Office Js"/><category term="Ole"/><category term="Oledb"/><category term="Openpyxl"/><category term="Openxml"/><category term="Outlook"/><category term="Paste"/><category term="Perl"/><category term="Powerpoint"/><category term="Replace"/><category term="Runtime Error"/><category term="Search"/><category term="Tfs"/><category term="Userform"/><category term="Xml"/><title type='text'>foodbyaguy</title><subtitle type='html'></subtitle><link rel='http://schemas.google.com/g/2005#feed' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/posts/default'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/'/><link rel='hub' href='http://pubsubhubbub.appspot.com/'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><generator version='7.00' uri='http://www.blogger.com'>Blogger</generator><openSearch:totalResults>25</openSearch:totalResults><openSearch:startIndex>1</openSearch:startIndex><openSearch:itemsPerPage>25</openSearch:itemsPerPage><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-3365198487486288949</id><published>2023-09-23T10:28:00.001-07:00</published><updated>2023-09-23T10:28:35.662-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Userform"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Validation For Userform To Check For Empty Controls, Using Loops</title><content type='html'>&lt;div class=&quot;question&quot;&gt;After the user done entering the data. When the user click Submit button. I will check if all the textbox is not empty.  The user will enter number of member, if the user enter 1 m&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;You could do something like this:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_0&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Private&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt; CommandButton1_Click()
&lt;span class=&quot;hljs-keyword&quot;&gt;If&lt;/span&gt; txtNoMember.Value = &lt;span class=&quot;hljs-string&quot;&gt;&quot;&quot;&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Then&lt;/span&gt;
    MsgBox &lt;span class=&quot;hljs-string&quot;&gt;&quot;Please enter the Number of Member.&quot;&lt;/span&gt;, vbExclamation, &lt;span class=&quot;hljs-string&quot;&gt;&quot;Input Data&quot;&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Exit&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Else&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; v &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;Long&lt;/span&gt;, ctrl &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Control
    v = txtNoMember.Value
    &lt;span class=&quot;hljs-keyword&quot;&gt;For&lt;/span&gt; i = &lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;To&lt;/span&gt; v
        &lt;span class=&quot;hljs-keyword&quot;&gt;For&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Each&lt;/span&gt; ctrl &lt;span class=&quot;hljs-keyword&quot;&gt;In&lt;/span&gt; Controls &lt;span class=&quot;hljs-comment&quot;&gt;&#39; loop through Controls and search for Control with the right name&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;If&lt;/span&gt; ctrl.Name = &lt;span class=&quot;hljs-string&quot;&gt;&quot;txtMember&quot;&lt;/span&gt; &amp;amp; Format(i, &lt;span class=&quot;hljs-string&quot;&gt;&quot;0#&quot;&lt;/span&gt;) &lt;span class=&quot;hljs-keyword&quot;&gt;Then&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;If&lt;/span&gt; ctrl.Value = &lt;span class=&quot;hljs-string&quot;&gt;&quot;&quot;&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Then&lt;/span&gt;
                    MsgBox &lt;span class=&quot;hljs-string&quot;&gt;&quot;Member(s) cannot be empty.&quot;&lt;/span&gt;, vbExclamation, &lt;span class=&quot;hljs-string&quot;&gt;&quot;Input Data&quot;&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Exit&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;If&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Exit&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;For&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;If&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Next&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Next&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_0&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;This works even if you enter 4 in txtNoMember, when you only have txtMemberNo03 as max.&lt;/p&gt;&lt;/div&gt;&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/3365198487486288949/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/validation-for-userform-to-check-for.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/3365198487486288949'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/3365198487486288949'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/validation-for-userform-to-check-for.html' title='Validation For Userform To Check For Empty Controls, Using Loops'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-584862674654724565</id><published>2023-09-23T06:48:00.001-07:00</published><updated>2023-09-23T06:48:30.103-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel 2016"/><category scheme="http://www.blogger.com/atom/ns#" term="Office Js"/><title type='text'>Office.js: Can I Set The Fill Of A Range Of Cells From A 2d Array?</title><content type='html'>&lt;div class=&quot;question&quot;&gt;So far, with office.js, I&#39;m getting used to this pattern, where you assign an array of data to a corresponding excel range:  range.values = [[&#39;Eurasia&#39;, &#39;29.96&#39;, &#39;0.25&#39;, &#39;15-Feb&#39; ]&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;For the formatting properties (and basically, non-arrays), you do have to set one cell at a time.&lt;/p&gt;&lt;p&gt;We have an item on the backlog to allow array-like reading and setting of these properties, but we don&#39;t have them yet. I&#39;ll bring it up again to the team, but it&#39;s not at the forefront of the queue.&lt;/p&gt;&lt;p&gt;Depending on the size of your array (and essentially, how many ranges you are creating inside of the one &lt;code&gt;Excel.run&lt;/code&gt; call), there may be some perf implications. If you run into issues there, let me know, and I can provide more info (I recommend opening a separate thread for it, though).&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;p&gt;By the way, there are some fun samples of setting multiple formats at a time shown as snippet in &lt;a rel=&quot;nofollow noreferrer noopener&quot; href=&quot;https://aka.ms/scriptlab&quot;&gt;Script Lab&lt;/a&gt;.&lt;/p&gt;&lt;p&gt;&lt;a rel=&quot;nofollow noreferrer noopener&quot; href=&quot;https://i.stack.imgur.com/Nzewi.png&quot;&gt;&lt;img alt=&quot;Script Lab&quot; src=&quot;https://i.stack.imgur.com/Nzewi.png&quot;&gt;&lt;/a&gt;&lt;/p&gt;&lt;/div&gt; 

</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/584862674654724565/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/officejs-can-i-set-fill-of-range-of.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/584862674654724565'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/584862674654724565'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/officejs-can-i-set-fill-of-range-of.html' title='Office.js: Can I Set The Fill Of A Range Of Cells From A 2d Array?'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-5267073056757878954</id><published>2023-09-23T05:42:00.001-07:00</published><updated>2023-09-23T05:42:52.804-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Methods"/><category scheme="http://www.blogger.com/atom/ns#" term="Object"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Using Object Properties That Aren&#39;t Listed In Object Browser</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I&#39;ve been looking into the underlying structure of object properties and methods in VBA a bit more. I&#39;ve just read that all properties and methods for each object is listed in the&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;&lt;a rel=&quot;nofollow noreferrer noopener&quot; href=&quot;https://i.stack.imgur.com/C7wPu.png&quot;&gt;&lt;img alt=&quot;enter image description here&quot; src=&quot;https://i.stack.imgur.com/C7wPu.png&quot;&gt;&lt;/a&gt;&lt;/p&gt;&lt;p&gt;It appears in mine - perhaps you&#39;re looking for &quot;Shapes&quot; which is a collection, as opposed to &quot;Shape&quot; which is the actual object?&lt;/p&gt;&lt;hr&gt;&lt;p&gt;&lt;strong&gt;&lt;em&gt;Detail:&lt;/em&gt;&lt;/strong&gt;&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;p&gt;&lt;code&gt;Shapes&lt;/code&gt; is a &lt;em&gt;collection&lt;/em&gt; of &lt;code&gt;Shape&lt;/code&gt; objects - therefore &lt;code&gt;Shapes&lt;/code&gt; has properties and methods that relate to a &lt;code&gt;Collection&lt;/code&gt; object. Each item in that collection is a &lt;code&gt;Shape&lt;/code&gt; object which has the properties and methods of a &lt;code&gt;Shape&lt;/code&gt;&lt;/p&gt;&lt;/div&gt;&lt;h2 id=&quot;solution_2&quot;&gt;Solution 2:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Besides what MacroMan explained, here is something you should always follow (as a habit)&lt;/p&gt;&lt;p&gt;&lt;strong&gt;Work with objects for intellisense to work correctly&lt;/strong&gt;. &lt;/p&gt;&lt;p&gt;See this Example&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_6&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt; Sample()
    &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; ws &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Worksheet
    &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; Shp &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Shape

    &lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; ws = Sheets(&lt;span class=&quot;hljs-string&quot;&gt;&quot;Front face&quot;&lt;/span&gt;)
    &lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; Shp = ws.Shapes(&lt;span class=&quot;hljs-string&quot;&gt;&quot;Drop Down 12&quot;&lt;/span&gt;)
&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_6&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;Now if you do &lt;code&gt;Shp.&lt;/code&gt; you will get the &lt;code&gt;.ControlFormat&lt;/code&gt; property.&lt;/p&gt;&lt;p&gt;Another example&lt;/p&gt;&lt;p&gt;When you want to access the &lt;code&gt;.Range&lt;/code&gt; of a worksheet, you will not get that if you type &lt;code&gt;Activesheet.&lt;/code&gt;. For intellisense, again work with objects.&lt;/p&gt;&lt;p&gt;&lt;a rel=&quot;nofollow noreferrer noopener&quot; href=&quot;https://i.stack.imgur.com/pR8dM.png&quot;&gt;&lt;img alt=&quot;enter image description here&quot; src=&quot;https://i.stack.imgur.com/pR8dM.png&quot;&gt;&lt;/a&gt;&lt;/p&gt;&lt;/div&gt; 


</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/5267073056757878954/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/using-object-properties-that-arent.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/5267073056757878954'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/5267073056757878954'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/using-object-properties-that-arent.html' title='Using Object Properties That Aren&#39;t Listed In Object Browser'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-3137133791244313788</id><published>2023-09-22T20:56:00.001-07:00</published><updated>2023-09-22T20:56:35.014-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Excel 2010"/><category scheme="http://www.blogger.com/atom/ns#" term="File Search"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>How To Get The Last Modified File In A Directory Using Vba In Excel 2010</title><content type='html'>&lt;div class=&quot;question&quot;&gt;Im looking for a way to do this in Excel 2010 using VBA. It used to be possible in Excel 2003 using the Application.FileSearch method, but this has be depreciated. (see below) Dim&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;If using the FileSystemObject is acceptable, you could use the method described &lt;a rel=&quot;nofollow noreferrer noopener&quot; href=&quot;http://www.tek-tips.com/viewthread.cfm?qid=1626406&amp;amp;page=12&quot;&gt;here&lt;/a&gt;.&lt;/p&gt;&lt;p&gt;To summarize:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_0&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; fso &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Scripting.FileSystemObject
&lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; fol &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Scripting.Folder
&lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; fdr &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Scripting.Folder
&lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; fil &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Scripting.File
&lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; flc &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Scripting.Folders

&lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; fso = CreateObject(&lt;span class=&quot;hljs-string&quot;&gt;&quot;Scripting.FileSystemObject&quot;&lt;/span&gt;)
&lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; fol = fso.GetFolder(&lt;span class=&quot;hljs-string&quot;&gt;&quot;YourPathName&quot;&lt;/span&gt;)
&lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; flc = fol.SubFolders

&lt;span class=&quot;hljs-keyword&quot;&gt;For&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Each&lt;/span&gt; fdr &lt;span class=&quot;hljs-keyword&quot;&gt;In&lt;/span&gt; flc
  &lt;span class=&quot;hljs-keyword&quot;&gt;For&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Each&lt;/span&gt; fil &lt;span class=&quot;hljs-keyword&quot;&gt;In&lt;/span&gt; fdr.Files
        Debug.Print fil.DateLastModified
  &lt;span class=&quot;hljs-keyword&quot;&gt;Next&lt;/span&gt; fil
&lt;span class=&quot;hljs-keyword&quot;&gt;Next&lt;/span&gt; fdr

&lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; fso = &lt;span class=&quot;hljs-literal&quot;&gt;Nothing&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; fol = &lt;span class=&quot;hljs-literal&quot;&gt;Nothing&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; flc = &lt;span class=&quot;hljs-literal&quot;&gt;Nothing&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_0&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt;&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/3137133791244313788/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/how-to-get-last-modified-file-in.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/3137133791244313788'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/3137133791244313788'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/how-to-get-last-modified-file-in.html' title='How To Get The Last Modified File In A Directory Using Vba In Excel 2010'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-4008215750587228339</id><published>2023-09-22T19:58:00.001-07:00</published><updated>2023-09-22T19:58:37.223-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Openxml"/><category scheme="http://www.blogger.com/atom/ns#" term="Xml"/><title type='text'>Xlsx Styles Getting Wrong Fill Pattern</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I am writing an xlsx file where I want to apply my own styles to cells in a worksheet.  I have a case where style 1 and style 2 both specify solid red, but when I open it in excel&lt;/div&gt;
&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;
&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Turns out that my second guess was right. Fill #1 is hardwired to gray125. Excel ignores the definition of this fill while reading the xml. Would have been nice if this were documented somewhere.&lt;/p&gt;&lt;/div&gt; 
&lt;br&gt;
</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/4008215750587228339/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/xlsx-styles-getting-wrong-fill-pattern.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/4008215750587228339'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/4008215750587228339'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/xlsx-styles-getting-wrong-fill-pattern.html' title='Xlsx Styles Getting Wrong Fill Pattern'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-6465476313597739853</id><published>2023-09-22T18:10:00.001-07:00</published><updated>2023-09-22T18:10:24.854-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Excel, Vba Macro To &#39;move&#39; Shapes To A Different Shape, Without Copying And Pasting</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I want to move a shape to a different sheet, without using copy and paste in the macro. Is there another way to do this?&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;If the shape is a chart object, you can just change its location:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_0&quot; class=&quot;hljs language-go&quot;&gt;ActiveChart.Location Where:=xlLocationAsObject, Name:=&lt;span class=&quot;hljs-string&quot;&gt;&quot;Sheet2&quot;&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_0&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;For all other types of shapes, there is no location property or move method. If you want to avoid copying and pasting, then you&#39;ll need to create a new shape on the new sheet and copy across all relevant properties, then delete the original.&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_1&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt; MoveShape()

    &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; shp1 &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Shape, shp2 &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Shape

    &lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; shp1 = Sheets(&lt;span class=&quot;hljs-string&quot;&gt;&quot;Sheet1&quot;&lt;/span&gt;).Shapes(&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;)
    &lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; shp2 = Sheets(&lt;span class=&quot;hljs-string&quot;&gt;&quot;Sheet2&quot;&lt;/span&gt;).Shapes.AddShape(shp1.Type, shp1.Left, shp1.Top, shp1.Width, shp1.Height)

    shp2.Name = shp1.Name
    shp2.Fill.ForeColor.RGB = shp1.Fill.ForeColor.RGB
    shp2.Line.Weight = shp1.Line.Weight
    shp2.Line.DashStyle = shp1.Line.DashStyle
    shp2.Line.ForeColor.RGB = shp1.Line.ForeColor.RGB

    shp1.Delete

&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_1&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt;&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/6465476313597739853/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/excel-vba-macro-to-move-shapes-to.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/6465476313597739853'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/6465476313597739853'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/excel-vba-macro-to-move-shapes-to.html' title='Excel, Vba Macro To &#39;move&#39; Shapes To A Different Shape, Without Copying And Pasting'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-1827419135400226121</id><published>2023-09-22T16:21:00.001-07:00</published><updated>2023-09-22T16:21:45.103-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Vba Excel Formula In A String</title><content type='html'>&lt;div class=&quot;question&quot;&gt;Hi I would like to put into a STRING the following formula Dim stringAppoggio As String Dim myMonth As String  myMonth = &#39;January 2020&#39;  stringAppoggio=&#39;=DAY(EOMONTH(DATEVALUE(01-&#39;&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Well, as per my comment, there are a few mistakes here:&lt;/p&gt;&lt;ul&gt;&lt;li&gt;You have used single quotes instead of double quotes to seperate your variable from VBA formula syntax&lt;/li&gt;&lt;li&gt;You have forgotten the quotes at all around your second &lt;code&gt;myMonth&lt;/code&gt; variable&lt;/li&gt;&lt;li&gt;You have created a formula that simply won&#39;t work&lt;/li&gt;&lt;/ul&gt;&lt;p&gt;Keep in mind, your variable is not just a month but a string holding a month and year &amp;gt; &lt;code&gt;&quot;January 2020&quot;&lt;/code&gt;, therefor &lt;code&gt;DATEVALUE&lt;/code&gt; won&#39;t need the &lt;code&gt;01-&lt;/code&gt; and &lt;code&gt;YEAR(myMonth)&lt;/code&gt; to work. Let me explain:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_5&quot; class=&quot;hljs language-bash&quot;&gt;=DATEVALUE(&lt;span class=&quot;hljs-string&quot;&gt;&quot;January 2020&quot;&lt;/span&gt;)
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_5&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;Will return &lt;code&gt;Integer&lt;/code&gt; 43831, or in other words: &lt;code&gt;1-1-2020&lt;/code&gt;. Then secondly, &lt;code&gt;EOMONTH&lt;/code&gt; will return the end of that same month as an &lt;code&gt;Integer&lt;/code&gt;, whereas &lt;code&gt;DAY&lt;/code&gt; will return the number of that day. So your formula would read:&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;pre&gt;&lt;code id=&quot;code_11&quot; class=&quot;hljs language-sql&quot;&gt;&lt;span class=&quot;hljs-operator&quot;&gt;=&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;DAY&lt;/span&gt;(EOMONTH(DATEVALUE(&quot;January 2020&quot;),&lt;span class=&quot;hljs-number&quot;&gt;0&lt;/span&gt;))
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_11&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;Now to write this in VBA:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_12&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; stringAppoggio &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;String&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; myMonth &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;String&lt;/span&gt;

myMonth = &lt;span class=&quot;hljs-string&quot;&gt;&quot;January 2020&quot;&lt;/span&gt;
stringAppoggio = &lt;span class=&quot;hljs-string&quot;&gt;&quot;=DAY(EOMONTH(DATEVALUE(&quot;&quot;&quot;&lt;/span&gt; &amp;amp; myMonth &amp;amp; &lt;span class=&quot;hljs-string&quot;&gt;&quot;&quot;&quot;),0))&quot;&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_12&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;You can check that it works:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_13&quot; class=&quot;hljs language-java&quot;&gt;Debug.Print &lt;span class=&quot;hljs-title function_&quot;&gt;Evaluate&lt;/span&gt;&lt;span class=&quot;hljs-params&quot;&gt;(&lt;span class=&quot;hljs-string&quot;&gt;&quot;=DAY(EOMONTH(DATEVALUE(&quot;&lt;/span&gt;&lt;span class=&quot;hljs-string&quot;&gt;&quot;&quot;&lt;/span&gt; &amp;amp; myMonth &amp;amp; &lt;span class=&quot;hljs-string&quot;&gt;&quot;&quot;&lt;/span&gt;&lt;span class=&quot;hljs-string&quot;&gt;&quot;),0))&quot;&lt;/span&gt;)&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_13&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;&lt;sub&gt;&lt;strong&gt;Note:&lt;/strong&gt; See the triple quotes? That&#39;s because we need to feed &lt;code&gt;DATEVALUE&lt;/code&gt; a string within quotes to work, otherwise it wouldn&#39;t be a string and will return an error&lt;/sub&gt;&lt;/p&gt;&lt;/div&gt;&lt;h2 id=&quot;solution_2&quot;&gt;Solution 2:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;pre&gt;&lt;code id=&quot;code_15&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; stringAppoggio &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;String&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; myMonth &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;String&lt;/span&gt;

myMonth = Chr(&lt;span class=&quot;hljs-number&quot;&gt;34&lt;/span&gt;) &amp;amp; &lt;span class=&quot;hljs-string&quot;&gt;&quot;January 2020&quot;&lt;/span&gt; &amp;amp; Chr(&lt;span class=&quot;hljs-number&quot;&gt;34&lt;/span&gt;)

stringAppoggio = &lt;span class=&quot;hljs-string&quot;&gt;&quot;=DAY(EDATE(&quot;&lt;/span&gt; &amp;amp; myMonth &amp;amp; &lt;span class=&quot;hljs-string&quot;&gt;&quot;,1)-DAY(&quot;&lt;/span&gt; &amp;amp; myMonth &amp;amp; &lt;span class=&quot;hljs-string&quot;&gt;&quot;))&quot;&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_15&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt; 


</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/1827419135400226121/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/vba-excel-formula-in-string.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/1827419135400226121'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/1827419135400226121'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/vba-excel-formula-in-string.html' title='Vba Excel Formula In A String'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-7647738834741497631</id><published>2023-09-22T12:45:00.001-07:00</published><updated>2023-09-22T12:45:16.622-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Desired Date Format Not Saved When Saving Excel File As .csv</title><content type='html'>&lt;div class=&quot;question&quot;&gt;having trouble with date formats in CSV file format(Excel) I want the date in &#39;YYYY-MM-DD hh:mm &#39; format and I need it in .CSV format. When reopening the file, the date format gets&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Use the format YYYYMMDD in all CSV files, which doesn&#39;t convert to date in Excel or you can do one of the below things&lt;/p&gt;&lt;ol&gt;&lt;li&gt;&lt;p&gt;Use a &lt;strong&gt;Custom format&lt;/strong&gt;, rather than one of the pre-selected Date formats, the export to CSV will keep your selected format. Otherwise it defaults back to the US format&lt;/p&gt;&lt;/li&gt;&lt;li&gt;&lt;p&gt;Place an &lt;strong&gt;apostrophe&lt;/strong&gt; in front of the date and it should export in the correct format. Just found it out for myself, I found this thread searching for an answer.&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;/li&gt;&lt;li&gt;&lt;p&gt;Change the &lt;strong&gt;date and time setting&lt;/strong&gt;s for your computer in the &quot;short date&quot; format under calendar settings. This will change the format for everything yyyy-mm-dd or however you want it to display; but remember it will look like that even for files saved on your computer.&lt;/p&gt;&lt;/li&gt;&lt;/ol&gt;&lt;/div&gt; 

</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/7647738834741497631/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/desired-date-format-not-saved-when.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/7647738834741497631'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/7647738834741497631'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/desired-date-format-not-saved-when.html' title='Desired Date Format Not Saved When Saving Excel File As .csv'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-6050944971760470549</id><published>2023-09-22T11:50:00.001-07:00</published><updated>2023-09-22T11:50:00.369-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Inputbox Confusing Decimal Entries With Strings</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I am trying to use an InputBox function to be able to have a user input data. However, the data mainly consists of decimal values. When I am testing my program and I go to subtract&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;You need to declare the variables as either &lt;code&gt;Double&lt;/code&gt; or &lt;code&gt;Single&lt;/code&gt;, depending on the expected input:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_2&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt; t()
&lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; thckmax &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;Double&lt;/span&gt;, thckmin &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;Double&lt;/span&gt;, thcknom &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;Double&lt;/span&gt;
thckmax = InputBox(&lt;span class=&quot;hljs-string&quot;&gt;&quot;What is the maximum nominal thickness?&quot;&lt;/span&gt;, &lt;span class=&quot;hljs-string&quot;&gt;&quot;Enter Max Nominal Thickness Measurement&quot;&lt;/span&gt;)
thckmin = InputBox(&lt;span class=&quot;hljs-string&quot;&gt;&quot;What is the minimum nominal thickness?&quot;&lt;/span&gt;, &lt;span class=&quot;hljs-string&quot;&gt;&quot;Enter Min Nominal Thickness Measurement&quot;&lt;/span&gt;)
thcknom = (thckmax + thckmin)
Debug.Print thcknom
&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_2&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;See &lt;a rel=&quot;nofollow noreferrer noopener&quot; href=&quot;http://cis.poly.edu/~mleung/CS394/f06/VBA/essentialElements/dataTypes.html&quot;&gt;VBA&#39;s data types&lt;/a&gt; for more info.&lt;/p&gt;&lt;p&gt;Also, I suggest adding &lt;code&gt;Option Explicit&lt;/code&gt; to the very top of your modules, which forces you to declare all variables.&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;/div&gt;&lt;h2 id=&quot;solution_2&quot;&gt;Solution 2:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;InputBox allows you to &lt;a rel=&quot;nofollow noreferrer noopener&quot; href=&quot;https://docs.microsoft.com/en-us/office/vba/api/excel.application.inputbox&quot;&gt;define the input type&lt;/a&gt;. Since you did not specify one, the default is used, which is TEXT.
Something like &lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_4&quot; class=&quot;hljs language-go&quot;&gt;thckmax = Application.InputBox(Prompt:=&lt;span class=&quot;hljs-string&quot;&gt;&quot;What is the maximum nominal thickness?&quot;&lt;/span&gt;, Type:=&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;)
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_4&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;guarantees you get a number, eventually preventing decimal symbol confusion.&lt;/p&gt;&lt;/div&gt; 


</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/6050944971760470549/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/inputbox-confusing-decimal-entries-with.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/6050944971760470549'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/6050944971760470549'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/inputbox-confusing-decimal-entries-with.html' title='Inputbox Confusing Decimal Entries With Strings'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-3635335837862636981</id><published>2023-09-22T06:47:00.001-07:00</published><updated>2023-09-22T06:47:40.096-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Ole"/><category scheme="http://www.blogger.com/atom/ns#" term="Perl"/><title type='text'>How Do I Use Perl To Run A Macro In An **already Open** Excel Workbook</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I would like to use Perl to run a macro in an already open Excel workbook.   The following code works if I just want to open a workbook and run a macro: #!C:\Perl\bin\perl.exe&lt;/div&gt;

&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;
&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Use &lt;code&gt;GetActiveObject&lt;/code&gt;. From &lt;a rel=&quot;nofollow noreferrer noopener&quot; href=&quot;http://docs.activestate.com/activeperl/5.10/lib/Win32/OLE.html#examples&quot;&gt;the docs&lt;/a&gt;:&lt;/p&gt;
&lt;pre&gt;&lt;code id=&quot;code_1&quot; class=&quot;hljs language-perl&quot;&gt;Here is a simple Microsoft Excel application.

        &lt;span class=&quot;hljs-keyword&quot;&gt;use&lt;/span&gt; Win32::OLE;

        &lt;span class=&quot;hljs-comment&quot;&gt;# use existing instance if Excel is already running&lt;/span&gt;
        &lt;span class=&quot;hljs-keyword&quot;&gt;eval&lt;/span&gt; {$ex = Win32::OLE-&amp;gt;GetActiveObject(&lt;span class=&quot;hljs-string&quot;&gt;&#39;Excel.Application&#39;&lt;/span&gt;)};
        &lt;span class=&quot;hljs-keyword&quot;&gt;die&lt;/span&gt; &lt;span class=&quot;hljs-string&quot;&gt;&quot;Excel not installed&quot;&lt;/span&gt; &lt;span class=&quot;hljs-keyword&quot;&gt;if&lt;/span&gt; $@;
        &lt;span class=&quot;hljs-keyword&quot;&gt;unless&lt;/span&gt; (&lt;span class=&quot;hljs-keyword&quot;&gt;defined&lt;/span&gt; $ex) {
            $ex = Win32::OLE-&amp;gt;new(&lt;span class=&quot;hljs-string&quot;&gt;&#39;Excel.Application&#39;&lt;/span&gt;, &lt;span class=&quot;hljs-function&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;sub&lt;/span&gt; &lt;/span&gt;{$_[&lt;span class=&quot;hljs-number&quot;&gt;0&lt;/span&gt;]-&amp;gt;Quit;})
                    &lt;span class=&quot;hljs-keyword&quot;&gt;or&lt;/span&gt; &lt;span class=&quot;hljs-keyword&quot;&gt;die&lt;/span&gt; &lt;span class=&quot;hljs-string&quot;&gt;&quot;Oops, cannot start Excel&quot;&lt;/span&gt;;
        }
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_1&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt; 
&lt;br&gt;
</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/3635335837862636981/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/how-do-i-use-perl-to-run-macro-in.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/3635335837862636981'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/3635335837862636981'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/how-do-i-use-perl-to-run-macro-in.html' title='How Do I Use Perl To Run A Macro In An **already Open** Excel Workbook'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-3553410977893104286</id><published>2023-09-22T05:34:00.001-07:00</published><updated>2023-09-22T05:34:40.658-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Email"/><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Internet Explorer"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Press &quot;new Mail&quot; Button In Microsoft 365 Email Using Vba</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I am trying to send an email over Microsoft 365 using IE as the browser. I am currently having difficulty getting VBA to create a new email and am trying to do so by simply telling&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Try &lt;code&gt;IE.Document.GetElementsByClassName(&quot;_n_j2 o365button&quot;)(0).Click&lt;/code&gt;&lt;/p&gt;&lt;p&gt;You can&#39;t use .GetElementByID() because the button hasn&#39;t been assigned an ID.&lt;/p&gt;&lt;p&gt;I&#39;ve used .GetElementsByClassName() because we know that the button has a class assigned, however a class is not unique. This method returns a collection of all elements with this class - without seeing the whole HTML, I&#39;m going to make an assumption that all buttons on the page share this class.&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;p&gt;The &lt;code&gt;(0)&lt;/code&gt; tells the code to click the first item stored in the collection.&lt;/p&gt;&lt;p&gt;(Again, I&#39;m going to assume you don&#39;t have something like &lt;code&gt;Option Base 1&lt;/code&gt; declared)&lt;/p&gt;&lt;p&gt;You might have to iterate through the collection and somehow test each item to see which button you need and change the &lt;code&gt;(0)&lt;/code&gt; accordingly.&lt;/p&gt;&lt;/div&gt; 

</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/3553410977893104286/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/press-new-mail-button-in-microsoft-365.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/3553410977893104286'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/3553410977893104286'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/press-new-mail-button-in-microsoft-365.html' title='Press &quot;new Mail&quot; Button In Microsoft 365 Email Using Vba'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-8960681138309741645</id><published>2023-09-22T04:39:00.001-07:00</published><updated>2023-09-22T04:39:42.997-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Add In"/><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Tfs"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>How Do I Resolve A Vba &#39;method Execute Of A [tfs Excel Add-in Commandbarbutton] Failed&#39; Run-time Error</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I am trying to write a macro to get / refresh an Azure DevOps query in Excel 2016 (via the TFS Excel Add-In / Team Ribbon) so that I can use the data pulled to run some calculation&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;I seem to have resolved it by selecting a different cell in the first line of my Sub Macro1 (i.e. changed &#39;Range(&quot;A&lt;strong&gt;1&lt;/strong&gt;&quot;).Select&#39; to &#39;Range(&quot;A&lt;strong&gt;2&lt;/strong&gt;&quot;).Select&#39;). I had been searching online for a resolution and came across this page (&lt;a rel=&quot;nofollow noreferrer noopener&quot; href=&quot;https://support.microsoft.com/en-ca/help/983119/run-time-error-2147467259-80004005-when-you-programmatically-set-a-pro&quot;&gt;https://support.microsoft.com/en-ca/help/983119/run-time-error-2147467259-80004005-when-you-programmatically-set-a-pro&lt;/a&gt;) which suggested that the &quot;Run-time error &#39;-2147467259 (80004005)&#39; &quot; was usually associated with protected worksheets and to unprotect the worksheet as a workaround.&lt;/p&gt;&lt;p&gt;Even though my worksheet itself is &lt;em&gt;not&lt;/em&gt; protected, the TFS Excel Add-In query results uses the first row of the generated table area (in my case, row 1) to provide information only and so somehow is &#39;protecting&#39; that row so it cant be selected or edited. I, therefore, changed my code to select a cell in the 2nd row and the code run without error and refreshed the query results. &lt;/p&gt;&lt;p&gt;I wanted to share this in case anyone had the same or similar issue where this would be helpful.&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;/div&gt; 

</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/8960681138309741645/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/how-do-i-resolve-vba-method-execute-of.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/8960681138309741645'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/8960681138309741645'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/how-do-i-resolve-vba-method-execute-of.html' title='How Do I Resolve A Vba &#39;method Execute Of A [tfs Excel Add-in Commandbarbutton] Failed&#39; Run-time Error'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-1931412596757242891</id><published>2023-09-22T02:08:00.001-07:00</published><updated>2023-09-22T02:08:24.829-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Userform Startupposition</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I have made a nice calendar pop up in excel and tested the UserForm position with: .Top = Application.Top + 125  .Left = Application.Left + 125  This works great however what I tru&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;This is working for me as you expect:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_0&quot; class=&quot;hljs language-markdown&quot;&gt;&lt;span class=&quot;hljs-code&quot;&gt;    .Top = ActiveCell.Top + ActiveCell.Height / 2 + .Height
    .Left = ActiveCell.Left + ActiveCell.Width * 1.5
&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_0&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt;&lt;h2 id=&quot;solution_2&quot;&gt;Solution 2:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;pre&gt;&lt;code id=&quot;code_1&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;If&lt;/span&gt; Application.Version = &lt;span class=&quot;hljs-string&quot;&gt;&quot;11.0&quot;&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Then&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Me&lt;/span&gt;.Top = Application.Top + [b7].Top + &lt;span class=&quot;hljs-number&quot;&gt;70&lt;/span&gt; - Windows(&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;).VisibleRange.Top
&lt;span class=&quot;hljs-keyword&quot;&gt;Else&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Me&lt;/span&gt;.Top = Application.Top + [b7].Top - &lt;span class=&quot;hljs-number&quot;&gt;4&lt;/span&gt; +  (Application.CommandBars.Item(&lt;span class=&quot;hljs-string&quot;&gt;&quot;Ribbon&quot;&lt;/span&gt;).Height * &lt;span class=&quot;hljs-number&quot;&gt;0.75&lt;/span&gt;) - Windows(&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;).VisibleRange.Top
&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;If&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Me&lt;/span&gt;.Left = Application.Left + [b7].Left + &lt;span class=&quot;hljs-number&quot;&gt;90&lt;/span&gt; - Windows(&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;).VisibleRange.Left
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_1&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;is not too bad...&lt;/p&gt;&lt;/div&gt;&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/1931412596757242891/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/userform-startupposition.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/1931412596757242891'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/1931412596757242891'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/userform-startupposition.html' title='Userform Startupposition'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-6619459676922038973</id><published>2023-09-22T01:16:00.001-07:00</published><updated>2023-09-22T01:16:20.417-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Powerpoint"/><category scheme="http://www.blogger.com/atom/ns#" term="Runtime Error"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Error When Pasting Slide: The Specified Data Type Is Unavailable</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I am getting following error while pasting a slide in PowerPoint in the following line: PPApp.ActiveWindow.View.PasteSpecial ppPasteOLEObject, msoFalse   Run-time error -2147188160&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;I was having the same problem and it happened as I was trying to export from Excel to PowerPoint without the PowerPoint reference, using it as object. The tricky thing was that sometimes it worked, other times it won´t. So after some testing I found out that it depends on the state of the PowerPoint View, if it is showing Thumbnails or a normal Slide view.&lt;/p&gt;&lt;p&gt;To fix it, set the ViewType as normal before pasting.&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_0&quot; class=&quot;hljs language-ini&quot;&gt;&lt;span class=&quot;hljs-attr&quot;&gt;PPAP.ActiveWindow.ViewType&lt;/span&gt; = ppViewNormal
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_0&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;or &lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;pre&gt;&lt;code id=&quot;code_1&quot; class=&quot;hljs language-ini&quot;&gt;&lt;span class=&quot;hljs-attr&quot;&gt;PPAP.ActiveWindow.ViewType&lt;/span&gt; = &lt;span class=&quot;hljs-number&quot;&gt;9&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_1&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;&lt;code&gt;PPAP&lt;/code&gt; stands for power point application object.&lt;/p&gt;&lt;/div&gt;&lt;h2 id=&quot;solution_2&quot;&gt;Solution 2:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Further to my comments above, this works for me. Let&#39;s say your &lt;code&gt;sheet1&lt;/code&gt; looks like this&lt;/p&gt;&lt;p&gt;&lt;img alt=&quot;enter image description here&quot; src=&quot;https://i.stack.imgur.com/C1IbU.png&quot;&gt;&lt;/p&gt;&lt;p&gt;Paste this code in a module.&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_4&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Option&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Explicit&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt; Sample()
    &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; PPApp           &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; PowerPoint.Application
    &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; PPPres          &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; PowerPoint.Presentation
    &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; PPSlide         &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; PowerPoint.Slide
    &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; SlideCount      &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;Long&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; ws &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Worksheet
    &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; rng &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Range

    &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; Filename &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;String&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; ws = ThisWorkbook.Sheets(&lt;span class=&quot;hljs-string&quot;&gt;&quot;Sheet1&quot;&lt;/span&gt;)

    &lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; rng = ws.Range(&lt;span class=&quot;hljs-string&quot;&gt;&quot;A1:K7&quot;&lt;/span&gt;)

    &lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; PPApp = CreateObject(&lt;span class=&quot;hljs-string&quot;&gt;&quot;PowerPoint.Application&quot;&lt;/span&gt;)
    PPApp.Visible = msoTrue
    &lt;span class=&quot;hljs-comment&quot;&gt;&#39;opening an existing presentation&lt;/span&gt;
    Filename = &lt;span class=&quot;hljs-string&quot;&gt;&quot;C:\Presentation1.pptx&quot;&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; PPPres = PPApp.Presentations.Open(Filename)

    SlideCount = PPPres.Slides.count

    &lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; PPSlide = PPPres.Slides.Add(SlideCount + &lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;, ppLayoutTitleOnly)

    &lt;span class=&quot;hljs-keyword&quot;&gt;With&lt;/span&gt; PPSlide.Shapes(&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;).TextFrame.TextRange
        .&lt;span class=&quot;hljs-keyword&quot;&gt;Text&lt;/span&gt; = &lt;span class=&quot;hljs-string&quot;&gt;&quot;Practice Financials - &quot;&lt;/span&gt; &amp;amp; _
                ws.Range(&lt;span class=&quot;hljs-string&quot;&gt;&quot;AH1&quot;&lt;/span&gt;).Value &amp;amp; &lt;span class=&quot;hljs-string&quot;&gt;&quot;  &quot;&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;With&lt;/span&gt; .Characters.Font
            .Size = &lt;span class=&quot;hljs-number&quot;&gt;24&lt;/span&gt;
            .Name = &lt;span class=&quot;hljs-string&quot;&gt;&quot;Arial Heading&quot;&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;With&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;With&lt;/span&gt;

    rng.Copy
    DoEvents

    PPSlide.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoFalse
&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_4&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;&lt;strong&gt;OUTPUT&lt;/strong&gt;&lt;/p&gt;&lt;p&gt;&lt;img alt=&quot;enter image description here&quot; src=&quot;https://i.stack.imgur.com/JieGR.png&quot;&gt;&lt;/p&gt;&lt;/div&gt; 


</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/6619459676922038973/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/error-when-pasting-slide-specified-data.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/6619459676922038973'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/6619459676922038973'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/error-when-pasting-slide-specified-data.html' title='Error When Pasting Slide: The Specified Data Type Is Unavailable'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-9033943671120447946</id><published>2023-09-21T23:26:00.001-07:00</published><updated>2023-09-21T23:26:22.853-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Count"/><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Search"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Search Column For Value And Count Number Of Rows</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I have a large workbook that contains a list of dates in column &#39;A&#39; of Sheet1.  Is there a way to search the column for a specific date, count how many rows down it is, then set th&lt;/div&gt;
&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;
&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Here you go...&lt;/p&gt;
&lt;pre&gt;&lt;code id=&quot;code_0&quot; class=&quot;hljs language-makefile&quot;&gt;Dim MyDate&amp;amp;, TheRow&amp;amp;
MyDate = DateSerial(2015, 4, 11)
TheRow = Application.Match(MyDate, Sheet1.[a:a], 0)
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_0&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt; 
&lt;br&gt;
</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/9033943671120447946/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/search-column-for-value-and-count.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/9033943671120447946'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/9033943671120447946'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/search-column-for-value-and-count.html' title='Search Column For Value And Count Number Of Rows'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-2386172769410405465</id><published>2023-09-21T22:31:00.001-07:00</published><updated>2023-09-21T22:31:23.504-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Appearance"/><category scheme="http://www.blogger.com/atom/ns#" term="Array Formulas"/><category scheme="http://www.blogger.com/atom/ns#" term="Excel Indirect"/><category scheme="http://www.blogger.com/atom/ns#" term="Google Sheets"/><category scheme="http://www.blogger.com/atom/ns#" term="Match"/><title type='text'>How To Get For Each Value In One Array Column Number In Another Array?</title><content type='html'>&lt;div class=&quot;question&quot;&gt;Please look the example sheet. The &#39;sheet1&#39; is a gradually filled table. In column &#39;A&#39; some kind of ID for each entity. These identifiers are sometimes repeated. In the result I ju&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;In G2 I entered this formula&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_0&quot; class=&quot;hljs language-sql&quot;&gt;&lt;span class=&quot;hljs-operator&quot;&gt;=&lt;/span&gt;ArrayFormula(iferror(SORT(&lt;span class=&quot;hljs-type&quot;&gt;ROW&lt;/span&gt;(A2:A);SORT(&lt;span class=&quot;hljs-type&quot;&gt;ROW&lt;/span&gt;(A2:A);A2:A;&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;);&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;)&lt;span class=&quot;hljs-operator&quot;&gt;-&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;MATCH&lt;/span&gt;(A2:A;SORT(A2:A);&lt;span class=&quot;hljs-number&quot;&gt;0&lt;/span&gt;)&lt;span class=&quot;hljs-operator&quot;&gt;-&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;ROW&lt;/span&gt;()&lt;span class=&quot;hljs-operator&quot;&gt;+&lt;/span&gt;&lt;span class=&quot;hljs-number&quot;&gt;2&lt;/span&gt;))
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_0&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;See if that works for you?&lt;/p&gt;&lt;p&gt;&lt;a rel=&quot;nofollow noreferrer noopener&quot; href=&quot;https://i.stack.imgur.com/yQHos.png&quot;&gt;&lt;img alt=&quot;Explanation:&quot; src=&quot;https://i.stack.imgur.com/yQHos.png&quot;&gt;&lt;/a&gt;&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;/div&gt; 

</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/2386172769410405465/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/how-to-get-for-each-value-in-one-array.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/2386172769410405465'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/2386172769410405465'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/how-to-get-for-each-value-in-one-array.html' title='How To Get For Each Value In One Array Column Number In Another Array?'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-1842748102380438309</id><published>2023-09-21T16:52:00.001-07:00</published><updated>2023-09-21T16:52:21.496-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Find"/><category scheme="http://www.blogger.com/atom/ns#" term="Replace"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Replace The Cell Value Throughout The Entire Workbook</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I am using  FndRplce() that code which replaces fnd = &#39;Ilya Malikzada&#39; with rplc = &#39;Arham&#39; throughout the entire workbook. But how to create such a code which works like: When i ch&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;If you want the name input to be a variable you can reference the cell specifically to do so. Just make it obvious so the user knows that&#39;s the input. I use Cell &lt;code&gt;A1&lt;/code&gt;, but you can adjust this and the sheet name accordingly. This reference can be a excel-named reference as well as long as you keep it within it&#39;s quotes&lt;/p&gt;&lt;p&gt;The adjustment I made was to your &lt;code&gt;rplc&lt;/code&gt; line like so&lt;/p&gt;&lt;p&gt;&lt;code&gt;ActiveWorkbook.Sheets(&quot;YourSheetName&quot;).Range(&quot;A1&quot;).Value&lt;/code&gt;&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;pre&gt;&lt;code id=&quot;code_3&quot; class=&quot;hljs language-go&quot;&gt;Sub FndRplce()

Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Dim ReplaceCount As Long

fnd = &lt;span class=&quot;hljs-string&quot;&gt;&quot;Ilya Malikzada&quot;&lt;/span&gt;
rplc = ActiveWorkbook.Sheets(&lt;span class=&quot;hljs-string&quot;&gt;&quot;YourSheetName&quot;&lt;/span&gt;).Range(&lt;span class=&quot;hljs-string&quot;&gt;&quot;A1&quot;&lt;/span&gt;).Value

For Each sht In ActiveWorkbook.Worksheets

  ReplaceCount = ReplaceCount + Application.WorksheetFunction.CountIf(sht.Cells, &lt;span class=&quot;hljs-string&quot;&gt;&quot;*&quot;&lt;/span&gt; &amp;amp; fnd &amp;amp; &lt;span class=&quot;hljs-string&quot;&gt;&quot;*&quot;&lt;/span&gt;)

  sht.Cells.Replace what:=fnd, Replacement:=rplc, _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
    
Next sht

End Sub
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_3&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt; 

</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/1842748102380438309/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/replace-cell-value-throughout-entire.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/1842748102380438309'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/1842748102380438309'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/replace-cell-value-throughout-entire.html' title='Replace The Cell Value Throughout The Entire Workbook'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-7437139040686652635</id><published>2023-09-21T14:01:00.001-07:00</published><updated>2023-09-21T14:01:51.169-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Openpyxl"/><category scheme="http://www.blogger.com/atom/ns#" term="Python"/><category scheme="http://www.blogger.com/atom/ns#" term="Xlwings"/><title type='text'>Overwriting Data To An Existing Workbook Using Python</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I am new to Python and working on a project that I could use some help on. So I am trying to modify an existing excel workbook in order to compare stock data. Luckily, there was a&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;blockquote&gt;&lt;p&gt;&lt;strong&gt;Comments&lt;/strong&gt;: You have a &lt;strong&gt;Dict&lt;/strong&gt; of &lt;code&gt;pandas.DataFrame&lt;/code&gt;. &lt;/p&gt;&lt;/blockquote&gt;&lt;p&gt;Selecting from a Dict using &lt;code&gt;list(fd_frames.values())[0]&lt;/code&gt; does lead to &lt;strong&gt;unpredictable&lt;/strong&gt; Results. Show the Keys of the Dict and choose the one you interested off using these Key, e.g.:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_2&quot; class=&quot;hljs language-bash&quot;&gt;&lt;span class=&quot;hljs-built_in&quot;&gt;print&lt;/span&gt;(fd_frames.keys())
 &amp;gt;&amp;gt;&amp;gt; dict_keys([&lt;span class=&quot;hljs-string&quot;&gt;&#39;key_1&#39;&lt;/span&gt;, &lt;span class=&quot;hljs-string&quot;&gt;&#39;key_2&#39;&lt;/span&gt;, &lt;span class=&quot;hljs-string&quot;&gt;&#39;key_n&#39;&lt;/span&gt;]
 df_2 = fd_frames[&lt;span class=&quot;hljs-string&quot;&gt;&#39;key_2&#39;&lt;/span&gt;]
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_2&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;Beside this, neither of the Dimension in your &lt;code&gt;pandas.DataFrame&lt;/code&gt; does match &lt;code&gt;M6:M30&lt;/code&gt; = 25. There are only &lt;strong&gt;8 columns&lt;/strong&gt; with &lt;strong&gt;20 Values&lt;/strong&gt;. Therfore you have to align your Worksheet Range to &lt;strong&gt;20 Rows&lt;/strong&gt;. To write Column 2017 to the Worksheet, e.g.:&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;pre&gt;&lt;code id=&quot;code_5&quot; class=&quot;hljs language-sql&quot;&gt;wb[&lt;span class=&quot;hljs-string&quot;&gt;&#39;M6:M25&#39;&lt;/span&gt;] &lt;span class=&quot;hljs-operator&quot;&gt;=&lt;/span&gt; df_2[&lt;span class=&quot;hljs-string&quot;&gt;&#39;2017&#39;&lt;/span&gt;].&lt;span class=&quot;hljs-keyword&quot;&gt;values&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_5&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;blockquote&gt;&lt;p&gt;&lt;strong&gt;Note&lt;/strong&gt;: I have updated the code below to accept &lt;code&gt;numpy.ndarray&lt;/code&gt; also.&lt;/p&gt;&lt;/blockquote&gt;&lt;hr&gt;&lt;blockquote&gt;&lt;p&gt;&lt;strong&gt;Question&lt;/strong&gt;: ... the goal is to pull the data and put it into an existing excel file&lt;/p&gt;&lt;/blockquote&gt;&lt;p&gt;Update a Workbooks Worksheet Range with List Values.
&lt;strong&gt;Using: OpenPyXL&lt;/strong&gt;: &lt;a rel=&quot;nofollow noreferrer noopener&quot; href=&quot;https://openpyxl.readthedocs.io/en/default/&quot;&gt;A Python library to read/write Excel 2010 xlsx/xlsm files&lt;/a&gt;&lt;/p&gt;&lt;blockquote&gt;&lt;p&gt;&lt;strong&gt;Note&lt;/strong&gt;: Observe how the List Values have to be arranged!
&lt;strong&gt;param values: List: *[row 1(col1, ... ,coln), ..., row n(col1, ... ,coln)]`&lt;/strong&gt;&lt;/p&gt;&lt;/blockquote&gt;&lt;pre&gt;&lt;code id=&quot;code_7&quot; class=&quot;hljs language-python&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;from&lt;/span&gt; openpyxl &lt;span class=&quot;hljs-keyword&quot;&gt;import&lt;/span&gt; Workbook, load_workbook

&lt;span class=&quot;hljs-keyword&quot;&gt;class&lt;/span&gt;&lt;span class=&quot;hljs-title class_&quot;&gt;UpdateWorkbook&lt;/span&gt;(&lt;span class=&quot;hljs-title class_ inherited__&quot;&gt;object&lt;/span&gt;):
    &lt;span class=&quot;hljs-keyword&quot;&gt;def&lt;/span&gt;&lt;span class=&quot;hljs-title function_&quot;&gt;__init__&lt;/span&gt;(&lt;span class=&quot;hljs-params&quot;&gt;self, fname, worksheet=&lt;span class=&quot;hljs-number&quot;&gt;0&lt;/span&gt;&lt;/span&gt;):
        self.fname = fname
        self.wb = load_workbook(fname)
        self.ws = self.wb.worksheets[worksheet]

    &lt;span class=&quot;hljs-keyword&quot;&gt;def&lt;/span&gt;&lt;span class=&quot;hljs-title function_&quot;&gt;save&lt;/span&gt;(&lt;span class=&quot;hljs-params&quot;&gt;self&lt;/span&gt;):
        self.wb.save(self.fname)

    &lt;span class=&quot;hljs-keyword&quot;&gt;def&lt;/span&gt;&lt;span class=&quot;hljs-title function_&quot;&gt;__setitem__&lt;/span&gt;(&lt;span class=&quot;hljs-params&quot;&gt;self, _&lt;span class=&quot;hljs-built_in&quot;&gt;range&lt;/span&gt;, values&lt;/span&gt;):
        &lt;span class=&quot;hljs-string&quot;&gt;&quot;&quot;&quot;
         Assign Values to a Worksheet Range
        :param _range:  String e.g [&#39;M6:M30&#39;]
        :param values: List: [row 1(col1, ... ,coln), ..., row n(col1, ... ,coln)]
        :return: None
        &quot;&quot;&quot;&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;def&lt;/span&gt;&lt;span class=&quot;hljs-title function_&quot;&gt;_gen_value&lt;/span&gt;():
            &lt;span class=&quot;hljs-keyword&quot;&gt;for&lt;/span&gt; value &lt;span class=&quot;hljs-keyword&quot;&gt;in&lt;/span&gt; values:
                &lt;span class=&quot;hljs-keyword&quot;&gt;yield&lt;/span&gt; value

            &lt;span class=&quot;hljs-keyword&quot;&gt;if&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;not&lt;/span&gt;&lt;span class=&quot;hljs-built_in&quot;&gt;isinstance&lt;/span&gt;(values, (&lt;span class=&quot;hljs-built_in&quot;&gt;list&lt;/span&gt;, numpy.ndarray)):
                &lt;span class=&quot;hljs-keyword&quot;&gt;raise&lt;/span&gt; ValueError(&lt;span class=&quot;hljs-string&quot;&gt;&#39;Values Type Error: Values have to be &quot;list&quot;: values={}&#39;&lt;/span&gt;.
                                  &lt;span class=&quot;hljs-built_in&quot;&gt;format&lt;/span&gt;(&lt;span class=&quot;hljs-built_in&quot;&gt;type&lt;/span&gt;(values)))
            &lt;span class=&quot;hljs-keyword&quot;&gt;if&lt;/span&gt;&lt;span class=&quot;hljs-built_in&quot;&gt;isinstance&lt;/span&gt;(values, numpy.ndarray) &lt;span class=&quot;hljs-keyword&quot;&gt;and&lt;/span&gt; values.ndim &amp;gt; &lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;:
                &lt;span class=&quot;hljs-keyword&quot;&gt;raise&lt;/span&gt; ValueError(&lt;span class=&quot;hljs-string&quot;&gt;&#39;Values Type Error: Values of Type numpy.ndarray must have ndim=1; values.ndim={}&#39;&lt;/span&gt;.
                                  &lt;span class=&quot;hljs-built_in&quot;&gt;format&lt;/span&gt;(values.ndim))

        &lt;span class=&quot;hljs-keyword&quot;&gt;from&lt;/span&gt; openpyxl.utils &lt;span class=&quot;hljs-keyword&quot;&gt;import&lt;/span&gt; range_boundaries
        min_col, min_row, max_col, max_row = range_boundaries(_&lt;span class=&quot;hljs-built_in&quot;&gt;range&lt;/span&gt;)
        cols = ((max_col - min_col)+&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;)
        rows = ((max_row - min_row)+&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;)
        &lt;span class=&quot;hljs-keyword&quot;&gt;if&lt;/span&gt; cols * rows != &lt;span class=&quot;hljs-built_in&quot;&gt;len&lt;/span&gt;(values):
            &lt;span class=&quot;hljs-keyword&quot;&gt;raise&lt;/span&gt; ValueError(&lt;span class=&quot;hljs-string&quot;&gt;&#39;Number of List Values:{} does not match Range({}):{}&#39;&lt;/span&gt;.
                             &lt;span class=&quot;hljs-built_in&quot;&gt;format&lt;/span&gt;(&lt;span class=&quot;hljs-built_in&quot;&gt;len&lt;/span&gt;(values), _&lt;span class=&quot;hljs-built_in&quot;&gt;range&lt;/span&gt;, cols * rows))

        value = _gen_value()
        &lt;span class=&quot;hljs-keyword&quot;&gt;for&lt;/span&gt; row_cells &lt;span class=&quot;hljs-keyword&quot;&gt;in&lt;/span&gt; self.ws.iter_rows(min_col=min_col, min_row=min_row,
                                           max_col=max_col, max_row=max_row):
            &lt;span class=&quot;hljs-keyword&quot;&gt;for&lt;/span&gt; cell &lt;span class=&quot;hljs-keyword&quot;&gt;in&lt;/span&gt; row_cells:
                cell.value = value.__next__()
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_7&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;blockquote&gt;&lt;p&gt;&lt;strong&gt;Usage&lt;/strong&gt;&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_8&quot; class=&quot;hljs language-python&quot;&gt;wb = UpdateWorkbook(&lt;span class=&quot;hljs-string&quot;&gt;r&#39;C:\Users\vince\Project\Spreadsheet.xlsx&#39;&lt;/span&gt;, worksheet=&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;)
df_2 = fd_frames[&lt;span class=&quot;hljs-string&quot;&gt;&#39;key_2&#39;&lt;/span&gt;]
wb[&lt;span class=&quot;hljs-string&quot;&gt;&#39;M6:M25&#39;&lt;/span&gt;] = df_2[&lt;span class=&quot;hljs-string&quot;&gt;&#39;2017&#39;&lt;/span&gt;].values
wb.save()
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_8&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/blockquote&gt;&lt;p&gt;&lt;strong&gt;&lt;em&gt;Tested with Python:3.4.2 - openpyxl:2.4.1 - LibreOffice:4.3.3.2&lt;/em&gt;&lt;/strong&gt;&lt;/p&gt;&lt;/div&gt;&lt;h2 id=&quot;solution_2&quot;&gt;Solution 2:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Here&#39;s how I do a similar procedure for other Stack explorers:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_9&quot; class=&quot;hljs language-python&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;import&lt;/span&gt; pandas &lt;span class=&quot;hljs-keyword&quot;&gt;as&lt;/span&gt; pd
&lt;span class=&quot;hljs-keyword&quot;&gt;from&lt;/span&gt; openpyxl &lt;span class=&quot;hljs-keyword&quot;&gt;import&lt;/span&gt; load_workbook
&lt;span class=&quot;hljs-keyword&quot;&gt;from&lt;/span&gt; openpyxl.utils.dataframe &lt;span class=&quot;hljs-keyword&quot;&gt;import&lt;/span&gt; dataframe_to_rows

&lt;span class=&quot;hljs-meta&quot;&gt;... &lt;/span&gt;create your pandas dataframe df...

&lt;span class=&quot;hljs-comment&quot;&gt;# Writing from pandas back to an existing EXCEL workbook&lt;/span&gt;&lt;span class=&quot;hljs-comment&quot;&gt;# Load workbook&lt;/span&gt;
wb = load_workbook(filename=target, read_only=&lt;span class=&quot;hljs-literal&quot;&gt;False&lt;/span&gt;, keep_vba=&lt;span class=&quot;hljs-literal&quot;&gt;True&lt;/span&gt;)
ws = wb[&lt;span class=&quot;hljs-string&quot;&gt;&#39;Sheet1&#39;&lt;/span&gt;]

&lt;span class=&quot;hljs-comment&quot;&gt;# Overwrite Existing data in sheet with a dataframe.&lt;/span&gt;
rows = dataframe_to_rows(df, index=&lt;span class=&quot;hljs-literal&quot;&gt;False&lt;/span&gt;, header=&lt;span class=&quot;hljs-literal&quot;&gt;True&lt;/span&gt;)

&lt;span class=&quot;hljs-keyword&quot;&gt;for&lt;/span&gt; r_idx, row &lt;span class=&quot;hljs-keyword&quot;&gt;in&lt;/span&gt;&lt;span class=&quot;hljs-built_in&quot;&gt;enumerate&lt;/span&gt;(rows, &lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;):
    &lt;span class=&quot;hljs-keyword&quot;&gt;for&lt;/span&gt; c_idx, value &lt;span class=&quot;hljs-keyword&quot;&gt;in&lt;/span&gt;&lt;span class=&quot;hljs-built_in&quot;&gt;enumerate&lt;/span&gt;(row, &lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;):
         ws.cell(row=r_idx, column=c_idx, value=value)

&lt;span class=&quot;hljs-comment&quot;&gt;# Save file&lt;/span&gt;
wb.save(&lt;span class=&quot;hljs-string&quot;&gt;&#39;outfile.xlsm&#39;&lt;/span&gt;)
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_9&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt; 


</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/7437139040686652635/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/overwriting-data-to-existing-workbook.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/7437139040686652635'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/7437139040686652635'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/overwriting-data-to-existing-workbook.html' title='Overwriting Data To An Existing Workbook Using Python'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-2039510889482905976</id><published>2023-09-21T12:08:00.001-07:00</published><updated>2023-09-21T12:08:36.275-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Conditional Formatting"/><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Excel Formula"/><title type='text'>Conditional Formatting: Combining Multiple Checks</title><content type='html'>&lt;div class=&quot;question&quot;&gt;Right now I check if a cell is blank, if it is, it becomes red. =ISBLANK($B$12)  However this cell only needs to be red when it&#39;s empty AND another condition is met: the cell above&lt;/div&gt;
&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;
&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;You could check for the cell being blank and above being &quot;YES&quot; with the following:&lt;/p&gt;
&lt;pre&gt;&lt;code id=&quot;code_0&quot; class=&quot;hljs language-swift&quot;&gt;&lt;span class=&quot;hljs-operator&quot;&gt;=&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;AND&lt;/span&gt;(&lt;span class=&quot;hljs-type&quot;&gt;ISBLANK&lt;/span&gt;(&lt;span class=&quot;hljs-variable&quot;&gt;$B&lt;/span&gt;&lt;span class=&quot;hljs-variable&quot;&gt;$12&lt;/span&gt;),&lt;span class=&quot;hljs-type&quot;&gt;EXACT&lt;/span&gt;(&lt;span class=&quot;hljs-variable&quot;&gt;$B&lt;/span&gt;&lt;span class=&quot;hljs-variable&quot;&gt;$11&lt;/span&gt;, &lt;span class=&quot;hljs-string&quot;&gt;&quot;YES&quot;&lt;/span&gt;))
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_0&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt; 
&lt;br&gt;
</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/2039510889482905976/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/conditional-formatting-combining.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/2039510889482905976'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/2039510889482905976'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/conditional-formatting-combining.html' title='Conditional Formatting: Combining Multiple Checks'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-4204670462407669595</id><published>2023-09-21T10:45:00.001-07:00</published><updated>2023-09-21T10:45:51.383-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Copy"/><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Paste"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Excel Vba Copy And Paste (without Using Copy+paste Functions) On Empty Row</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I&#39;ve done this before and I&#39;m scratching my head to why I can&#39;t figure it out. I&#39;m trying to pull data from one sheet and paste it into another sheet on the next row. I&#39;ve done thi&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Try this:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_0&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Option&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Explicit&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt; SubmitChange()

    &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; lastRow &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;Long&lt;/span&gt;

    lastRow = Sheet44.Cells(Sheet44.Rows.Count, &lt;span class=&quot;hljs-number&quot;&gt;2&lt;/span&gt;).&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;(xlUp).Row + &lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;

    Sheet44.Range(&lt;span class=&quot;hljs-string&quot;&gt;&quot;B&quot;&lt;/span&gt; &amp;amp; lastRow).Value2 = Sheet43.Range(&lt;span class=&quot;hljs-string&quot;&gt;&quot;A6&quot;&lt;/span&gt;).Value2

&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_0&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;.&lt;/p&gt;&lt;p&gt;Notes:&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;ul&gt;&lt;li&gt;&lt;p&gt;You should always use &lt;code&gt;Option Explicit&lt;/code&gt; at the top of the module&lt;/p&gt;&lt;ul&gt;&lt;li&gt;it will force you to properly declare variables&lt;/li&gt;&lt;/ul&gt;&lt;/li&gt;&lt;li&gt;&lt;p&gt;If you copied and pasted your code without any changes, there is a subtle issue:&lt;/p&gt;&lt;ul&gt;&lt;li&gt;your variable &quot;Row_number&quot; is defined outside the scope of this sub (global)&lt;/li&gt;&lt;li&gt;inside this sub you have a second variable named &quot;row_number&quot; (lower case &quot;r&quot;)&lt;/li&gt;&lt;li&gt;the loop increments the global variable &quot;Row_number&quot;&lt;/li&gt;&lt;li&gt;but in the last line the value gets always assigned to Sheet44.Range(&quot;B2&quot;)&lt;/li&gt;&lt;li&gt;&quot;row_number&quot; (local to this sub) never gets incremented as you&#39;d expect&lt;/li&gt;&lt;/ul&gt;&lt;/li&gt;&lt;/ul&gt;&lt;p&gt;The other issue is this &lt;code&gt;Sheet43.Range(A6).Value&lt;/code&gt; should be this &lt;code&gt;Sheet43.Range(&quot;A6&quot;).Value&lt;/code&gt;&lt;/p&gt;&lt;/div&gt; 

</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/4204670462407669595/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/excel-vba-copy-and-paste-without-using.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/4204670462407669595'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/4204670462407669595'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/excel-vba-copy-and-paste-without-using.html' title='Excel Vba Copy And Paste (without Using Copy+paste Functions) On Empty Row'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-7718561961041643246</id><published>2023-09-21T08:56:00.001-07:00</published><updated>2023-09-21T08:56:33.771-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Calendar"/><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Outlook"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Exporting Outlook Calendar Data To Excel File - Shared Calendars And Vba</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I&#39;m trying to export some data from Outlook to Excel. The task is to analyze all the shared calendar and find out when an user is going on vacation. All the people would put an app&lt;/div&gt;
&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;
&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Instead of using &lt;code&gt;Namespace.GetDefaultFolder&lt;/code&gt;, call &lt;code&gt;Namespace.CreateRecipient&lt;/code&gt; passing the name of the other user, call &lt;code&gt;Recipient.Resolve&lt;/code&gt;, then pass it to &lt;code&gt;Namespace.GetSharedDefaultFolder&lt;/code&gt;&lt;/p&gt;&lt;/div&gt; 
&lt;br&gt;
</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/7718561961041643246/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/exporting-outlook-calendar-data-to.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/7718561961041643246'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/7718561961041643246'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/exporting-outlook-calendar-data-to.html' title='Exporting Outlook Calendar Data To Excel File - Shared Calendars And Vba'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-6315314034385995549</id><published>2023-09-21T07:55:00.001-07:00</published><updated>2023-09-21T07:55:46.066-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Macros"/><category scheme="http://www.blogger.com/atom/ns#" term="Python"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><category scheme="http://www.blogger.com/atom/ns#" term="Xlwings"/><title type='text'>Possible Bug? Xlwings Cannot Run An Excel Macro?</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I am having a problem getting xlwings to run a macro from Python. Despite following the code from xlwings documentation, I cannot get xlwings to execute an Excel macro. For instanc&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Try the following:&lt;/p&gt;&lt;p&gt;&lt;strong&gt;In VBA&lt;/strong&gt;:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_0&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt; Test(number)
   &lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; ws = Worksheets(&lt;span class=&quot;hljs-string&quot;&gt;&quot;Hoja1&quot;&lt;/span&gt;)
   ws.Range(&lt;span class=&quot;hljs-string&quot;&gt;&quot;A1&quot;&lt;/span&gt;).Value = number
&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_0&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;&lt;strong&gt;In python:&lt;/strong&gt;&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;pre&gt;&lt;code id=&quot;code_1&quot; class=&quot;hljs language-python&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;import&lt;/span&gt; xlwings &lt;span class=&quot;hljs-keyword&quot;&gt;as&lt;/span&gt; xw
wb1 = xw.Book(&lt;span class=&quot;hljs-string&quot;&gt;&#39;Libro1.xlsm&#39;&lt;/span&gt;)    
macro=wb1.macro(&lt;span class=&quot;hljs-string&quot;&gt;&#39;Test&#39;&lt;/span&gt;)
macro(&lt;span class=&quot;hljs-number&quot;&gt;10&lt;/span&gt;)
&lt;span class=&quot;hljs-built_in&quot;&gt;print&lt;/span&gt;(&lt;span class=&quot;hljs-string&quot;&gt;&#39;done.&#39;&lt;/span&gt;)
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_1&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt; 

</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/6315314034385995549/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/possible-bug-xlwings-cannot-run-excel.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/6315314034385995549'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/6315314034385995549'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/possible-bug-xlwings-cannot-run-excel.html' title='Possible Bug? Xlwings Cannot Run An Excel Macro?'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-69871852007239553</id><published>2023-09-21T06:54:00.001-07:00</published><updated>2023-09-21T06:54:47.588-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Run-time Error &#39;1004&#39; In Excel Vba</title><content type='html'>&lt;div class=&quot;question&quot;&gt;For some reason I keep getting a range global runtime error when I try to run the following script.  Any help would be greatly appreciated. Sub crossUpdate() Dim rng As Range  Set&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Try changing your code into:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_0&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt; crossUpdate()
   &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; rng &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Range
   ActiveWorkbook.Sheets(&lt;span class=&quot;hljs-string&quot;&gt;&quot;Sheet1&quot;&lt;/span&gt;).&lt;span class=&quot;hljs-keyword&quot;&gt;Select&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; rng = ActiveWorkbook.Sheets(&lt;span class=&quot;hljs-string&quot;&gt;&quot;Sheet1&quot;&lt;/span&gt;).Range(&lt;span class=&quot;hljs-string&quot;&gt;&quot;A2&quot;&lt;/span&gt;).&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;(xlDown)
   rng.&lt;span class=&quot;hljs-keyword&quot;&gt;Select&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_0&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt;&lt;h2 id=&quot;solution_2&quot;&gt;Solution 2:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Whenever you &lt;strong&gt;select&lt;/strong&gt; a range, you must have already &lt;strong&gt;Select&lt;/strong&gt; ed the proper sheet. Also fix the &lt;strong&gt;Select&lt;/strong&gt; command Try:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_1&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt; crossUpdate()
    &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; rng &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Range
    &lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; rng = Sheet1.Cells.Range(&lt;span class=&quot;hljs-string&quot;&gt;&quot;A2&quot;&lt;/span&gt;).&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;(xlDown)
    Sheet1.&lt;span class=&quot;hljs-keyword&quot;&gt;Select&lt;/span&gt;
    rng.&lt;span class=&quot;hljs-keyword&quot;&gt;Select&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_1&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;&lt;strong&gt;EDIT#1&lt;/strong&gt;&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;p&gt;This version will &lt;strong&gt;Select&lt;/strong&gt; the block of cells:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_2&quot; class=&quot;hljs language-vbnet&quot;&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt; crossUpdate()
    &lt;span class=&quot;hljs-keyword&quot;&gt;Dim&lt;/span&gt; rng &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt; Range, N &lt;span class=&quot;hljs-keyword&quot;&gt;As&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;Long&lt;/span&gt;
    Sheet1.&lt;span class=&quot;hljs-keyword&quot;&gt;Select&lt;/span&gt;
    N = Cells(Rows.Count, &lt;span class=&quot;hljs-string&quot;&gt;&quot;A&quot;&lt;/span&gt;).&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;(xlUp).Row
    &lt;span class=&quot;hljs-keyword&quot;&gt;Set&lt;/span&gt; rng = Cells.Range(&lt;span class=&quot;hljs-string&quot;&gt;&quot;A2:A&quot;&lt;/span&gt; &amp;amp; N)
    rng.&lt;span class=&quot;hljs-keyword&quot;&gt;Select&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;End&lt;/span&gt;&lt;span class=&quot;hljs-keyword&quot;&gt;Sub&lt;/span&gt;&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_2&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt; 


</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/69871852007239553/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/run-time-error-1004-in-excel-vba.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/69871852007239553'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/69871852007239553'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/run-time-error-1004-in-excel-vba.html' title='Run-time Error &#39;1004&#39; In Excel Vba'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-9111865267338436105</id><published>2023-09-21T04:18:00.001-07:00</published><updated>2023-09-21T04:18:43.645-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="C#"/><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Oledb"/><title type='text'>How To Count Empty Rows When Reading From Excel</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I&#39;m using OLEDB to connect and read through data from an Excel spreadsheet. I have IMEX=&#39;1&#39; and everything works ok.  My problem is the sheets I&#39;m reading from may start with sever&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;I think your problem is with your connection string. I tested the below code and it worked for me:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_0&quot; class=&quot;hljs language-csharp&quot;&gt;     DataSet Contents = &lt;span class=&quot;hljs-keyword&quot;&gt;new&lt;/span&gt; DataSet();
     &lt;span class=&quot;hljs-keyword&quot;&gt;using&lt;/span&gt; (OleDbDataAdapter adapter = &lt;span class=&quot;hljs-keyword&quot;&gt;new&lt;/span&gt; OleDbDataAdapter(&lt;span class=&quot;hljs-string&quot;&gt;&quot;select FirstName,LastName,Email,Mobile from [&quot;&lt;/span&gt; + mySheet + &lt;span class=&quot;hljs-string&quot;&gt;&quot;]&quot;&lt;/span&gt;, connection))
     {
         adapter.Fill(Contents,&lt;span class=&quot;hljs-string&quot;&gt;&quot;MyTable&quot;&lt;/span&gt;);
     }

     &lt;span class=&quot;hljs-keyword&quot;&gt;foreach&lt;/span&gt; (DataRow content &lt;span class=&quot;hljs-keyword&quot;&gt;in&lt;/span&gt; Contents.Tables[&lt;span class=&quot;hljs-string&quot;&gt;&quot;MyTable&quot;&lt;/span&gt;].Rows)
     {
         &lt;span class=&quot;hljs-keyword&quot;&gt;if&lt;/span&gt; (content[&lt;span class=&quot;hljs-number&quot;&gt;0&lt;/span&gt;].ToString() == &lt;span class=&quot;hljs-string&quot;&gt;&quot;&quot;&lt;/span&gt; &amp;amp;&amp;amp; content[&lt;span class=&quot;hljs-number&quot;&gt;0&lt;/span&gt;].ToString() == &lt;span class=&quot;hljs-string&quot;&gt;&quot;&quot;&lt;/span&gt; &amp;amp;&amp;amp; content[&lt;span class=&quot;hljs-number&quot;&gt;0&lt;/span&gt;].ToString() == &lt;span class=&quot;hljs-string&quot;&gt;&quot;&quot;&lt;/span&gt; &amp;amp;&amp;amp; content[&lt;span class=&quot;hljs-number&quot;&gt;0&lt;/span&gt;].ToString() == &lt;span class=&quot;hljs-string&quot;&gt;&quot;&quot;&lt;/span&gt;)
         {
             Console.WriteLine(&lt;span class=&quot;hljs-string&quot;&gt;&quot;Empty Row&quot;&lt;/span&gt;);
         }
         &lt;span class=&quot;hljs-keyword&quot;&gt;else&lt;/span&gt;
         {
             Console.WriteLine(content[&lt;span class=&quot;hljs-number&quot;&gt;0&lt;/span&gt;] + &lt;span class=&quot;hljs-string&quot;&gt;&quot; | &quot;&lt;/span&gt; + content[&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;] + &lt;span class=&quot;hljs-string&quot;&gt;&quot; | &quot;&lt;/span&gt; + content[&lt;span class=&quot;hljs-number&quot;&gt;2&lt;/span&gt;] + &lt;span class=&quot;hljs-string&quot;&gt;&quot; | &quot;&lt;/span&gt; + content[&lt;span class=&quot;hljs-number&quot;&gt;3&lt;/span&gt;]);
         }
     }
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_0&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;p&gt;My Connection String is:&lt;/p&gt;&lt;pre&gt;&lt;code id=&quot;code_1&quot; class=&quot;hljs language-swift&quot;&gt;    string cnnStr &lt;span class=&quot;hljs-operator&quot;&gt;=&lt;/span&gt;&lt;span class=&quot;hljs-string&quot;&gt;&quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&lt;span class=&quot;hljs-subst&quot;&gt;\&quot;&lt;/span&gt;C:&lt;span class=&quot;hljs-subst&quot;&gt;\\&lt;/span&gt;Untitled 1.xls&lt;span class=&quot;hljs-subst&quot;&gt;\&quot;&lt;/span&gt;;Extended Properties=&lt;span class=&quot;hljs-subst&quot;&gt;\&quot;&lt;/span&gt;Excel 8.0;HDR=Yes;IMEX=1&lt;span class=&quot;hljs-subst&quot;&gt;\&quot;&lt;/span&gt;&quot;&lt;/span&gt;;
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_1&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt;&lt;h2 id=&quot;solution_2&quot;&gt;Solution 2:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;check below code :It will return the empty rows..&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;pre&gt;&lt;code id=&quot;code_2&quot; class=&quot;hljs language-vbnet&quot;&gt;System.Data.OleDb.OleDbConnection conn = &lt;span class=&quot;hljs-built_in&quot;&gt;new&lt;/span&gt; System.Data.OleDb.OleDbConnection(&lt;span class=&quot;hljs-string&quot;&gt;&quot;Provider=Microsoft.ACE.OLEDB.12.0; Data Source = &quot;&lt;/span&gt; + strFileName + &lt;span class=&quot;hljs-string&quot;&gt;&quot;; Extended Properties = \&quot;&lt;/span&gt;Excel &lt;span class=&quot;hljs-number&quot;&gt;8.0&lt;/span&gt;;HDR=NO;IMEX=&lt;span class=&quot;hljs-number&quot;&gt;1&lt;/span&gt;\&lt;span class=&quot;hljs-string&quot;&gt;&quot;;&quot;&lt;/span&gt;); /*&lt;span class=&quot;hljs-keyword&quot;&gt;for&lt;/span&gt; office &lt;span class=&quot;hljs-number&quot;&gt;2007&lt;/span&gt; connection*/
                    conn.Open();
&lt;span class=&quot;hljs-type&quot;&gt;string&lt;/span&gt; strQuery = &lt;span class=&quot;hljs-string&quot;&gt;&quot;SELECT * FROM [&quot;&lt;/span&gt; + Table + &lt;span class=&quot;hljs-string&quot;&gt;&quot;]&quot;&lt;/span&gt;;
System.Data.OleDb.OleDbDataAdapter adapter = &lt;span class=&quot;hljs-built_in&quot;&gt;new&lt;/span&gt;  System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
System.Data.DataTable ExcelToDataTable = &lt;span class=&quot;hljs-built_in&quot;&gt;new&lt;/span&gt; System.Data.DataTable();
adapter.Fill(ExcelToDataTable);

DT = ExcelToDataTable.Copy();

int count = DT.Rows.Cast&amp;lt;DataRow&amp;gt;().&lt;span class=&quot;hljs-keyword&quot;&gt;Where&lt;/span&gt;(row =&amp;gt; row.ItemArray.All(field =&amp;gt; field &lt;span class=&quot;hljs-built_in&quot;&gt;is&lt;/span&gt; System.DBNull || &lt;span class=&quot;hljs-type&quot;&gt;string&lt;/span&gt;.&lt;span class=&quot;hljs-keyword&quot;&gt;Compare&lt;/span&gt;((field &lt;span class=&quot;hljs-keyword&quot;&gt;as&lt;/span&gt;&lt;span class=&quot;hljs-type&quot;&gt;string&lt;/span&gt;).Trim(), &lt;span class=&quot;hljs-type&quot;&gt;string&lt;/span&gt;.Empty) == &lt;span class=&quot;hljs-number&quot;&gt;0&lt;/span&gt;)).ToList().Count();
&lt;/code&gt;&lt;button class=&quot;copy-btn btn btn-secondary btn-sm&quot; onclick=&quot;copyCode(this,&#39;code_2&#39;)&quot;&gt;Copy&lt;/button&gt;&lt;/pre&gt;&lt;/div&gt;&lt;h2 id=&quot;solution_3&quot;&gt;Solution 3:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;As stated by @Knvn&lt;/p&gt;&lt;p&gt;You need to specifiy the file extension &lt;code&gt;.xls&lt;/code&gt; with the file name in your connection string.&lt;/p&gt;&lt;/div&gt; 

</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/9111865267338436105/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/how-to-count-empty-rows-when-reading.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/9111865267338436105'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/9111865267338436105'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/how-to-count-empty-rows-when-reading.html' title='How To Count Empty Rows When Reading From Excel'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3107383339698834899.post-4095853537855206098</id><published>2023-09-20T21:50:00.001-07:00</published><updated>2023-09-20T21:50:26.633-07:00</updated><category scheme="http://www.blogger.com/atom/ns#" term="Commandbutton"/><category scheme="http://www.blogger.com/atom/ns#" term="Excel"/><category scheme="http://www.blogger.com/atom/ns#" term="Vba"/><title type='text'>Excel Commandbutton Names Changing</title><content type='html'>&lt;div class=&quot;question&quot;&gt;I created an excel file with macros on ActiveX-CommandButtons.  They worked well, when I just finished them.  As soon as I opened the file again, the CommandButton-Names changed.&lt;/div&gt;&lt;h2 id=&quot;solution_1&quot;&gt;Solution 1:

&lt;/h2&gt;&lt;div class=&quot;answer-desc&quot;&gt;&lt;p&gt;Right click on CommandButton&lt;/p&gt;&lt;p&gt;Go to (click on) &#39;Format Text Effects&#39;&lt;/p&gt;&lt;p&gt;Give the name to button, whichever you want.&lt;/p&gt;&lt;a name=&#39;more&#39;&gt;&lt;/a&gt;&lt;p&gt;Save it.&lt;/p&gt;&lt;p&gt;Close the workbook.&lt;/p&gt;&lt;p&gt;Open it back and check whether name changes&lt;/p&gt;&lt;p&gt;(No need to enable &#39;Design Mode&#39;)&lt;/p&gt;&lt;p&gt;It works for me. Hope it shall work for you too.&lt;/p&gt;&lt;p&gt;If it do not work then please let me know.&lt;/p&gt;&lt;p&gt;For more info: &lt;a rel=&quot;nofollow noreferrer noopener&quot; href=&quot;https://support.office.microsoft.com/en-in/article/Add-a-button-and-assign-a-macro-to-it-in-a-worksheet-d58edd7d-cb04-4964-bead-9c72c843a283?CorrelationId=345214f6-6d01-4b07-994b-b2e33159e694&amp;amp;ui=en-US&amp;amp;rs=en-IN&amp;amp;ad=IN&quot;&gt;https://support.office.microsoft.com/en-in/article/Add-a-button-and-assign-a-macro-to-it-in-a-worksheet-d58edd7d-cb04-4964-bead-9c72c843a283?CorrelationId=345214f6-6d01-4b07-994b-b2e33159e694&amp;amp;ui=en-US&amp;amp;rs=en-IN&amp;amp;ad=IN&lt;/a&gt;&lt;/p&gt;&lt;/div&gt; 

</content><link rel='replies' type='application/atom+xml' href='https://foodbyaguy.blogspot.com/feeds/4095853537855206098/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/excel-commandbutton-names-changing.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/4095853537855206098'/><link rel='self' type='application/atom+xml' href='https://www.blogger.com/feeds/3107383339698834899/posts/default/4095853537855206098'/><link rel='alternate' type='text/html' href='https://foodbyaguy.blogspot.com/2023/09/excel-commandbutton-names-changing.html' title='Excel Commandbutton Names Changing'/><author><name>Dora Effertz</name><uri>http://www.blogger.com/profile/05000973911597526683</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry></feed>