Technology Tales

Excel VBA – Read Data from a Closed Excel File or Workbook without Opening it

A practical method exists in VBA for automatically pulling data from a closed Excel file into a destination workbook without manually opening the source file. By writing a procedure within the Workbook\_Open() event of the destination file, the process triggers automatically each time that file is opened. The approach involves briefly opening the source workbook in read-only mode behind the scenes, counting its rows, iterating through the data and copying it across to the destination file, before closing the source again without saving.

Setting the Application.ScreenUpdating property to false during this process helps improve speed and prevents any visual disruption on screen. The result is a reliable, largely automated solution that reduces manual effort, minimises errors and ensures that anyone opening the destination file will always see the most current figures drawn from the source.

How to create stunning visualisations from scratch using Python

Visualisation is a core skill for data scientists, as it helps communicate analytical insights clearly and allows the human brain to identify patterns and trends far more readily than through other means. Using Python's Matplotlib and Seaborn libraries, it is possible to build a wide range of charts, from basic trend lines and scatter plots to bar charts, pie charts, donut charts and heatmaps.

Matplotlib offers extensive customisation and underpins many other Python visualisation libraries, though it requires more lines of code to produce polished results. Seaborn, which is built on top of Matplotlib, addresses this by enabling attractive, ready-to-use charts with minimal coding, making it particularly well suited to exploratory data analysis. One of its most useful features is the pair plot, which compares every attribute in a dataset against every other attribute in a single line of code.

Beyond static images, Python also supports the creation of interactive charts that can be exported as HTML files and opened in a browser, giving audiences the ability to zoom and explore data in greater detail. Data transformation using the Pandas library is an important preparatory step throughout, ensuring datasets are structured appropriately before any chart is produced.

Office VBA Reference

Visual Basic for Applications is a programming language designed to extend Office applications by enabling automation of repetitive tasks and the creation of custom functionalities. It allows users to perform actions typically done manually through the interface, such as formatting documents or interacting with users, and can be used to enhance Office applications with tailored features that meet specific business needs. This resource is aimed at experienced users seeking to leverage VBA for improving efficiency and customising Office tools through code.

SAS Usage Note 49421: A "Cannot write image" error might occur with ODS Graphics and the SAS/GRAPH® Statistical Graphics (SG) procedures

A "Cannot write image" error may occur in SAS when generating graphics using ODS Graphics or SG procedures, typically due to insufficient disk permissions, unauthorised access to a directory, or conflicts between ODS destinations. This commonly arises when attempting to write graphics to a specific ODS destination while the listing destination remains open, which can be resolved by closing the listing destination with ods listing close; or specifying a valid directory using the GPATH= option in the ods listing statement. Alternatively, wrapping procedures in ods html statements with the PATH= option to define an accessible output directory may also address the issue. The error is relevant across multiple operating systems, including Windows, Linux and various Unix variants, and applies to SAS 9.4M2 and later versions.

SAS Usage Note 39705: Troubleshooting "Sort initialisation failure" and "Sort execution failure" errors in Windows and UNIX environments

Errors encountered during SAS SORT and SQL procedures, such as "Sort initialisation failure" and "Sort execution failure," often stem from insufficient disk space, memory, or system resource limits, incorrect SAS or operating system configurations, or inadequate permissions for temporary directories. Troubleshooting involves reviewing SAS log details using commands like proc options to assess memory and utility settings, verifying user permissions for directories referenced by the WORK and UTILLOC options, checking system-imposed limits with tools like ulimit -a and ensuring sufficient space in temporary storage locations. Adjustments to system parameters, such as increasing MEMSIZE or SORTSIZE based on memory usage statistics, may be necessary. These issues can occur across a wide range of operating systems, requiring careful monitoring of resource allocation and temporary file management to resolve failures effectively.

Announcing LAMBDA: Turn Excel formulas into custom functions LAMBDA function

Microsoft Excel has introduced a new function called LAMBDA, which allows users to create their own reusable custom functions using Excel's native formula language, without the need for VBA, macros or JavaScript. Previously, creating custom functions required writing code in a separate language such as JavaScript, but LAMBDA removes that barrier, making the capability accessible to non-programmers.

A LAMBDA function accepts up to 253 parameters and a calculation as its final argument, and once named via the Name Manager under the Formulas tab, it can be called anywhere within a workbook just like any built-in Excel function. This brings significant practical benefits, such as reducing repetitive copy-and-pasting of complex formulas, making spreadsheets easier to read and maintain, and allowing errors to be corrected in a single place rather than across multiple cells.

