Excel VBA : Data Tips

by Dermot Balson - Independent Developer
Image of Line Break

Saving data in dBASE format.

The worksheet data must be laid out in a table format. Define the table, or list, with the range name "database". Then, choose File > Save As, select the file type dBASE IV, enter a file name and click OK to export the file.

Using ODBC in VBA?

Try doing it without ODBC.XLA and calling the DLL functions directly. The pay off is speed. If you have problems getting the ODBC syntax right, record an Excel macro using xlQuery to extract data the way you want it, then adapt the code to ODBC format.

This might sound obvious, but I was asked this very question by none other than our August Editor - Mark Trescowthick.

Running Excel macros from VB or Access.

Try this :

  Dim xlobj as Object

  Set xlobj = CreateObject("Excel.Application.5")


  Set xlobj = Nothing

Passing a SQL query with more than 255 characters.

Place the SQL string in an array where each element is limited to 255 (any number of elements). Or, store the SQL in a range, and pass range object as argument.

Written by: Dermot Balson
August 1995

Image of Arrow linked to Next Article