311 questions
0
votes
1
answer
124
views
VBA PowerQuery Directory
I am using VBA to try and hopefully automate my PowerQuery needs.
I've recorded a macro from the import data from PDF in the data tab.
I have then tried creating a user definable source path within ...
- 13
3
votes
0
answers
229
views
Passing strings between Excel and Delphi 12
I currently have a Delphi DLL which takes in 2 parameters as PChar and another parameter that outputs as a PChar to Excel. In Excel these are all string, but whenever I run the macro in Excel, my ...
- 31
2
votes
1
answer
208
views
TextJoin Function could not merge more than 142 columns/cells
I want to merge cells/particular columns from one row till next non-empty cell in row-3. But i got #CALC! error. Is there any solution to this. Getting same error in CONCAT function. Below is my code, ...
1
vote
1
answer
86
views
RegExp that removes '/', hangs on cell that requires format change
I'm attempting to import a subset of information from one .xlsx file into another spreadsheet. Some of the data is 'polluted' with unwanted characters. I need to extract substrings from some cells, so ...
- 121
0
votes
3
answers
203
views
want to split data depends on multiple columns
I want to split data on basis of A & B-column, if next row is same from A-column then look into B-column. if next row is same from B then copy F, G H-column till value from B is unchanged. Its ...
1
vote
1
answer
64
views
Error when creating additional fields during data upload (Excel to Access)
I am using this code to copy the data from excel sheets to access db.
For each table i am creating additional 2 columns to hold the excel file name (Data.xlsx) and run date.
I am getting "ByRef ...
- 31
0
votes
0
answers
49
views
Markov Chain prediction using Excel Macro - accuracy differs significantly in real time prediction and back testing
I have a macro for inventory replenishment prediction using Markov Chain. It uses data for last 10 days including today to predict inventory replenishment (rep R) or not (blank B).
The model works ...
- 1
2
votes
2
answers
50
views
Word document with command buttons -- copy entire document except for command buttons?
I have a word document that has a "Reset Fields" command button and a "Copy" button. Once the fields are filled in I need to copy the document to paste in an email and/or other ...
- 21
0
votes
2
answers
105
views
vba AutoFilter Cells with blank background color but have data
how to AutoFilter Cells with blank background color but have data?
What value do I need to pass in colorIndex to get the following results ?
enter image description here
Sub FilterByColor()
Dim ...
0
votes
2
answers
272
views
VBA Fahrenheit to Celsius
How do I write this function?
Function Fahrenheit2C()
Dim f As Integer
' Convert Fahrenheit to Celsius
Fahrenheit2C = (f - 32) / 1.8
End Function
I get "value errors", even ...
3
votes
2
answers
147
views
Memory not released when using Dictionarys in 64-bit Excel
When I store data in a dictionary, the memory is not released after it goes out of scope or is set to Nothing. I have to explicitly use the RemoveAll method to release the memory.
This problem occurs ...
- 41
0
votes
0
answers
199
views
The database cannot be opened because the VBA project contained in it cannot be read when open .accdb
https://i.sstatic.net/57JO73HO.png
Message error
I received a request from a customer who wanted to run the Access system on both 32bit and 64bit environments.
Example code:
' Windows API‚
#If Win64 ...
-1
votes
2
answers
99
views
Function as variant in for loop return "Object required" Error
I try to make cell A1, A2, A3 = aa
using code below but getting error "Object required".
Public Function cellv(ByVal row As Variant, col As Integer) As Variant
cellv = ThisWorkbook....
- 3
0
votes
1
answer
34
views
Application.OnKey procedure not available when .xlsm opened from Sharepoint Online
I'm using Application.OnKey to intercept attempts to use Ctrl + X and Ctrl + V to cut and paste all, and instead notify the user that they must instead use Ctrl + C and then Ctrl + Alt + V to open '...
0
votes
0
answers
82
views
Type Mismatch (PowerPoint Office 365 VBA)
Sample code:
Sub CreateSlides()
'Open the Excel workbook. Change the filename here.
Dim OWB As Excel.Workbook
Set OWB = Excel.Application.Workbooks.Open("C:\Users\willi\OneDrive\Documents\EDU\...
0
votes
1
answer
59
views
Save a backup when closing a UserForm
I wrote code to save a backup when closing a UserForm.
It works while the workbook is visible or when I click the save button.
Code for Workbook_BeforeSave.
Privat sub Workbook_BeforeSave(byvale ...
0
votes
1
answer
72
views
Copy range text including soft hyphens from Word to Excel or Access while avoiding clipboard
I'm trying to copy text inside a cell of a narrow Word table including soft hyphens produced by MS Word's AutoHyphenation feature to Excel or Access.
It works, when the soft hyphens are already ...
- 106
0
votes
0
answers
70
views
TypeOf doesn't work properly after resetting VBA
Setup
Given the following code (inside "ThisWorkbook") and the excel object "MainTable", which is the auto generated class module of my first Table just renamed, I get unexpected ...
- 1,469
0
votes
1
answer
78
views
'only one SQL statement allowed' VBA error, when reading from one database and updating another
I'm using VBA 7 with MS Word to change field values in a SQLite database. I need to read every record in a table from one database, change it value and update some record in another table from second ...
- 1
0
votes
2
answers
101
views
How to set the print area of an array of worksheets?
Good day. I was creating a macro where i set an array of worksheets to have a certain print area.
Unfortunately the error "Runtime Error '438' Objcects dosent support this property or method"...
- 21
0
votes
1
answer
76
views
How can i printpreview multiple excel sheets, with the names of the sheets located in a range?
Good day. I was trying to print preview multiple sheets using an array. I was trying to set an array to a range and using the print preview function that way. Unfortunately it did not work.
The thing ...
- 21
-1
votes
1
answer
47
views
Problems with cancel/accept inputbox button VBA
I have an inputbox that asks the user for the number of relatives they live with. But in the inputbox when I clicked on the cancel or accept button I got a type mismatch error. However, what I was ...
0
votes
1
answer
157
views
Put attributes in the XML file using VBA
How do I put the attributes below that are in the node envideclaracaoautodesembaraco in my vba code. I want my .xml file to generate the same attributes as the example below.
<...
0
votes
1
answer
85
views
Adjust export script (excel->XML) to combine nodes under parent
How do I export my xml file as per the example using the data generated from Excel below:
[sample input data](https://i.sstatic.net/hz68v.png)enter image description here
Below is my xml of data ...
0
votes
1
answer
86
views
Create an XML file using spreadsheet data with Visual Basic
The VBA codes generate an xml file as per the model below:
<Produtos xmlns="http://www.test.com/engine/3">
<Item_1>
<UF xmlns="" yid="1">AM</UF&...
0
votes
0
answers
62
views
Excel VBA7 Run-time error '453': Can't find DLL entry point GetUnits in
I'm calling one of the many functions from a DLL using this code:
Declare PtrSafe Function GetUnits Lib "C:\Users\svd.dll" () As String
and I'm using the function with following code:
...
- 1
0
votes
2
answers
135
views
Excel macro to compare adjacent cell value and highlight cell with same value in another column
I have data of questions, choices & answers.
Col-A has questions and choices starting with question 1 and choices A,B,C,D. Similarly I have 500 questions and each has choices.
Col-B has questions &...
- 195
0
votes
0
answers
57
views
VBA showing error : method 'default' of object 'range' failed
Sub sec()
N = Range("B5")
c = 0
Do While Cells(5, c + 3) <> " "
c = c + 1
Loop
For i = 1 To c
N = N + Cells(5, i + 2) / ...
0
votes
1
answer
87
views
How to evaluate this function in VBA?
I am trying to use the below Excel formula in my VBA code but can't get it to work:
=Rows(Unique(Filter(myRange, myRange))
This function works as expected in Excel, and the range I am using is a ...
0
votes
1
answer
42
views
Search for part of a word, then replace the whole word
I am trying to search for a certain part of a word, then replace the whole word.
Imaginary example:
The document contains two words: "when, where"
I want to search for "wh" then, ...
0
votes
0
answers
29
views
How can I bind two separate sub-tabs to different tables in a main tab (VBA7)?
Basically there is a main tab control called TabCtl0 where we can
switch from menu to other menus. For example when page1 in TabCtl0 is
clicked it goes to the Dashboard and page2 goes to Reports and ...
- 143
0
votes
1
answer
59
views
Why first letter in the first name deleting itself when typing the second letter; VBA 7?
This is the where we enter the value = txtSearchCenter
This is where the result will be = lstPersonnelSearch
Option Compare Database
Private Sub txtSearchCriteria_KeyPress(KeyAscii As Integer)
...
- 143
0
votes
1
answer
151
views
Get AppointmentItem with GlobalAppointmentID using VBA
Based on this post, it seems I cannot set a variable to the appointmentItem by using GlobalAppointmentID, either by the Restrict or Find methods.
This post seems to be utilizing C#. Is it possible to ...
- 43
1
vote
3
answers
3k
views
VBA Excel Sumif and sumifs function
Sales Details
Month
Amount
January
120
March
250
April
380
June
510
January
640
March
770
April
900
June
1030
January
1160
March
1290
April
1420
January
1550
Noted: all vba code write below.........
...
0
votes
0
answers
241
views
Automate Updating Text boxes in PP with Excel data
This VBA code, titled "UpdateTextboxesFromExcel," is designed to interact with both Microsoft Excel and PowerPoint to read a specific cell value from an Excel workbook and update a textbox ...
0
votes
1
answer
130
views
Update the file paths dynamically in network drives using Word VBA
I'm trying to create an Excel object in Word which reflects live update of data from Excel.
My use case is to update the object path dynamically when two files, Word and Excel are being sent to ...
0
votes
2
answers
88
views
Automate creating new PP/Excel sheets and updating the links
I'm having troubles with a VBA code I have written which is designed to do the below:
Define File Paths:
The macro begins by defining several string variables for file paths: originalPptPath, ...
0
votes
1
answer
407
views
I have a dll, Prorfl.dll from HotelLock. I am integrating it into my Access VBA, but it is giving error nor result
The Prorfl.dll has been referenced as follows
Declare Function initializeUSB Lib "C:\Klax\WKNL\proRFL.dll" (ByVal fUSB As Byte) As Boolean
I get an error calling the function InitializedUSB(...
- 3
-1
votes
3
answers
443
views
Is there a vba code that can parse out specific data from a single cell that has multiple paragraphs in excel?
I am in need of some help please. I have found multiple vba codes that can help with pulling certain characters in a cell, but the trouble I am having is that a single cell has multiple paragraphs ...
- 1
0
votes
1
answer
79
views
VBA - How to make a sub tab control form appear in only a specific page of the main tab control form?
So, I have a main tab control form called TabCtl11. The side left bar menu; for example, when Dashboard Menu is clicked it correspond to the page1 of the main tab control and Report Menu when clicked ...
- 143
0
votes
0
answers
74
views
There is an error on line 12 of custom code BC30205 End of statement expected using rdl VBA
I am working on an application I am facing an issue below is the error and the code
[rsCompilerErrorInCode] There is an error on line 12 of custom code: [BC30205] End of statement expected using rdl ...
- 1,111
0
votes
2
answers
125
views
Issue an IP address based on condition, cell value of 1
I am trying to create an Excel Function which issues an IP address based on a flag condition. The flag is the presence or absence of a 1.
If a 1 is present then an IP address should be issued.
The ...
- 3
0
votes
1
answer
83
views
Stuck in converting one complex formula into VBA
Below is the formula:-
=DROP(REDUCE("",A2:INDEX(A:A,COUNTA(A:A)),LAMBDA(x,y,LET(z,BYROW(C2:C5,LAMBDA(v,SUM(N(v=x)))),VSTACK(x,TAKE(TOCOL(IFS((D2:D5+z)=MIN((D2:D5+z)),C2:C5),2),1))))),1)
...
- 73
0
votes
2
answers
72
views
VBA, If array has a string or not
I'm trying to parse through a worksheet with raw data. Sometimes the data I have may be of 2 parts (in my case Machines & production, or just Production). Not really competent in VBA, but I split ...
0
votes
1
answer
36
views
How do I create VBA code that is associated with a particular worksheet in C#?
I wrote a program in VBA a number of years that reads in files and builds a macro enabled spreadsheet. It works reasonably well. I am rewriting this code in C# with enhancements. The problem that I ...
0
votes
1
answer
65
views
VBA Subroutine Does Not Retain Bookmarks in Word
I generated the VBA code below in order to populate a large Word template document from Excel. My goal is to update a bookmark and then the associated cross references to that bookmark to update as ...
0
votes
1
answer
599
views
Connection to MariaDB Database on a Synology NAS via Excel VBA is denied
I'm trying to connect Excel VBA (Office 365, Windows 11) with a MariaDB Database. At first the MariaDB Server was running on my local machine and everything was running. But now the Database Server is ...
1
vote
1
answer
560
views
How to filter tasks in MS Project, using VBA, and with multiple conditions?
I have a line of VBA code that creates a filter with one condition, this works well:
FilterEdit Name:="temp", TaskFilter:=True, Create:=True, OverwriteExisting:=True, FieldName:="...
- 13
-1
votes
1
answer
201
views
"DLL-Entrypoint 96 in oleaut32.dll was not found" error message occurs on my PowerPoint for Mac (Translated from German)
For the last few days, when I open PowerPoint (Version 16.79 used on M1 MacBook Air), the error message "Microsoft Visual Basic - DLL-Entrpoint 96 in oleaut32.dll was not found" occurs every ...
- 1
0
votes
1
answer
93
views
Copy List of Tables from Excel to Word
I am trying to copy list of Tables in a single sheet to Word.
The tables are pasted in the beginning of document.
Is there any way to paste at bookmarks?
Also can the code contain manner to format ...
