File -&gt; Options -&gt; Trust Center -&gt; Trust Center Setttings -&gt; Macro Settings -&gt; Trust Access to the VBA Project object model.

&lt;kbd&gt;![enter image description here][1]&lt;/kbd&gt;

&lt;kbd&gt;![enter image description here][2]&lt;/kbd&gt;



This is usually needed if you are referencing Extensibility library.


  [1]: http://i.stack.imgur.com/U1Bnm.png
  [2]: http://i.stack.imgur.com/tVOcw.png

Its very easy to do this in Python using the `pywin32` module by Mark Hammond.

What the above post by @user2140173 does actually is to change some registry values. This can be programmatically accomplished by the `pywin32` module as follows:

    import win32api
    import win32con

    key = win32api.RegOpenKeyEx(win32con.HKEY_CURRENT_USER,
                                &quot;Software\\Microsoft\\Office\\16.0\\Excel&quot;
                                + &quot;\\Security&quot;, 0, win32con.KEY_ALL_ACCESS)
    win32api.RegSetValueEx(key, &quot;AccessVBOM&quot;, 0, win32con.REG_DWORD, 1)


Ref: 

1. http://docs.activestate.com/activepython/2.4/pywin32/win32api__RegSetValueEx_meth.html
2. http://docs.activestate.com/activepython/2.4/pywin32/win32api__RegOpenKeyEx_meth.html


Mac Version - In the past there was no setting for this on the Mac version. You did get always a dialog saying the thing. 

But now, at least with version 16.30, not sure how long ago, the Mac Users have the same setting available.

BTW, it is under security, not under trust.

HTH

[![screenshot privacy trust VBA object model][1]][1]


  [1]: https://i.stack.imgur.com/T4Xnj.png

In MATLAB, to swap the first and second columns of a table `A`, one would do this&lt;sup&gt;1&lt;/sup&gt;

    A = A(:, [2 1 3:end]);

Is there a similarly convenient way to do this if `A` were a pandas `DataFrame` instead?

&lt;sub&gt;&lt;sup&gt;1&lt;/sup&gt; MATLAB uses 1-based indexing.&lt;/sub&gt;



How to swap two DataFrame columns?

All I have been reading lots of blogs on this subject but still I could not get clear idea when to use one over another hashset or tree set.

Taken an example:

 1. I have a comparable objects. I have put them in HashSet. Now when (**only when I want**) i want to set to be sorted based on compareTo logic, I can call Collections.sort(object)

 2. whereas Treeset by default always use the compareTo or compare(obj1, obj2) **all the time**. Hence performance will be hit with TreeSet but output will be same as #1 (Collections.sort).

Is this understanding correct?

Java: Hashset Vs TreeSet - when should I use over the other?

I have two scheduled tasks on my computer. They both open Excel files and run a macro &amp; are pretty similar in what they do. They both work on my computer. However I moved the scheduled tasks onto a colleague&#39;s computer. One worked the other didn&#39;t. 

The one that didn&#39;t work opened Excel but had an error saying:
&gt;&quot;programmatic access to visual basic project is not trusted&quot;.

Like I say, both Excel files are very similar. The one that didn&#39;t work does reference two additional projects the other one does not. They are,

    Microsoft Visual Basic for Applications Extensibility 5.3
    Microsoft Windows Common Controls-2.6.0 (SP6)

I have never come across this error before.

Programmatic Access To Visual Basic Project Is Not Trusted

I have two scheduled tasks on my computer. They both open Excel files and run a macro &#x26; are pretty similar in what they do. They both work on my computer. However I moved the scheduled tasks onto a colleague's computer. One worked the other didn't.
The one that didn't work opened Excel but had an error saying:
>"programmatic access to visual basic project is not trusted".
Like I say, both Excel files are very similar. The one that didn't work does reference two additional projects the other one does not. They are,
<pre><code class="hljs language-rust">Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft Windows Common Controls-2.6.0 (SP6)
</code></pre>
I have never come across this error before.

I often see questions relating to `Overflow` errors with [tag:VBA].

My question is why use the `integer` variable declaration instead of just defining all numerical variables (excluding `double` etc.) as `long`?

