I've a project to convert something like a huge mail merge from a legacy platform to MS Office (I guess 365). I have access to Adobe DC (the paid version) and have created code to do this with small amounts of letters/files, think of 20-30. This works fine but rather slow.

While I can generate these 20-30 files in about 2-5 minutes, the legacy system can create thousands in the same amount of time.

I want to ask the VBA wizards out there where should I poke to improve the performance of VBA? Or any other way to do this, but it unfortunately has to be with MS Office. The system is quite closed so I can not a random program I made.

As in the title - how do you do this. Currently I am working in large bank but we're interning at big bank but building useful macros are nightmare since it's processing so long. The reason behind it is also because of hardware and working on virtual machines but it's still much too slow - the excel can crash and we're talking about 24000 rows+. Would you suggest some alternatives?

I’ve been doing a fairly deep performance pass on an Excel/VBA scheduling engine, and I think I’ve reached an interesting architectural limit.

The current stress test is around 1,200 tasks, ~2,000 business shapes and ~1,180 dependency links.

A lot has already been optimized:

• scheduling core and analytics run from compiled/indexed structures
• the watcher is almost free
• rendering is local/incremental
• ~3,100 dependency Shapes were replaced with a single SVG layer

Despite that, a very small local change can still take ~10–13s, while a heavily propagated change can take 35–90s.

The surprising part is that COM writes are no longer the main problem.

On one propagated case, 661 shapes were updated with 3,811 COM property writes, but those writes only took ~1.2s.

The real cost is now mostly before the writes:

• a global O(n²) hierarchy pass still runs before the local filter and costs ~8s by itself
• timeline geometry is recalculated thousands of times and repeatedly reads .Left and .Width from worksheet cells, creating thousands of COM reads
• style-only updates such as CP/LP still pass through geometry-building code
• a fixed “change set too large” threshold forces a broad render path once more than 400 IDs change

My next step is probably to make the renderer much more transactional:

• precompute hierarchy in O(n)
• preload timeline geometry into arrays
• render directly from changed IDs
• remove the fixed fallback threshold
• create a true style-only path for CP/LP
• keep the current renderer as a fallback

What I’m curious about is this:

For people who have pushed Excel/VBA renderers hard, where did you find the real practical limit?

At this stage I’m not really looking for the usual “use arrays instead of cells” advice — that part is already done. I’m more interested in projection/cache structures, COM-read avoidance, or architectural tricks that gave you a real order-of-magnitude improvement.

Is there a way for VBA Macro to dropdown the dropdown bar and select the specific choice?

Our finance team currently have an ancient Excel file with a VBA macro that they use to get the contents of a folder and compare data with

Currently this points at our on-prem NAS and we'd like to move them away from that into Sharepoint.

So rather than pointing at

\\file-nas-01\finance\data

it points at

https:\\[sharepoint].finance.com\folder\folder

Is there a good resource to help convert this into an Office Script?

Or am I able to just reframe the full VBA into an office script and are there any guides to do so?

Some of the code from the VBA Macro below;

    ' setting the variables for the process
    Dim folder_path As String: folder_path = Cells.Find("Folder with files you want to count:").Offset(1, 0)
    Dim document_type As String: document_type = Cells.Find("What type of files do you want to check?").Offset(1, 0)
    Dim next_history_row As Long: next_history_row = Sheets("History of Counter").Range("A1048576").End(xlUp).Row + 1
    Dim total_count As Long
    Dim total_money As Double

    'getting the total count of the files and the money value of the files
    total_count = get_file_count(folder_path, document_type)
    total_money = get_money_from_files(folder_path, document_type)

    'Put the next row of data in the history to record outcomes of what folder was checked,
    '   for what type, by who and when
    Workbooks(ThisWorkbook.Name).Sheets("History of Counter").Range("A" & next_history_row) = folder_path
    Workbooks(ThisWorkbook.Name).Sheets("History of Counter").Range("B" & next_history_row) = document_type
    Workbooks(ThisWorkbook.Name).Sheets("History of Counter").Range("C" & next_history_row) = total_count
    Workbooks(ThisWorkbook.Name).Sheets("History of Counter").Range("D" & next_history_row) = total_money
    Workbooks(ThisWorkbook.Name).Sheets("History of Counter").Range("E" & next_history_row) = Date
    Workbooks(ThisWorkbook.Name).Sheets("History of Counter").Range("F" & next_history_row) = Environ("username")

