FTP

These code extracts show you how to send and receive files using FTP from within VBA.

You will need to amend the code for your own account name, password, directories and file names.

The code creates three files.

• First is a text file (.txt) that contains the commands processed by FTP,
• Second is a batch file (.bat) that runs the FTP command
• Third is a marker file (.out) that is created by the batch when FTP has finished processing. When this is created, our VBA code detects it and knows it can then carry on with its tidying up routine

There are many reasons to want to loop through ranges of cells in an Excel worksheet. The most common seem to be:

• Changing cell color based on a condition;
• Creating sets of data;
• Generating reports.

Programmers will be aware that loops enable us to tackle the movement through an array of objects in a variety of ways. We can use a counted loop to iterate a set number of times, or an uncounted loop which will only stop once a certain condition is met.

In this article, we will look at how to loop through a collection of cells using both counted and uncounted loops. The term range is used here to identify an object containing cells from a worksheet.

Often, we Excel users refer to ranges that need to move or expand in future versions of our reports. For example:

• Each month, we must report the current month, the year to date, and, perhaps, the twelve most-recent months of financial data. Next month, we must point our existing report to new data.
  
  
• We might use the TREND function to forecast weekly sales. Next week, we must point the function at one more week of data.
  
  
• We occasionally need an easy way to point one chart at different areas of a spreadsheet database.

Dynamic range names can meet all these requirements.