|
Office-365/Excel/Code-vba-Makro
Excel: REwrite RTF to HTML
|
23.04.2020 339
|
|
The following
format enumerations are used to convert characters to HTML in RTF:
In case of double
underlines
XlUnderlineStyle:
xlUnderlineStyleDouble -4119 Double fat underline
xlUnderlineStyleDoubleAcco
|
|
Office-365/Excel/Tutorials
Excel Instructions: Remove Duplicate Rows, Remove Duplicates
|
22.04.2020 300
|
|
Remove duplicate rows , remove duplicates
To remove duplicate rows or unnecessary lines:
You select a range or table in Excel
Then Excel->Menu-> Data -> Remove duplicates
Then you can select the columns after which you want
|
|
Office-365/Excel/Tutorials
Excel grouping from top to bottom
|
02.03.2020 343
|
|
Excel grouping from top to bottom
How to flip the grouping sign from bottom to top in Excel when opening
in standard mode excel displays the grouping character below the grouping
In theclosed state, the grouping character is below the hidden area
|
|
Office-365/Excel/Code-vba-Makro
Excel: Convert numbers, date and time to text
|
14.01.2020 469
|
|
How to convert entire columns or ranges
in Excel from numbers, date or time format to real text
|
|
Office-365/Excel/Formulas
Excel : Convert numbers to text
|
10.01.2020 411
|
|
The following example shows how to convert numbers to text in Microsoft Excel
The problem is that SAP used to output numbers such as personnel numbers or accounts in Excel as text You could see it by the fact that the numbers were displayed left-aligned in Excel
Since
|
|
Office-365/Excel/Templates
Excel : Encoding values with HASH functions SHA256
|
04.12.2019 3699
|
|
Applies to
Microsoft Excel, Access Word Outlook PowerPoint, Secure Encryption
The following
file encodes text with the SHA256 hash into encrypted values
To do this, the
appended code is inserted into vba macro
You can use the
Excel function to
=get
|
|
Office-365/Excel/Templates
Excel : Anonymizing and pseudonymizing numbers
|
04.12.2019 352
|
|
With a simple vba
function
Download with
Excel File as an example
The following
Excel file anonymizes data based on a unique random number
Anonymization:
Anonymization
changes sensitive critical data to another non-easily derived new replacemen
|
|
Office-365/Excel/Tutorials
Excel: Replace text in a selected area
|
26.11.2019 345
|
|
Guide:
I want to change text in the cells of a defined Excel range
How do you do that?
You mark the area in Excel
Then Ctrl+H for the Search and Replace dialog
In Find What: Enter the search text
In Replace with: enter the n
|
|
Office-365/Excel/Code-vba-Makro
Excel: Convert letters to Ascii numbers around
|
26.11.2019 355
|
|
Excel: Convert letters to Ascii numbers around
Here you will find a code with which you
can convert letters into numbers in Excel
In the example, A becomes 1 , B->2,
C->3
The vba code is for copying and an example
file in the appendix
|
|
Office-365/Excel/Formulas
Excel function: find the position in a column or row
|
26.11.2019 340
|
|
Actually, the function comparison (search
text; search area,1)
But the formula always leads to a problem
in vba Excel That's why I simply rewrote the function here with
=get_First_Match(SearchText; search area)
The function, the code is below for
copy
|
|
Office-365/Excel/Formulas
Excel: Find text in a value
|
07.11.2019 359
|
|
Excel: Find text in a value
the formula finds the text position in which a text or character occurs
The difference is =SEARCH and =FIND in whether case is case sensitive
=SEARCH("a"; B4)
|
|
Office-365/Excel/Tutorials
Change Excel Adress Z1S1 R1C1 row column to A1
|
25.10.2019 431
|
|
Excel -> Toggle column header from 1 2 3 4 5 to A B C
In cell at the top left is:
Z1S1
But I need the cell address: A1
File Options
Reference type switching to
File->Options->Formulas-> Z1S1 Reference Type :
|
|
Office-365/Excel/Add--in
Excel Icons
|
12.01.2019 3082
|
|
Excel Icons
A list of
internal names for Microsoft Excel icons:
|
|
Office-365/Excel/Formulas
Excel Exponent Numbers
|
22.10.2018 803
|
|
High number
in Excel
Hochzahl is
the character ^
The key for
^ is located to the left of the 1 and is simply clicked and then a space
Example:
2 4 Giga heart is the frequency for mobile phones
= 2 4 * 10 high 9
|
|
Office-365/Excel/Code-vba-Makro
Excel File Dialog for Selecting File-Paths to Worksheet
|
09.10.2018 1019
|
|
Excel File Dialog:
The
following Excel macro code in vba creates a file dialog for an Excel file
Several
files can be selected and adopted as text
Download:
The code template is available for download under Codedocu de as Excel sample
file
or: Th
|
|
Office-365/Excel/Add--in
Addin: vba code
|
07.09.2018 1411
|
|
Excel vba
code for the add-in
Function of the code
|
|
Office-365/Excel/Add--in
Exel file with system addin as Template
|
07.09.2018 1157
|
|
This
article discusses an Excel help tool that allows you to create simple Excel
input forms
The Excel
file has green input fields, which should serve as input navigation as with the
tax office All other areas are automatically locked by the Excel add-in
|
|
Office-365/Excel/VSTO-Addin
Excel Addin: Creating a Ribbonbar Buttons for a vba Excel Addin
|
06.09.2018 1256
|
|
Excel
Addin: Creating a Ribbonbar Buttons for a vba Excel Addin
How to
create a button in Excel's Ribbonbar from the vba code in an Excel Addin
Task:
For an
existing Excel Addin, a button should be created, which is generally used in
the toolbar or Ribbonbar of Exce
|
|
Office-365/Excel/VSTO-Addin
Remove custom toolbars
|
06.09.2018 930
|
|
Remove custom toolbars
How to remove the custom toolbars in VSTO Addins?
Problem:
If I create an addin for Excel in Visual Studio under Vsto Office, then in 2016 the new Ribbon will show the area "Custom Toolbars" in front of the actual Ribbonbar areas
an old problem
|
|
Office-365/Excel/Code-vba-Makro
Excel Template: Automatically send an e-mail for a specific entry in Excel
|
05.09.2018 1474
|
|
Excel
Template: Automatically send an e-mail for a specific entry in Excel
The
following Excel document automatically sends an email when a specific value is
entered in an input area
If a value
such as Test is entered in the input column B: B, then a standard email is
|
|
Office-365/Excel/Code-vba-Makro
Excel vba: recognize an input in cells
|
05.09.2018 1220
|
|
Excel vba:
recognize an input in cells
How to
monitor the input in an excel sheet when changing a cell in a specific area
solution
You can
examine the Worksheet Change on Target Range in Excel
|
|
Office-365/Excel/Tutorials
Excel: Move cursor with arrow key
|
23.01.2018 1561
|
|
In
Microsoft Excel, when you press the arrow keys, the sheet moves and the input
cursor does not jump from cell to cell,
then
this is due to the key: scrolls on the keyboard
On:
When the roll is on, the Excel sheet shifts
Off:
When scroll
|
|
Office-365/Excel/Tutorials
Excel: Copy cells without empty lines
|
08.01.2018 1139
|
|
How to
copy a block in Microsoft MS Excel and pass only the cells with values without
empty lines and cells?
For
this you mark the area which you want to copy without empty cells
Then take Ribbonbar-> Start-> Filter
|
|
Office-365/Excel/Formulas
Excel: Text Like function
|
08.01.2018 1235
|
|
Task: A
formula that allows you to check a text for the occurrence of a search text or
term
In vba
this is the formula LIKE
Solution:
Formula
= ISNUMBER (FIND
("balance"; B3))
In the
display in E
|
|
Office-365/Excel/Formulas
Excel: Determine a substring from text with Formulas
|
08.01.2018 1155
|
|
Excel: Determine substring from text
If you
want to determine a certain text from a cell from long Excel lists, then you
use several formulas
The
best way to create the result of several steps
If you
want to calculate the number a
|
|
Office-365/Excel/Powerquery
Excel PowerQuery: Rename Columns
|
18.12.2017 1417
|
|
How to rename columns in the MS Excel Powerquery
The Powerquery in Excel contains connections and data
copies of linked databases If you want to customize columns and fields of a
linked table in Excel,
then you can do this in Excel in the PowerQuery
|
|
Office-365/Excel
Dynamically evaluate Access with Excel
|
17.11.2017 1946
|
|
This
article shows how to evaluate MS Access data dynamically in local MS Excel
files
Data
evaluations in MS Access can no longer be evaluated using an integrated Pivot,
PivotTable, PivotChart
The
evaluation via ActiveX charts is also no longer maintained
|
|
Office-365/Excel
Excel: Switching the data connection to another database
|
16.11.2017 3773
|
|
This
example shows how to change the data connection to another database at runtime
in an Excel file
In
Microsoft Excel, data from Access databases can be attached using the Data
Connection Manager
If you set
up a data connection to an MS Access database her
|
|
Office-365/Excel/Code-vba-Makro
Excel, vba: insert image into a cell of cache
|
31.10.2017 1593
|
|
Excel, vba: insert image into a cell of cache
This
command adds a bitmap image to a special cell in Microsoft Excel in Excel
Excel Sheet Paste Destination
'
|
|