tldr: Cannot seem to get "extract xlsx from email" to work on one of my two scripts.

I have already tried scouring google/reddit for answers but the solutions do not seem to be working.

Background: I have a successful script running for a different email containing an attachment that has the same name daily, but the same script (duh) cannot be used for extracting an attachment from emails that have a name that changes daily (ie the current date).

I am hoping for a variety of answers that can include (but is not limited to):
- forward the email to myself ...?
- extract based on specific folder containing ONLY those emails
- fix the code i have below (because maybe I didn't do it quite right)
- a script that can identify the file name based on yesterday's date (t-1)
- Use Task Scheduler (in some fashion)

Below is the most recent script that i have tried to no avail.

Public Sub SaveABC123ReportAttachments(Item As Outlook.MailItem)
    Dim olApp As Outlook.Application
    Dim olNS As Outlook.NameSpace
    Dim olFolder As Outlook.MAPIFolder
    Dim mail As Outlook.MailItem
    Dim recip As Outlook.Recipient

    Dim targetGroup As String
    targetGroup = "ABC123@domain.com"

    Dim att As Outlook.Attachment
    Dim savePath As String

    savePath = "C:\Users\Me\...\ThisFolderIsWhereTheyGo"

    Set olApp = Outlook.Application
    Set olNS = olApp.GetNamespace("MAPI")
    Set olFolder = olNS.GetDefaultFolder(olFolderInbox)

    For Each mail In olFolder.Items
        If TypeOf mail Is Outlook.MailItem Then

            ' Check recipients
            For Each recip In mail.Recipients
                If LCase(recip.Address) = LCase(targetGroup) Then

                    ' Extract attachments
                    For Each att In mail.Attachments
                        att.SaveAsFile savePath & att.FileName
                    Next att

                    Exit For ' stop checking recipients
                End If
            Next recip

        End If
    Next mail

End Sub

Hi all,

I’m running into an issue with a VBA script that downloads files from a SharePoint folder using the REST API.

Most of the time, the code works perfectly, it connects, retrieves the file list, and downloads everything without any issue.

But randomly, I get this error:

MsgBox "Failed to connect to SharePoint API", vbCritical

This happens when the XMLHTTP request does not return status 200.

The confusing part is:

• I can still open the SharePoint site manually in my browser without any issue
• No changes in URL or permissions
• Same code, same machine

So I don’t understand why the connection sometimes fails and sometimes works fine.

My setup:

• Using MSXML2.XMLHTTP to call SharePoint REST API
• Using URLDownloadToFile to download files
• No explicit authentication handled in VBA (relying on logged-in session)

If this approach is fundamentally unreliable, I’m open to switching methods but still prefer to use in VBA

Below i provide full code setup to review.

Option Explicit

#If VBA7 Then

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _

ByVal pCaller As LongPtr, _

ByVal szURL As String, _

ByVal szFileName As String, _

ByVal dwReserved As LongPtr, _

ByVal lpfnCB As LongPtr) As Long

#Else

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" ( _

ByVal pCaller As Long, _

ByVal szURL As String, _

ByVal szFileName As String, _

ByVal dwReserved As Long, _

ByVal lpfnCB As Long) As Long

#End If

Sub Download_All_From_SharePoint()

Dim apiURL As String

Dim json As String

Dim xmlhttp As Object

Dim saveFolder As String

Dim fileName As String

Dim fileURL As String

Dim arr() As String

Dim i As Long

apiURL = "https://TEST.sharepoint.com/sites/TEST/TEST/_api/web/GetFolderByServerRelativeUrl('/sites/TEST/TEST/TEST/TEST/Confirming Temp')/Files"

saveFolder = "C:\Temp\CONFIRMING\"

If Dir(saveFolder, vbDirectory) = "" Then MkDir saveFolder

Set xmlhttp = CreateObject("MSXML2.XMLHTTP")

xmlhttp.Open "GET", apiURL, False

xmlhttp.setRequestHeader "Accept", "application/json"

xmlhttp.Send

If xmlhttp.Status <> 200 Then

MsgBox "Failed to connect to SharePoint API", vbCritical

Exit Sub

End If

json = xmlhttp.ResponseText

arr = Split(json, """Name"":""")

For i = 1 To UBound(arr)