Unless you&#39;re performing an operation like in a for loop where you can guarantee that the value won&#39;t exceed the 32,767 limit, is there an impact on performance or something else that would dictate not using `long`?

Why Use Integer Instead of Long?

I want to concatenate two strings with a linebreak between them.

    st = &quot;Line 1&quot; + newline + &quot;Line2&quot;

How do I add a newline to VBA or Visual Basic 6?

Add newline to VBA or Visual Basic 6

I am fairly new to SQL and VBA. I have written a SQL query that I would like to be able to call and run from a VBA sub in an excel workbook and then bring the query results into the workbook. I have found some subs online (stackoverflow and other places) that claim to do this but I am having trouble understanding them as they contain no explanation. For example, here is a sub that I found online:

```vb
Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
 
    &#39; Create the connection string.
    sConnString = &quot;Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;&quot; &amp; _
                  &quot;Initial Catalog=MyDatabaseName;&quot; &amp; _
                  &quot;Integrated Security=SSPI;&quot;
    
    &#39; Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    &#39; Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute(&quot;SELECT * FROM Table1;&quot;)
    
    &#39; Check we have data.
    If Not rs.EOF Then
        &#39; Transfer result.
        Sheets(1).Range(&quot;A1&quot;).CopyFromRecordset rs
    &#39; Close the recordset
        rs.Close
    Else
        MsgBox &quot;Error: No records returned.&quot;, vbCritical
    End If

    &#39; Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
    
End Sub
```

First of all, would this work? Second, what do I need to replace in the sub (it looks like provider, data source, initial catalog, etc) and where do I find the info to replace them with?

I hope this question is not too confusing and I appreciate your help!


Using Excel VBA to run SQL query

I have some Excel worksheets that use ActiveX checkboxes to control certain activity.  They worked recently but today started to give errors.  I was alerted to this by a colleague, but it was still working on my computer.  I checked his version of Excel against mine and his was newer.  I noticed there were new Windows updates, so I did the update.  After I applied pending updates, it now no longer works on my computer.  I cannot check the ActiveX checkboxes any longer, and, as a part of trying to debug, it appears I cannot even add an ActiveX control to any worksheet, even a new worksheet, any more.  I get an error dialog that says, &quot;Cannot insert object.&quot;  (I can still add form controls, just not ActiveX.)  Anyone else experiencing this after a recent update?  Any suggestions?

Thanks,

Mike

Microsoft Excel ActiveX Controls Disabled?

I&#39;m trying to get the length of an array, yet I keep getting this error:

&gt; Object required

Am I doing something wrong?

    Dim columns As Variant
    columns = Array( _
    &quot;A&quot;, &quot;ID&quot;, _
    &quot;D&quot;, &quot;Name&quot;)
    Debug.Print columns.Length  &#39; Error: Object required

Get length of array?

I have a large spreadsheet file (.xlsx) that I&#39;m processing using python pandas. It happens that I need data from two tabs (sheets) in that large file. One of the tabs has a ton of data and the other is just a few square cells.

