Replies

Which version are you usuig?

Sorry, should have mentioned – 2007. Andy.

Let’s say the list is in cell A1. Use the following code.

Dim arr As Variant
arr = Split(Range(“A1”), “,”)

Note: by default, the array index will be zero-based, i.e. the first element is arr(0).
The index of the last element is UBound(arr).

That’s great, thanks. I was trying to look at each character in turn to find the commas, but
it was getting a bit messy. Andy.

Select Pivot Table | AutoCalc | Average, or click the AutoCalc button on the Pivot Table toolbar and select Average.

Thank you. I note that I also have to click on a salary otherwise AutoCalc is greyed out.
Andy.

No, you have to select records from a table or stored query. You could create a temporary table.

That’s a pain.. but thank you very much again. Andy.

You could use DoCmd.TransferSpreadsheet in Access to import from an Excel workbook, or DoCmd.TransferText to import directly from a .csv file.
If you want, you can use Automation to do this from Excel.

Alternatively, you could use ADO – it can run SQL on an Excel worksheet or .csv file.

Thank you very much. Andy.

I had assumed from your description that you had already imported the CSV file into an Access table named tblImport, and wanted to transfer its records to a new or existing table.

No, sorry. The csv data is currently in an Excel worksheet. I now need, from Excel, to either create a new
Access table or append to an existing one. Andy.

You could run an SQL statement:

Dim strSQL As String
Dim strNewTable As String
Dim strCSVTable As String
strNewTable = “tblNew”
strCSVTable = “tblImport”
strSQL = “SELECT * INTO [” & strNewTable & “] FROM [” & strCSVTable * “]”
CurrentDb.Execute strSQL, dbFailOnError

To append to an existing table, use

strSQL = “INSERT INTO [” & strNewTable & “] SELECT * FROM [” & strCSVTable * “]”

You can specify columns instead of *.

Thank you. Is tblImport an Excel range name? Andy.

In Design View of the Macro, go to the Design ribbon and in the Show/Hide Ribbon and click on “Show All Actions”.

Thank you! That seems an odd design change though. Perhaps the actions that are then shown
are considered to be “less safe”? Otherwise it seems pointless. Andy.

It should still be there: Access 2007 Developer Reference > TransferSpreadsheet Macro Action.
But this article states “Note This action will not be allowed if the database is not trusted.” So make sure that your database is trusted.

Thank you. Although I’ve trusted my folder location and closed re-opened Access, the
TransferSpreadsheet action doesn’t appear for a new macro.
Do you think this means it’s only available now using VBA and DoCmd? Andy.

Try code like this:

Code:

Dim lngSection As Long
lngSection = Selection.Information(Type:=wdActiveEndSectionNumber)
Selection.InsertBreak Type:=wdSectionBreakNextPage
With ActiveDocument.Sections(lngSection + 1).Headers(wdHeaderFooterPrimary)
  .LinkToPrevious = False
  .Shapes(lngSection + 1).Delete
  .Range.Text = "My new header text"
End With

Note: working with shapes in headers and footers is very tricky because all shapes in all headers and footers form a single collection.

Thank you very much. I shall explore this. Andy.

.. should have mentioned this is for Word 2003,
and that the final result is that the headers for section 1 and 2 are in the wrong order.
Andy.

Thank you – exactly what I needed to know. Andy.

Thank you.
So if, for example, I wanted to change the default font for everyone I would need to
distribute a new copy of Normal.dot and get people to replace their own Normal.dot?
Andy.

Your first version works fine for me in 2003! Which SPs do you have installed? (I’m only on SP2 here)

I’m using a version without Service Packs. Perhaps I should use the Advanced Filter
version – they always seem more robust that using AutoFilter. Andy.

No it shouldn’t. Can you post the workbook (removing any confidential info)?

Attached.
I’ve got it to work using Advanced Filter, but would prefer that the first version worked.
Thanks, Andy.