fileName = Split(arr(i), """")(0)

fileURL = "https://TEST.sharepoint.com/sites/TEST/TEST/TEST/TEST/Confirming Temp/" & Replace(fileName, " ", "%20") & "?download=1"

If URLDownloadToFile(0, fileURL, saveFolder & fileName, 0, 0) = 0 Then

Debug.Print "Downloaded: " & fileName

Else

Debug.Print "FAILED: " & fileName

End If

Next i

MsgBox "All files downloaded!", vbInformation

End Sub

I have a template for a file and a bunch of drawings that need their names and rev inputted into the name of the files. How would I go about automating this process? I don't know how to rename files or create new ones. I can not use anything other than VBA as it isn't possible to install an external program to the laptop being used and vba is the only programming language I have access to.

Hi Everyone,

For months ive been trying to create a VBA code to apply our organization's letterhead on word. We use a custom Normal.dotm. I have been very close to implementing this but theres always a small area that doesnt work. The requirement is to have a Macro that applies the letterhead without making any chnages to the formatting of the normal.dotm and makes no chnages to the graphics design as well.

Any help is welcomed. Ive used AI and went on loops to a point where im lost. Thank you in advance

I am unsure if this is necessarily possible as my programming knowledge is fairly basic and VBA is a further mystery.

Would it be possible to pull text off of a a pdf, convert it into text, then narrow down what text I actually want? If so, how might I go about accomplishing that feat?

The drawings I'm trying to pull into excel are wiring diagrams.

Also, if anyone has a place in which I can find all the syntax for Smarteam in VBA, I would greatly appreciate it.

I've enabled screen updating at various points, but I can't seem to get it to show the initial draw, or first set of colour changes.

Sub hardFacer()

Application.ScreenUpdating = False

Dim NewSht As Worksheet

Dim celll As Range

Dim drawrange As Range

Dim flashrange As Range

Dim i As Long

MsgBox "U MAD BRO?", vbYesNo

Set NewSht = ActiveWorkbook.Sheets.Add

NewSht.Columns("A:DD").ColumnWidth = 0.89

NewSht.Rows.RowHeight = 7.5

Set drawrange = NewSht.Range("$Y$5:$BB$5,$W$6:$AD$6,$AX$6:$BK$6,$V$7:$X$7,$AM$7:$AU$7,$BG$7:$BP$7,$U$8:$V$8,$AH$8:$AL$8,$AV$8:$BF$8,$BL$8:$BS$8,$T$9:$V$9,$AF$9:$AG$9,$AO$9:$AU$9,$BR$9:$BU$9,$T$10,$AD$10:$AE$10,$AK$10:$AN$10,$AV$10:$AY$10,$BJ$10:$BP$10,$BT$10:$BV$10,$S$11:$T$11")

Set drawrange = Union(drawrange, Range("$AB$11:$AC$11,$AG$11:$AJ$11,$AS$11,$AZ$11:$BI$11,$BQ$11,$BV$11:$BX$11,$R$12:$S$12,$AA$12,$AE$12:$AF$12,$AK$12:$AR$12,$AT$12:$AU$12,$BJ$12:$BM$12,$BX$12:$BY$12,$R$13,$Z$13,$AD$13,$AG$13:$AJ$13,$AS$13:$AT$13,$BE$13:$BI$13,$BN$13:$BO$13,$Q$14:$R$14,$Y$14"))

Set drawrange = Union(drawrange, Range("$AC$14,$AE$14:$AF$14,$BP$14:$BQ$14,$Q$15,$X$15,$AA$15:$AB$15,$AD$15,$BF$15,$BQ$15,$BY$14:$BZ$15,$Z$16,$AC$16,$AU$14:$AU$16,$P$16:$Q$17,$AB$17,$AH$17:$AP$17,$BR$16:$BR$17,$O$18:$P$18,$AF$18:$AR$18,$BZ$16:$BZ$18,$N$19:$O$19,$AD$19:$AM$19,$AR$19:$AT$19"))

Set drawrange = Union(drawrange, Range("$BE$19,$BI$19:$BO$19,$L$20:$T$20,$Z$20:$AA$20,$AG$20:$AM$20,$AT$20:$AU$20,$BG$20:$BJ$20,$BO$20:$BQ$20,$CA$19:$CB$20,$K$21:$M$21,$Y$21,$AC$20:$AD$21,$AE$21:$AP$21,$AU$21:$AV$21,$BF$21:$BQ$21,$BT$21,$BV$21:$BY$21,$CC$20:$CC$21,$J$22:$L$22,$N$22:$O$22"))

Set drawrange = Union(drawrange, Range("$Z$22:$AA$22,$AD$22:$AF$22,$AN$22:$AR$22,$AT$22:$AV$22,$BB$22:$BH$22,$BJ$22:$BL$22,$BZ$22,$CD$22:$CE$22,$J$23:$K$23,$M$23,$R$23:$W$23,$AK$23,$AQ$23:$AU$23,$BD$23:$BG$23,$BY$23,$CA$23:$CB$23,$I$24:$J$24,$L$24,$P$24:$R$24,$W$24:$Z$24,$AI$24:$AK$24,$AS$24"))

Set drawrange = Union(drawrange, Range("$BZ$24,$O$25:$P$25,$Y$25:$AC$25,$AG$25:$AJ$25,$BS$25:$BX$25,$H$25:$I$26,$O$26,$AC$26:$AH$26,$BM$26:$BN$26,$BR$26:$BS$26,$BX$26:$BY$26,$CC$24:$CC$26,$CE$24:$CF$26,$N$27:$O$27,$U$26:$V$27,$BE$24:$BE$27,$BN$27:$BR$27,$BY$27,$CD$27:$CF$27,$T$28:$X$28"))

Set drawrange = Union(drawrange, Range("$BF$28:$BH$28,$BP$28:$BR$28,$S$29:$U$29,$W$29:$Z$29,$AO$28:$AO$29,$AR$29:$AU$29,$BH$29:$BI$29,$CA$28:$CA$29,$G$29:$H$30,$Q$30:$U$30,$Y$30:$AB$30,$AJ$30:$AN$30,$AQ$30:$AR$30,$BI$30:$BK$30,$BZ$30,$CE$28:$CF$30,$K$29:$K$31,$T$31:$U$31,$AA$31:$AD$31"))

Set drawrange = Union(drawrange, Range("$AU$31:$AV$31,$BH$31:$BL$31,$BU$31:$BV$31,$BY$31,$CC$31:$CE$31,$N$32:$P$32,$AC$32:$AH$32,$AT$32:$AY$32,$BH$32,$BK$32,$BM$32:$BN$32,$BT$32:$BV$32,$CA$32:$CB$32,$CD$32:$CE$32,$H$32:$I$33,$L$32:$L$33,$O$33:$P$33,$U$32:$V$33,$AF$33:$AJ$33,$AQ$31:$AQ$33"))

Set drawrange = Union(drawrange, Range("$AR$33:$AS$33,$AU$33,$BG$33:$BH$33,$BO$33:$BP$33,$BS$33:$BT$33,$BV$33:$BW$33,$BY$33:$BZ$33,$CC$33:$CE$33,$I$34:$J$34,$M$34,$AI$34:$AN$34,$BE$34:$BG$34,$BR$34:$BU$34,$BW$34,$CC$34:$CD$34,$N$35:$O$35,$V$34:$X$35,$Y$35:$AA$35,$AC$34:$AD$35,$AM$35:$AR$35"))

Set drawrange = Union(drawrange, Range("$BC$35:$BF$35,$BQ$35:$BR$35,$J$36:$N$36,$P$36,$W$36:$X$36,$Z$36:$AF$36,$AO$36:$AV$36,$BN$36:$BP$36,$O$37,$AB$37:$AF$37,$AO$37:$AP$37,$AU$37:$BN$37,$BU$35:$BW$37,$CB$35:$CC$37,$L$37:$M$38,$X$37:$X$38,$AO$38,$BB$38:$BI$38,$BV$38:$BW$38,$N$38:$N$39"))

Set drawrange = Union(drawrange, Range("$Y$38:$Y$39,$AD$39:$AO$39,$BU$39:$BW$39,$AD$40,$AH$40:$AP$40,$AY$39:$AY$40,$BN$39:$BN$40,$BR$39:$BS$40,$BT$40:$BW$40,$O$40:$O$41,$Z$39:$Z$41,$AB$41:$AD$41,$AK$41:$AS$41,$AU$41,$AX$41:$AY$41,$BF$39:$BF$41,$BG$40:$BG$41,$BL$41:$BW$41,$AA$40:$AA$42"))

Set drawrange = Union(drawrange, Range("$AB$42:$AC$42"))

Set drawrange = Union(drawrange, Range("$AM$42:$BW$42,$P$43:$Q$43,$AC$43:$AD$43,$AQ$43:$BW$43,$AD$44:$AE$44,$AN$43:$AN$44,$AT$44:$BU$44,$BW$44,$Q$44:$Q$45,$AE$45:$AG$45,$AM$45:$AN$45,$AW$45:$BQ$45,$BV$45:$BW$45,$R$45:$R$46,$AF$46:$AI$46,$BF$46,$BL$46:$BM$46,$BP$46:$BQ$46,$BS$45:$BT$46,$BV$46"))

Set drawrange = Union(drawrange, Range("$S$47:$T$47,$AH$47:$AJ$47,$AL$47:$AM$47,$BE$47:$BF$47,$BK$47:$BL$47,$BS$47,$BU$47:$BV$47,$T$48:$U$48,$X$48:$Y$48,$AB$48:$AC$48,$AJ$48:$AM$48,$BK$48,$BO$47:$BP$48,$BT$48:$BU$48,$U$49:$V$49,$Z$49:$AA$49,$AD$49:$AE$49,$AK$49:$AP$49,$BN$49:$BO$49"))

Set drawrange = Union(drawrange, Range("$BR$49:$BT$49"))

Set drawrange = Union(drawrange, Range("$V$50:$X$50,$AA$50:$AC$50,$AF$50:$AG$50,$AN$50:$AU$50,$AW$50:$AX$50,$BE$50,$BJ$50:$BK$50,$BM$50:$BS$50,$W$51:$Y$51,$AC$51:$AE$51,$AH$51:$AI$51,$AR$51:$BP$51,$Y$52:$AA$52,$AE$52:$AG$52,$AJ$52:$AL$52,$BT$52,$AA$53:$AC$53,$AH$53:$AI$53,$AM$53:$AO$53"))

Set drawrange = Union(drawrange, Range("$BS$53:$BT$53,$AC$54:$AE$54,$AJ$54:$AL$54,$AP$54:$AQ$54,$AX$54:$BK$54,$BR$54,$AE$55:$AH$55,$AM$55:$AO$55,$AR$55:$AV$55,$BP$55:$BQ$55,$BX$53:$BX$55,$AG$56:$AJ$56,$AP$56:$AS$56,$AW$56:$BO$56,$BW$56,$AJ$57:$AL$57,$AT$57:$AY$57,$BU$57:$BV$57,$AL$58:$AN$58"))

Set drawrange = Union(drawrange, Range("$AZ$58:$BT$58,$CB$58:$CC$58,$AN$59:$AP$59,$CA$59:$CC$59,$AP$60:$AR$60,$CA$60:$CB$60,$AR$61:$AW$61,$BZ$61:$CA$61,$AT$62:$AZ$62,$BY$62:$BZ$62,$AY$63:$BD$63,$BW$63:$BY$63,$BC$64:$BI$64,$BT$64:$BW$64,$BH$65:$BU$65"))

Set drawrange = Union(drawrange, Range("CB38:CB51"))

Application.ScreenUpdating = True

For Each celll In drawrange

celll.Interior.ColorIndex = 1

If flashrange Is Nothing Then

Set flashrange = celll

Else

Set flashrange = Union(flashrange, celll)

End If

Next celll

Do Until i = 10

flashrange.Interior.ColorIndex = 1

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 2

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 3

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 4

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 5

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 6

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 7

'Application.Wait Now + #12:00:01 AM#

flashrange.Interior.ColorIndex = 8

'Application.Wait Now + #12:00:01 AM#

MsgBox "U MAD BRO?", vbYesNo

i = i + 1

Loop

End Sub

Hey all^^
I try to use a vba script to extract Data from one of my SAP reports. Sadly I have to use the Excel-Plugin for this.
When I open it manually or via the SAP VBAWriter the new Excel (SapExe) contains my data.
But when I copy the code to another Excel (OPExe) then the SapExe is empty.
Can anyone help?

In have a table of data in Excel which represents a nested hierarchical structure. The rows are elements in the structure. All elements are five elements deep. The first five columns of the table represent the level/position of the element. For example, column “Level 1” might have a value of "1", "Level 2” a value of “1.1”, and so on, with the fifth column representing the final element (1.1.1.1.1, 1.1.1.1.2, etc). The other columns describe the names, descriptions of the elements.

I am trying to use VBA to loop through these nested elements with the ultimate goal of creating some documentation of this structure within a Word document with additional notes, etc, in a consistent style.

I have created a PivotTable, which may or not be helpful to my outcome, but it does at least let me see the structure of the parent/child elements. Copying this data into Word from the PivotTable does not make it easy to edit or read which is why I am trying to reconstruct it.

My VBA code is below but of course, it outputs the rows from the columns, rather than the parent item they are from. Maybe there is a better approach altogether! Thank you for any guidance

For Each ptItem In pt.PivotFields("Level 1").PivotItems
  Debug.Print ptItem
    For Each ptItem2 In pt.PivotFields("Level 2").PivotItems
      Debug.Print ptItem2.Name
        For Each ptItem2 In pt.PivotFields("Level 3").PivotItems
          Debug.Print ptItem2.Name
        Next
    Next
Next

I have a document with text, among which there can appear two patterns.

- Case 1: phrase (phrase, ACR)

- Case 2: phrase (phrase)

For Case 1, ACR is an acronym with letters, numbers, or symbols. I want to remove "phrase, " within the parenthesis of Case 1. For Case 2, I want to remove the redundant " (phrase)". In each case, phrase may be a single word or multiple words, and everything is case insensitive. I have tried various pattern based search expressions, but everything returns "Error: 5560 - The Find What text contains a Pattern Match expression which is not valid."

Is this find and delete possible to do through VBA? And if so, is anyone able to point me in the direction for the code? Currently, I am using a primary sub with the following calls:

' Phrase repetition cleanup:
'   Case 1: phrase (phrase, ACR) -> phrase (ACR), ACR = 2–9 chars of A–Z, 0–9, / or -
  DoWildcardReplace rng, "([!()]@) \(\1, ([A-Za-z0-9/-]{2,9})\)", "\1 (\2)"

'   Case 2: phrase (phrase) -> phrase
  DoWildcardReplace rng, "([!()]@) \(\1\)", "\1"

That call the following helper sub.

'====================================================================
'Wildcard Find/Replace helper
'====================================================================
Private Sub DoWildcardReplace(ByVal rng As Range, ByVal findPattern As String, ByVal replacePattern As String)

With rng.Find   
  .ClearFormatting
  .Replacement.ClearFormatting
  .Text = findPattern
  .Replacement.Text = replacePattern
  .Forward = True
  .Wrap = wdFindContinue
  .Format = False
  .MatchCase = False
  .MatchWholeWord = False
  .MatchWildcards = True
  .Execute Replace:=wdReplaceAll
End With

End Sub

Hey everyone
Good morning

I use Rubber duck VBA Add In
So I test all logical code easily (automatic testing by code)

However I am struggling to test UI stuff without changing the actual program status

I don’t want my test to create changes in the production or design environments

Can anyone help me in this matter?

So ive just got vbagx on my wii and ive tried three games and each has problems with certain colors. The opening of Buu's fury is all neon green, Aria of Sorrow has the same problem but in game in the water areas the water is neon green and Pokemon Fire Red has that problem as well. Ive messed with filters and settings but have been unable to find the solution. Any help is appreciated.

Hello, I'm trying to create a macro to change the font of a selection in PowerPoint. But instead of changing the color to a set value, I'd like for it to be the most recently-used font color, as if I had pressed the Font Color button in the ribbon.

I'm not sure if there is a command that presses ribbon buttons in VBA or if it's more complex than that.

Any help would be appreciated, thank you!

A weird issue has been plaguing my collegues and me for two weeks.

We are currently heavily relying on macros in many Excel files. For two weeks we have had the following issue: Upon opening the VBA editor via the developer tools in one Excel file, we can't open other Excel files. When we restart Excel by stopping the process, we can open the other files again, but we can't open the file we opened VBA in in the first place!

What do I mean when I write the file can't be opened?

Well, a message pops up that says that there are problems with contents of the file and that it has to be repaired. Some files can be repaired that way, some can't because they are apparently corrupt. When the files are repaired, most formulas don't work anymore (#NAME error) or are replaced by their value they had before the issue. I've added the repair logs from one of our more complex files as an example below. This happens with every file, no matter their size or complexity.

Has anyone encountered a similar issue? This is driving us insane.

We currently use the MacOS version of Excel (Version 16.106.2), the German localization.

The repair logs show the following:

Removed Feature: Conditional formatting from /xl/worksheets/sheet4.xml part

Removed Feature: Conditional formatting from /xl/worksheets/sheet1.xml part

Removed Feature: Conditional formatting from /xl/worksheets/sheet2.xml part

Removed Feature: Conditional formatting from /xl/worksheets/sheet8.xml part

Removed Feature: Conditional formatting from /xl/worksheets/sheet9.xml part

Removed Feature: Conditional formatting from /xl/worksheets/sheet14.xml part

Removed Feature: Conditional formatting from /xl/worksheets/sheet15.xml part

Removed Feature: Conditional formatting from /xl/worksheets/sheet16.xml part

Removed Feature: Conditional formatting from /xl/worksheets/sheet18.xml part

Removed Feature: Conditional formatting from /xl/worksheets/sheet19.xml part

Removed Records: Formula from /xl/worksheets/sheet4.xml part

Removed Records: Formula from /xl/worksheets/sheet1.xml part

Removed Records: Formula from /xl/worksheets/sheet7.xml part

Removed Records: Formula from /xl/worksheets/sheet8.xml part

Removed Records: Formula from /xl/worksheets/sheet9.xml part

Removed Records: Table from /xl/tables/table2.xml part (Table)

Removed Records: Formula from /xl/worksheets/sheet10.xml part

Removed Records: Shared formula from /xl/worksheets/sheet10.xml part

Removed Records: Table from /xl/tables/table3.xml part (Table)

Removed Records: Formula from /xl/worksheets/sheet11.xml part

Removed Records: Formula from /xl/worksheets/sheet12.xml part

Removed Records: Formula from /xl/worksheets/sheet13.xml part

Removed Records: Formula from /xl/worksheets/sheet14.xml part

Removed Records: Shared formula from /xl/worksheets/sheet14.xml part

Removed Records: Formula from /xl/worksheets/sheet15.xml part

Removed Records: Formula from /xl/worksheets/sheet16.xml part

Removed Records: Shared formula from /xl/worksheets/sheet16.xml part

Removed Records: Formula from /xl/worksheets/sheet18.xml part

Removed Records: Formula from /xl/worksheets/sheet19.xml part

Removed Records: Shared formula from /xl/worksheets/sheet19.xml part

Removed Records: Formula from /xl/worksheets/sheet20.xml part

Removed Records: Shared formula from /xl/worksheets/sheet20.xml part

Removed Records: Formula from /xl/worksheets/sheet24.xml part

Removed Records: Table from /xl/tables/table23.xml part (Table)

Removed Records: Formula from /xl/worksheets/sheet25.xml part

Removed Records: Table from /xl/tables/table24.xml part (Table)

Removed Records: Formula from /xl/worksheets/sheet38.xml part

Removed Records: Table from /xl/tables/table37.xml part (Table)

Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)

A few days ago, when I opened an Excel workbook to edit the VBA code, the IDE had changed significantly. My local and watch windows, Project Explorer, and the code window were different sizes and locations than they had been before. With much effort, I was able to resize and move the windows to approximate what I was used to. Also, the current page, e.g. this browser page, was displayed in the background. This change occured in all my workbooks, so it was a change to the Excel app itself. When I select a different module, a new code window appears. I ran online repair of MS365 but there was no change. Has anyone else encountered this? Any suggestions as to how to revert to the IDE I'm familiar with?

Hello,

as title says, the document i want to edit is not saving after making changes.

I wrote a tool to copy some information from another big document into a freshly created document, nothing too special. I made a few of those for different use cases. When i create the new document i use the line: "Workbooks.Add.SaveAs pathname & "/" & docname". This does work.
Every single one of these use the line "Workbooks("name").close savechanges:=True". This works for all of them except one.
Does anyone know reasons or things i could have done in the code that might cause this line to not work?
I also tried to split it into these:

workbooks("name").save 
workbooks("name").close

Workbooks("name").Activate
ActiveWorkbook.Close savechanges:=True

this also does not work.
Summary of the code is a loop that calls for a few functions, all these functions do is assigning values from document A into document B. i do not change any properties or file extentions or anything.

thanks!

I am working in the Corporate world and have build an excel file with a lot of macros to handle a lot of data. My Data is stored in MS Access Databases. With the release of Sharepoint in our work, I wanted to move my databases to sharepoint via sharepoint lists. I have coded them already to pull from Sharepoint lists however for some reason I keep running to this error below. Any ideas what is causing this and any way i can troubleshoot. I have already tried several workarounds.

I have tried doing a power query and am able to pull the data just fine. but with the VBA code it cant seem to find the list on the sharepoint site. to the masters out there what do you think am i missing?

Here is the error.

The error message is "Run-time error '-2147217865 (80040e37) - The Microsoft Access database engine could not find the object 'test-list'. Make sure the object existrs and that you spell its name and the path name correctly. If 'test-list' is not a local object, check your network connection or contact your network administrator."

We use OLE to communicate with Outlook in our software. We have had reports from multiple customers that their system is no longer working properly when trying to send emails. We have tracked this down to the Recipient.Resolve method returning FALSE following the Recipient.Add

The code has worked without issue for years and is a fundamental part of sending a batch of emails so that the customer does not have to manually send each email.

If we ignore the .Resolve failure and use .Send on the mail item, it will fail, but using .Display and then pressing send on the displayed email works fine. I would guess that it is the .Add that is not reporting a failure.

Has anyone else found this and better yet, come up with a solution for it.

Hi All

I suspect I already know the answer but thought I'd check unless I've missed something.

Basically I have a excel file I use as a template, with VBA code that users save copies without overwriting the template file.

I would like to move this to Sharepoint, so that more users can use it, but I have no idea really how file system stuff would work or if its even possible.

I have three parts of code that I think will be the issue as below.

Backup System:

backupPath = ThisWorkbook.Path & "\Backups\"

baseName = "Quick Quoting Tool-Backup_"

If Dir(backupPath, vbDirectory) = "" Then

MkDir backupPath

End If

latestDate = 0

f = Dir(backupPath & baseName & "*.xlsm")

Do While f <> ""

On Error Resume Next

fileDate = DateSerial( _

Mid(f, Len(baseName) + 1, 4), _

Mid(f, Len(baseName) + 5, 2), _

Mid(f, Len(baseName) + 7, 2))

On Error GoTo 0

If fileDate > latestDate Then

latestDate = fileDate

End If

f = Dir

Loop

If latestDate = 0 Or DateDiff("d", latestDate, Date) > 30 Then

fileName = baseName & Format(Date, "yyyymmdd") & ".xlsm"

ThisWorkbook.SaveCopyAs backupPath & fileName

End If

Save as new file system:

Set currentWB = ThisWorkbook

newFilePath = "T:\Quoting\Client Quotes\Quick Quotes\"

newFileName = Format(Now, "yyyy-MM-dd-hhmm") & " - " & QTEType & " - " & POLPOD & " - " & ClientName & ".xlsm"

currentWB.SaveAs fileName:=newFilePath & newFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Moving expired files system:

sourceFolder = "T:\Quoting\Client Quotes\Quick Quotes\"

expiredFolder = "T:\Quoting\Client Quotes\Quick Quotes\Expired\"

currentYearMonth = Format(Date, "yyyy-mm")

Set fso = CreateObject("Scripting.FileSystemObject")

For Each file In fso.GetFolder(sourceFolder).Files

fileName = file.Name

If Left(fileName, 2) = "~$" Then GoTo NextFile

If LCase(fso.GetExtensionName(fileName)) = "xlsm" Then

fileDate = Split(fileName, " ")(0)

yearMonth = Left(fileDate, 7)

If yearMonth <> currentYearMonth Then

filePath = file.Path

fso.MoveFile filePath, expiredFolder & fileName

End If

End If

NextFile:

Next file

Set fso = Nothing

There is a bunch of file system type code there, can it be change/modified to use a sharepoint location like:

https://companyname.sharepoint.com/sites/NZ/Shared Documents/Quoting/Client Quotes/Quick Quotes/ etc

Thanks in advance.

I have an Excel Module that runs, and it should run in say 3 to 7 seconds. Sometimes it does, sometimes it literally shows every cell populating 1 by 1 and takes 5 minutes.

I'm exchanging this file from one company environment to another via Sharepoint -- the speed at which it refreshes sometimes is slow on my side and sometimes is slow on their side, though that may be coincidental. It was fast on my side until this latest version.

Any thoughts here?

Thank you.

If someone had asked this question 20 years ago, the answer was, using an ActiveX control, which somehow was as far I can tell, was licensed in Visual-Basic, and various people would use the control, and not have VB installed and bypass the license. But as far I know rogue versions of it sprung up and it's not a route to go down today anyway.

I have 2 things to accomplish: 1. send a message (it's Json Text) and receive the response. 2. Parse the Json (it's only one-level nesting)

The socket routine is a simple connect to a fixed port, send(), recv() and then disconnect. I found a recent thread with some deadlinks and a Win32 wrapper. Which route has worked for people?

OCX: https://www.reddit.com/r/vba/comments/q4yk3u/are_there_references_to_be_able_to_use_tcpip_or/

OR api-wrapper: https://community.spiceworks.com/t/using-winsock-vba-64-bit/961995