Notably, LAMBDA also supports recursion, meaning a function can call itself, which enables looping behaviour that was previously only achievable through scripting. The function is also compatible with dynamic arrays and rich data types, expanding the range of calculations that can be performed, such as computing distances between cities using geographic coordinates. If an incorrect number of arguments is passed or the function is entered in a cell without being called, Excel will return an error, so following standard formula best practices is advisable.

How to Use Parameters in PowerShell Part I

How to Use Parameters in PowerShell Part II

PowerShell offers two approaches to passing values into scripts: unnamed arguments via the $args array, and named parameters defined using a param() block. Named parameters are generally preferred as they support type enforcement, default values, mandatory fields and validation sets that restrict inputs to predefined options.

Boolean and switch datatypes allow flag-style parameters, with switch being simpler as it requires no assigned value. Parameters can also accept arrays, enabling a single named parameter to handle multiple values at once. For pipeline-based workflows, the ValueFromPipeline attribute allows values to be passed via the pipe operator, with begin, process and end blocks controlling how each piped value is handled.

Where multiple properties need to be piped simultaneously, ValueFromPipelineByPropertyName allows a script to receive named properties from the output of other cmdlets. It is also possible to combine pipelined and directly passed parameters within the same script, provided parameter ordering is managed carefully to avoid unintended value assignment. Together, these techniques make PowerShell scripts considerably more flexible, portable and maintainable across different environments.

How to get rid of page numbers in TeX/LaTeX

Removing page numbers in LaTeX documents can be achieved through several methods depending on the document structure and class used. The nopageno package allows \pagestyle{plain} to function like \pagestyle{empty}, suppressing numbering in simple cases, though it does not affect documents using non-plain styles.

For targeted suppression, \pagestyle{empty} or \thispagestyle{empty} can be applied to specific sections or pages, though these may not override page styles set by commands like \maketitle or \chapter. Custom solutions exist for classes such as KOMA-script and memoir, which offer dedicated page style configurations for special pages. The \pagenumbering{gobble} command prevents page numbers from being printed entirely but resets the counter, making it suitable only for initial use. Additionally, the scrpage2 package provides a method to suppress numbers by redefining \pagemark, though this requires the use of \pagestyle{plain} to be effective.

Debugging SASUSER issues when you use SAS software

Users of SAS software may occasionally encounter problems with the SASUSER directory, typically caused by corrupted or outdated catalogs and item stores. Common issues include warning messages about SASUSER.TEMPLAT not being a valid item store, SAS being unable to open the SASUSER.PROFILE catalog or SASUSER.REGSTRY item store, errors when using the Output Delivery System or generating graphics output and read-only access to the SASUSER directory.

Many of these problems can be resolved by first identifying the SASUSER directory location using the PROC OPTIONS command, closing all active SAS sessions and then renaming the problematic files with an unrecognised file extension, prompting SAS to generate fresh, uncorrupted versions upon restart. Where multiple sessions are running simultaneously, only the first session will have update access, so consolidating to a single session is advisable before attempting further troubleshooting.

In multi-user or grid computing environments, the RSASUSER system option may be enforcing read-only access by policy, in which case programmes and processes should be adjusted to avoid relying on SASUSER for personal content, though in local or private environments the option can be changed to NORSASUSER within the SAS configuration file.

Fedora Packages of R Software

R can be installed on Fedora, CentOS and RHEL systems through a meta-package that pulls in several components covering core runtime files, development headers and Java support, with EPEL providing compatible packages for enterprise Linux distributions. The installation is organised across multiple directories separating binaries, libraries and documentation, and users can install additional packages either from official repositories or through the cran2copr project, which maintains automated daily synchronisations of over 20,000 CRAN packages via Fedora Copr.

Since Fedora 33, R has been linked against FlexiBLAS, a wrapper library that allows runtime switching between optimised BLAS and LAPACK backends without leaving an R session, with OpenBLAS set as the default. Development environments including RStudio Desktop and Server are available through a dedicated Copr repository, alongside graphical front-ends and Emacs integration from official repositories.

Containerised development is supported through both official Fedora Docker images and Toolbox, the latter allowing rootless environments, useful for testing new R releases or keeping development separate from the base system. Issues with packages can be reported through the R-SIG-Fedora mailing list or the relevant GitHub repositories, and the overall Fedora R stack is maintained by a number of contributors.