By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,585 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

acCmdImportAttachExcel (Access 2007)

JustJim
Expert 100+
P: 407
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
May 13 '09 #1
Share this Question
Share on Google+
7 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnImportTable_Click()
  2. On Error Resume Next
  3. RunCommand acCmdImportAttachExcel     
  4. End Sub
  5.  
See this thread for additional pertinent information:
http://bytes.com/topic/access/answer...-access-2007-a
May 13 '09 #2

JustJim
Expert 100+
P: 407
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
May 13 '09 #3

JustJim
Expert 100+
P: 407
Reporting back just for completeness; I did the suit and tie thing today (on-site school visit) and used
Expand|Select|Wrap|Line Numbers
  1. 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
May 14 '09 #4

puppydogbuddy
Expert 100+
P: 1,923
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.
May 14 '09 #5

JustJim
Expert 100+
P: 407
@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
May 14 '09 #6

P: 3
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
Nov 13 '09 #7

P: 3
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!
Nov 13 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.