When I use [`pd.read_excel()`](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.io.excel.read_excel.html) on *any* worksheet, it looks to me like the whole file is loaded (not just the worksheet I&#39;m interested in). So when I use the method twice (once for each sheet), I effectively have to suffer the whole workbook being read in twice (even though we&#39;re only using the specified sheet).

How do I only load specific sheet(s) with `pd.read_excel()`?

Using Pandas to pd.read_excel() for multiple worksheets of the same workbook

I am surprised to find that there is no easy way to export multiple data.frame to multiple worksheets of an Excel file? I tried **xlsx** package, seems it can only write to one sheet (override old sheet); I also tried **WriteXLS** package, but it gives me error all the time...

My code structure is like this: by design, for each iteration, the output dataframe (tempTable) and the sheetName (sn) got updated and exported into one tab. 

    for (i in 2 : ncol(code)){ 
            ...
            tempTable &lt;- ...
            sn &lt;- ...
            WriteXLS(&quot;tempTable&quot;, ExcelFileName = &quot;C:/R_code/../file.xlsx&quot;,
                  SheetNames = sn);
    }

I can export to several cvs files, but there has to be an easy way to do that in Excel, right?

 

Easy way to export multiple data.frame to multiple Excel worksheets

I know there is a ton of these threads but all of them are for very simple cases like 3x3 matrices and things of that sort and the solutions do not even begin to apply to my situation.  So I&#39;m trying to graph G versus l1 (that&#39;s not an eleven, but an L1). The data is in the file that I loaded from an excel file. The excel file is 14x250 so there are 14 arguments, each with 250 data points.  I had another user (shout out to Hugh Bothwell!) help me with an error in my code, but now another error has surfaced.

So here is the code in question:

    # format for CSV file:
    header = [&#39;l1&#39;, &#39;l2&#39;, &#39;l3&#39;, &#39;l4&#39;, &#39;l5&#39;, &#39;EI&#39;,
          &#39;S&#39;, &#39;P_right&#39;, &#39;P1_0&#39;, &#39;P3_0&#39;,
          &#39;w_left&#39;, &#39;w_right&#39;, &#39;G_left&#39;, &#39;G_right&#39;]

    def loadfile(filename, skip=None, *args):
        skip = set(skip or [])
        with open(filename, *args) as f:
            cr = csv.reader(f, quoting=csv.QUOTE_NONNUMERIC)
            return np.array(row for i,row in enumerate(cr) if i not in skip)
    #plot data
    outputs_l1 = [loadfile(&#39;C:\\Users\\Chris\\Desktop\\Work\\Python Stuff\\BPCROOM - Shingles analysis\\ERR analysis\\l_1 analysis//BS(1) ERR analysis - l_1 - P_3 = {}.csv&#39;.format(p)) for p in p3_arr]

    col = {name:i for i,name in enumerate(header)}

    fig = plt.figure()
    for data,color in zip(outputs_l1, colors):
        xs  = data[:, col[&quot;l1&quot;     ]]
        gl = data[:, col[&quot;G_left&quot; ]] * 1000.0    # column 12
        gr = data[:, col[&quot;G_right&quot;]] * 1000.0    # column 13
        plt.plot(xs, gl, color + &quot;-&quot;, gr, color + &quot;--&quot;)
    for output, col in zip(outputs_l1, colors):
        plt.plot(output[:,0], output[:,11]*1E3, col+&#39;--&#39;)
    plt.ticklabel_format(axis=&#39;both&#39;, style=&#39;plain&#39;, scilimits=(-1,1))
    plt.xlabel(&#39;$l1 (m)$&#39;)
    plt.ylabel(&#39;G $(J / m^2) * 10^{-3}$&#39;)
    plt.xlim(xmin=.2)
    plt.ylim(ymax=2, ymin=0)

    plt.subplots_adjust(top=0.8, bottom=0.15, right=0.7)

After running the entire program, I recieve the error message:

    Traceback (most recent call last):
      File &quot;C:/Users/Chris/Desktop/Work/Python Stuff/New Stuff from Brenday 8 26 2014/CD_ssa_plot(2).py&quot;, line 115, in &lt;module&gt;
        xs  = data[:, col[&quot;l1&quot;     ]]
    IndexError: too many indices for array

and before I ran into that problem, I had another involving the line a few below the one the above error message refers to:

    Traceback (most recent call last): File &quot;FILE&quot;, line 119, in &lt;module&gt; 
    gl = data[:, col[&quot;G_left&quot; ]] * 1000.0 # column 12 
    IndexError: index 12 is out of bounds for axis 1 with size 12

I understand the first error, but am just having problems fixing it.  The second error is confusing for me though.  My boss is really breathing down my neck so any help would be GREATLY appreciated!

Content Type	Original Author	Original Content on Stackoverflow
Question	mHelpMe	View Question on Stackoverflow
Solution 1 - Vba	user2140173	View Answer on Stackoverflow
Solution 2 - Vba	alpha_989	View Answer on Stackoverflow
Solution 3 - Vba	iPadawan	View Answer on Stackoverflow

Programmatic Access To Visual Basic Project Is Not Trusted

Vba Problem Overview

Vba Solutions

Solution 1 - Vba

Solution 2 - Vba

Solution 3 - Vba

Java: Hashset Vs TreeSet - when should I use over the other?

How to swap two DataFrame columns?

Attributions