Access VBA loop through all Tables using DAO.TableDef

Posted on June 18, 2017June 18, 2017 by Wyman W

This Access VBA tutorial explains how to loop through all Tables using DAO.TableDef in Access. You may also want to read: Access VBA loop through Table records Access VBA loop through all Tables Let's say we use a Macro to import 100 Tables and then we want to delete them all. It is time consuming for us to delete one by one and there is a high risk of manual error of deleting the wrong one. In this case we want to write a Macro to loop through all Tables to delete all Tables that meet c...

Access VBA delete Table using DoCmd.DeleteObject Method

Posted on June 13, 2017June 18, 2017 by Wyman W

This Access VBA tutorial explains how to delete Table (delete single table, delete multiple tables) in VBA using DoCmd.DeleteObject Method. You may also want to read: Access delete table records Access VBA delete Table In Access VBA, deleting Table can be done by DoCmd.DeleteObject Method. It is an extremely simple and straight forward, the syntax is as below. In order to delete Table, use acTable in the ObjectType argument. DoCmd.DeleteObject(ObjectType, ObjectName) Name Requ...

Access VBA import txt using DoCmd.TransferText Method

Posted on June 12, 2017 by Wyman W

This Access VBA tutorial explains how to import txt using DoCmd.TransferText Method in one Table or separate Table. Microsoft Access - import TXT using DoCmd.TransferText Method In my previous post , I explained how to use DoCmd.TransferText Method to export queries to files such as txt, this tutorial explains how to import txt into Table. First prepare a TXT file such as below Then import the txt file manually. Before the final step, create a specification (which field is what data ...

Excel dependent dropdown list | dependent validation list

Posted on June 4, 2017 by Wyman W

This Excel tutorial explains how to create dependent dropdown list where dropdown list values depend on another list value. Excel dependent dropdown list / dependent validation list Suppose you have a continent list in Excel (Asia, North America, South America, etc). When an user selects a continent from a dropdown list, the user can then select the second dropdown list which contains all the countries of your selected continent, so the country list is dependent on the continent value. For ...

MS Project shift the whole schedule

Posted on June 3, 2017 by Wyman W

This MS Project tutorial explains how to shift the whole schedule using Move Project or shift part of the schedule or tasks in Microsoft Project. MS Project shift the whole schedule Before a project begins, it is common to postpone the project schedule due to many reasons, especially when a schedule requires many stakeholders to review and approve. Therefore it is highly likely that we need to adjust our schedule repeatedly before our project begins. This kind of moving schedule action is best...

Excel VBA Range PasteSpecial Method

Posted on June 2, 2017 by Wyman W

This Excel VBA tutorial explains how to use Range.PasteSpecial Method to paste special such as paste values. Excel VBA Range PasteSpecial Method In Excel worksheet, if you copy a Cell and then Paste Special, you can see a list of options. The most commonly used Paste Special is Paste Values, in order to remove all the formula. In Excel VBA, Paste Speical is done through Range.PasteSpecial Method. Syntax of Range.PasteSpecial Method Range.PasteSpecial(Paste, Operation, SkipBlanks, Tran...

MS Project delete Summary Task without deleting subtasks

Posted on May 30, 2017 by Wyman W

This MS Project tutorial explains how to delete Summary Task without deleting subtasks in Microsoft Project. Microsoft Project delete Summary Task Suppose we have created a Summary Task for Task 1 to 3. If we decide to delete the Summary Task, highlight row 1, then press keyboard Delete button. You would receive a warning message that deleting Summary Task will also delete all subtasks. If we click OK, task 1 to task 3 will be deleted. However, somet...

Excel VBA set print area

Posted on March 20, 2017 by Wyman W

This Excel VBA tutorial explains how to set print area, page width, Print Title, Orientation in Excel VBA. Excel VBA set print area To set Print Area in Excel spreadsheet, first select (highlight) Cells that we want to set as Print Area, and then navigate to ribbon Page Layout > Print Area > Set Print Area To set Print Area in Excel VBA, we have to use PageSetup.PrintArea Property. For example, in order to set Print Area A1:N21, we can write Public Sub printArea() Active...

Excel VBA freeze panes using FreezePanes property

Posted on March 12, 2017 by Wyman W

This Excel tutorial explains how to freeze panes in Excel spreadsheet and freeze panes in Excel VBA using FreezePanes property. Freeze Panes in Excel spreadsheet To explain how to freeze panes in Excel VBA, first I have to explain how to freeze panes in Excel spreadsheet. Freeze Row The most common kind of freeze panes is to freeze the first row as it contains the header of the column. So that when you scroll down the spreadsheet, you can still see the header in row 1. Highlight row 2, na...

Excel VBA unmerge columns automatically

Posted on March 4, 2017March 11, 2017 by Wyman W

This Excel VBA tutorial explains how to unmerge columns automatically and then delete blank column. You may also want to read: Excel VBA reformat merged cells to row Excel VBA separate line break data into different rows Excel VBA unmerge columns automatically For some systems, when you export a non-Excel report (e.g. PDF) to Excel format, some columns could be merged in order to fit the width of the original report layout. Even for Sharepoint, this formatting issue also happens. I...