acCmdImportAttachExcel (Access 2007)

[JustJim]

Hi all,

I've got a client that upgraded to Vista and Office 07 thereby breaking one of my applications that they were using.

Apologies for vagueness in the following question - I'm off-site and don't have Access 07 here.

The problem is most in evidence in a line of code that used the TransferSpreadsheet method which now seems to have trouble figgering out what goes where and whether or not I'm using a header line in the spreadsheet file.

I've come across (in this forum) the Access 07 command acCmdImportAttachExcel.

Can someone who has Access 07 pass along to me the arguments for this command and any further details you've noticed when using it.

Thanks
Jim

[puppydogbuddy]

There are no arguments. The command opens an Excel import dialog box. For .txt and .csv file types, you would use RunCommand acCmdImportAttachText. An example of the code used behind a button is shown below.

Private Sub btnImportTable_Click()
On Error Resume Next
RunCommand acCmdImportAttachExcel     
End Sub
 

See this thread for additional pertinent information:
http://bytes.com/topic/access/answer...-access-2007-a

[JustJim]

Thanks PDB,

Your link was where I found out about the command (great minds etc). Probably not going to be automated enough for my users, but I'll give it a go when I get back on site again tomorrow - another suit and tie day darnit.

Thanks again

Jim

[JustJim]

Reporting back just for completeness; I did the suit and tie thing today (on-site school visit) and used

DoCmd.RunCmd acCmdImportAttachExcel

and took the users through the dialogs and how to save the import specs. It was so much simpler for them with the old DoCmd.TransferSpreadsheet etc etc which Access 2007 seems to get confused about. It doesn't error out, it just doesn't seem to know where to put stuff!

Anyway, new, web-based version coming up so; no blood, no foul?

Jim

[puppydogbuddy]

Jim,
MS is coming out with sp2 soon which contains many bug fixes, and restores the functionality of the OutputTo (excel) command (very similar to TransferSpreadsheet). You might want to look into that.

[JustJim]

@puppydogbuddy
Yes, I'm probably going to have to bite the bullet and build a Vista/Office 07 machine, at least one to use at home (my office).

Then again, Windows 7 is going to fix everything, isn't it? Now where have I heard that before?

Jim

[rjgibson1985]

Take a look at the ImportExportSpecification object. This should let you create, save, and execute an import or export operation without opening the dialog box for the users.

http://msdn.microsoft.com/en-us/library/bb256359.aspx

[rjgibson1985]

To better clarify my suggestion, here is how I used the ImportExportSpecifications:

CurrentProject.ImportExportSpecifications.Add "Excel Import", "<?xml version='1.0' encoding='utf-8' ?>" & _
"<ImportExportSpecification Path = 'C:\Documents and Settings\rjgibso\My Documents\MCN-Applicants-Mthly.xlsx' xmlns='urn:www.microsoft.com/office/access/imexspec'>" & _
"<ImportExcel FirstRowHasNames='true' AppendToTable='Data' Range='&apos;MCN-Applicants--Mthly#qry$&apos;' >" & _
"<Columns PrimaryKey='{Auto}'>" & _
"<Column Name='Col1' FieldName='FNAME' Indexed='NO' SkipColumn='false' DataType='Text' />" & _
"<Column Name='Col2' FieldName='MNAME' Indexed='NO' SkipColumn='false' DataType='Text' />" & _
"<Column Name='Col3' FieldName='LNAME' Indexed='NO' SkipColumn='false' DataType='Text' />" & _
"<Column Name='Col4' FieldName='UID' Indexed='YESDUPLICATES' SkipColumn='false' DataType='Double' />" & _
"<Column Name='Col5' FieldName='APPL-CCYYS' Indexed='NO' SkipColumn='false' DataType='Double' />" & _
"<Column Name='Col6' FieldName='ADM-MAJOR' Indexed='NO' SkipColumn='false' DataType='Double' />" & _
"<Column Name='Col7' FieldName='MAJOR NAME' Indexed='NO' SkipColumn='false' DataType='Text' />" & _
"<Column Name='Col8' FieldName='ADM-MAJSEQ' Indexed='NO' SkipColumn='false' DataType='Double' />" & _
"<Column Name='Col9' FieldName='SEQ' Indexed='NO' SkipColumn='false' DataType='Text' />" & _
"<Column Name='Col10' FieldName='ADM-DEC' Indexed='NO' SkipColumn='false' DataType='Text' />" & _
"<Column Name='Col11' FieldName='DEC-DATE' Indexed='NO' SkipColumn='false' DataType='DateTime' />" & _
"<Column Name='Col12' FieldName='APPL-RECEIVED' Indexed='NO' SkipColumn='false' DataType='Text' />" & _
"<Column Name='Col13' FieldName='ADM-CLASS' Indexed='NO' SkipColumn='false' DataType='Text' />" & _
"<Column Name='Col14' FieldName='RACE' Indexed='NO' SkipColumn='false' DataType='Double' />" & _
"<Column Name='Col15' FieldName='CITIZENSHIP' Indexed='NO' SkipColumn='false' DataType='Double' />" & _
"</Columns>" & _
"</ImportExcel>" & _
"</ImportExportSpecification>"
CurrentProject.ImportExportSpecifications.Item(Cur rentProject.ImportExportSpecifications.Count - 1).Execute
CurrentProject.ImportExportSpecifications.Item(Cur rentProject.ImportExportSpecifications.Count - 1).Delete

If you are not sure about all of the column names and options and you want to get it right the first time, you should go through the External Data, Import option Wizard and select the "Save Import Steps" when you are finished importing it the exact way that you will always want to. Then create a large text box called txtBig and a button that calls an event procedure. In the procedure, type this code:

txtBig.value = CurrentProject.ImportExportSpecifications.Item(Cur rentProject.ImportExportSpecifications.Count - 1).XML

Just click the button and your text box will have all of the complicated XML ready for you to copy and paste into your CurrentProject.ImportExportSpecifications.Add() command.

You will also need to replace the double quotes with single quotes, but this is easy enough by using the Replace... tool in notepad.

Enjoy!