Access , Excel VBA Samples and Wordpress Plugin Posts
-
Preserving Excel Comments Across versions
You may need to save excel comments from one excel model and load them into another file. This can be a time consuming task and this routine will eliminate if (Â big IF) you have a key column to store the comments on. In this case it was the crystal ID. You need an access database…
-
Auto Unzipping Files and Loading of Excel Files
We used this function for auto unzipping files , the Excel files had been sent by email and an outlook rule saved the attachments into a given folder The Unzipit function uses the Chilkat Component and Excel Automation to open and save the file into a specific folder and load the contents into an SQL…
-
SAP – Changing the format of downloaded files
The SAP system often downloads files as MHT which can be opened in excel but can be enormous. After getting a list of files for conversion I pass the file name (tmpFile) to this function to load the file listing and then save the file as Excel 2003 for easy import into and Access or…
-
Agresso Budget Loading from Excel
Actually this could be used for a variety of purposes but I wrote this for Agresso. This is designed for a sheet with the data in column d , e and f which was required by period and transposes for loading. Use the x variable to define the offset relative position, this allows you to…
-
Loading Files from a Folder
I use this function to load CSV, txt and Excel files as part of the ETL Process. An example of this function call is available within our sample app. The button “Import and Load” will first clear the existing data and then import any files which match the section name “Import Invoices” in the table…
-
Altova Mapforce
I am currently running a demo of the mapforce to review how the software works and how to implement the system. I am unclear at this time if the software will store the mapping and allow it to be called by an API to allow customisation of messages for individual customers. Ideally it will allow…
-
Nomenclature
When designing an access database it helps to have a consistent nomenclature (naming conventions) When referring to objects in code you will always know what the defined object is Tables – Start with tbl For system tables or tables you wish to automatically hide start with Usys Access System tables start with Msys Queries –…
-
Creating SEPA Files
In this Excel Model you can create an SEPA XML file using the details you enter on the SEPA Debit tab. I used Chilkat XML when working with XML, I have also used MS XML but find Chilkat better. Chilkat XML is free and you can download the program from their website. Sepa Setup To…
-
Importing Data into Access
A lot of my applications involve importing data into access from an external source, executing some transformation and applying business rules. When I first started using Access I would have written an import specification for each file, and then added queries or code to load the data and apply business rules. This approach involves a…
-
SAP BOM Upload
To load a bill of materials into SAP I needed to create a XML file with the hierarchy of the bill. To start this project in Access you will need to set a reference to ChilkatXML (free from chilkatsoft.com), or you use MS DOM Object but I prefer the coding structure provided by chilkat, and a…
