Macro Trouble

[Samantha B.]

Hi everyone,

I'm working on what I thought would be a fairly straightforward macro that
would "insert database" from an Excel file with a standard name and path. In
other words, I want the macro to pick up the Excel file contents (certain
columns only) from the file, stored on the user's C, and drop it into the
Word doc. Most of it is working fine; I can filter the columns as needed.
But I continue to get the "Select Data Source" prompt and file explorer; my
users have to go manually pick out the Excel file from their C drives.

This isn't a showstopper for me... but it will be for some users! Any
advice? Or have I just hit a normal functional limitation?

My macro is below... thanks in advance for any suggestions!



Sub GetInterview()
'
' GetInterview Macro
' Macro recorded 10/25/2006 by UserMe'
ActiveWindow.ActivePane.SmallScroll Down:=131
Selection.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=True,
_
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=C:\filename;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase
Password="""";Jet OLEDB:Engine Type=" _
, SQLStatement:="SELECT `F1`, `F3` FROM `Sheet1$`" & "",
PasswordDocument _
:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", DataSource:="C:\filename.xls", From _
:=-1, To:=-1, IncludeFields:=False
End Sub

 

[Cindy M.]

Hi Samantha,

I'm working on what I thought would be a fairly straightforward macro that
would "insert database" from an Excel file with a standard name and path. In
other words, I want the macro to pick up the Excel file contents (certain
columns only) from the file, stored on the user's C, and drop it into the
Word doc. Most of it is working fine; I can filter the columns as needed.
But I continue to get the "Select Data Source" prompt and file explorer; my
users have to go manually pick out the Excel file from their C drives.

This isn't a showstopper for me... but it will be for some users! Any
advice? Or have I just hit a normal functional limitation?

You'll get this if you're not passing all the required connection information.
My experience with the InsertDatabase method has taught me to avoid it.
Instead, I insert a database field using the UI, then copy the field's
contents. In my macro, I use the Fields.Add method and use the copied field
content for the Text parameter.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail