Linking to Excel files via VBA

[SusanV]

Good morning all,

I'd create linked tables (Excel spreadsheets) programmatically to extract
certain data prior to manipulation. I don't want to import this data, as
there's much MUCH more than I need - I only need to get some recordcounts
from SQL based on these spreadsheets (identical structure), then manipulate
that data. I'll be parsing over 50 directories for files with certain names,
and obviously this will be much simpler if automated. I've got all the code
done except for the automation of the linked tables, and VBA help is not
helpful, nor is google - I've got the dir part down to find the files, but
can't quite seem to work out the actual creation of the linked table.

Ideas, links or whitepapers?

TIA,

SusanV

 

[Pieter Wijnen]

See
http://support.microsoft.com/kb/257819

HTH

Pieter

 

[SusanV]

Thanks for the link - I'm having trouble grasping this though - I can get
the connection ok, but how do I then run a query against it? The query is
very simple:

SELECT DISTINCT Fieldname from Table

But how do I specify this connection? When I try the FROM [Sheet1$] it
errors that [Sheet1$] is not a valid name. Obviously I'm doing something
wrong!

<feeling slow and stupid today>
Thanks again for your help,
SusanV

"Pieter Wijnen"

 

[Pieter Wijnen]

Another way to do it is simply using DoCmd.TransferDatabase & (Temporarily)
Link the Excel Woorkbok (Sheet) as a Table

HTH

Pieter

Thanks for the link - I'm having trouble grasping this though - I can get
the connection ok, but how do I then run a query against it? The query is
very simple:

SELECT DISTINCT Fieldname from Table

But how do I specify this connection? When I try the FROM [Sheet1$] it
errors that [Sheet1$] is not a valid name. Obviously I'm doing something
wrong!

<feeling slow and stupid today>
Thanks again for your help,
SusanV

"Pieter Wijnen"

See
http://support.microsoft.com/kb/257819

HTH

Pieter

 

[SusanV]

Hi Pieter,

Looking at the VBA help for DoCmd.TransferDatabase, no mention of Excel, it
looks like I have to set up ODBC for each spreadsheet - not going there.
I'll import then just set the function to close the db and compact and
repair on close during that function, as this is a once a month reporting
thing.

From VBA Help:

Argument Description
transfertype One of the following intrinsic constants:
acExport
acImport (default)
acLink
If you leave this argument blank, the default constant (acImport) is
assumed.
Note The acLink transfer type is not supported for Microsoft
Access projects (.adp).

databasetype A string expression that's the name of one of the types
of databases you can use to import, export, or link data.
Microsoft Access (default)
Jet 2.x
Jet 3.x
dBase III
dBbase IV
dBase 5
Paradox 3.x
Paradox 4.x
Paradox 5.x
Paradox 7.x
ODBC Databases



I'm amazed that this is not a simple thing to do - there's a bazillion ways
to import, and the wizard is so incredibly simple - yes it has headings,
here's a name for the table... Frustrating!

Thanks again for your help,
SusanV


"Pieter Wijnen"

Another way to do it is simply using DoCmd.TransferDatabase &
(Temporarily) Link the Excel Woorkbok (Sheet) as a Table

HTH

Pieter

Thanks for the link - I'm having trouble grasping this though - I can get
the connection ok, but how do I then run a query against it? The query is
very simple:

SELECT DISTINCT Fieldname from Table

But how do I specify this connection? When I try the FROM [Sheet1$] it
errors that [Sheet1$] is not a valid name. Obviously I'm doing something
wrong!

<feeling slow and stupid today>
Thanks again for your help,
SusanV

"Pieter Wijnen"

See
http://support.microsoft.com/kb/257819

HTH

Pieter

Good morning all,

I'd create linked tables (Excel spreadsheets) programmatically to
extract certain data prior to manipulation. I don't want to import this
data, as there's much MUCH more than I need - I only need to get some
recordcounts from SQL based on these spreadsheets (identical
structure), then manipulate that data. I'll be parsing over 50
directories for files with certain names, and obviously this will be
much simpler if automated. I've got all the code done except for the
automation of the linked tables, and VBA help is not helpful, nor is
google - I've got the dir part down to find the files, but can't quite
seem to work out the actual creation of the linked table.

Ideas, links or whitepapers?

TIA,

SusanV

 

[Pieter Wijnen]

As you might already have understood I've never actually linked Excel into
Access before (just imported data)
So I did some research & Found:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel5, "XLTMP",
"C:\Data\MyFile.xls"

HTH

Pieter

Hi Pieter,

Looking at the VBA help for DoCmd.TransferDatabase, no mention of Excel,
it looks like I have to set up ODBC for each spreadsheet - not going
there. I'll import then just set the function to close the db and compact
and repair on close during that function, as this is a once a month
reporting thing.

From VBA Help:

Argument Description
transfertype One of the following intrinsic constants:
acExport
acImport (default)
acLink
If you leave this argument blank, the default constant (acImport)
is assumed.
Note The acLink transfer type is not supported for Microsoft
Access projects (.adp).

databasetype A string expression that's the name of one of the types
of databases you can use to import, export, or link data.
Microsoft Access (default)
Jet 2.x
Jet 3.x
dBase III
dBbase IV
dBase 5
Paradox 3.x
Paradox 4.x
Paradox 5.x
Paradox 7.x
ODBC Databases



I'm amazed that this is not a simple thing to do - there's a bazillion
ways to import, and the wizard is so incredibly simple - yes it has
headings, here's a name for the table... Frustrating!

Thanks again for your help,
SusanV


"Pieter Wijnen"

Another way to do it is simply using DoCmd.TransferDatabase &
(Temporarily) Link the Excel Woorkbok (Sheet) as a Table

HTH

Pieter

Thanks for the link - I'm having trouble grasping this though - I can
get the connection ok, but how do I then run a query against it? The
query is very simple:

SELECT DISTINCT Fieldname from Table

But how do I specify this connection? When I try the FROM [Sheet1$] it
errors that [Sheet1$] is not a valid name. Obviously I'm doing something
wrong!

<feeling slow and stupid today>
Thanks again for your help,
SusanV

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
wrote in message See
http://support.microsoft.com/kb/257819

HTH

Pieter

Good morning all,

I'd create linked tables (Excel spreadsheets) programmatically to
extract certain data prior to manipulation. I don't want to import
this data, as there's much MUCH more than I need - I only need to get
some recordcounts from SQL based on these spreadsheets (identical
structure), then manipulate that data. I'll be parsing over 50
directories for files with certain names, and obviously this will be
much simpler if automated. I've got all the code done except for the
automation of the linked tables, and VBA help is not helpful, nor is
google - I've got the dir part down to find the files, but can't quite
seem to work out the actual creation of the linked table.

Ideas, links or whitepapers?

TIA,

SusanV

 

[SusanV]

Thank you! I knew it couldn't actually be half as hard as it seemed!
You rock!

"Pieter Wijnen"

As you might already have understood I've never actually linked Excel into
Access before (just imported data)
So I did some research & Found:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel5, "XLTMP",
"C:\Data\MyFile.xls"

HTH

Pieter

Hi Pieter,

Looking at the VBA help for DoCmd.TransferDatabase, no mention of Excel,
it looks like I have to set up ODBC for each spreadsheet - not going
there. I'll import then just set the function to close the db and compact
and repair on close during that function, as this is a once a month
reporting thing.

From VBA Help:

Argument Description
transfertype One of the following intrinsic constants:
acExport
acImport (default)
acLink
If you leave this argument blank, the default constant (acImport)
is assumed.
Note The acLink transfer type is not supported for Microsoft
Access projects (.adp).

databasetype A string expression that's the name of one of the types
of databases you can use to import, export, or link data.
Microsoft Access (default)
Jet 2.x
Jet 3.x
dBase III
dBbase IV
dBase 5
Paradox 3.x
Paradox 4.x
Paradox 5.x
Paradox 7.x
ODBC Databases



I'm amazed that this is not a simple thing to do - there's a bazillion
ways to import, and the wizard is so incredibly simple - yes it has
headings, here's a name for the table... Frustrating!

Thanks again for your help,
SusanV


"Pieter Wijnen"

Another way to do it is simply using DoCmd.TransferDatabase &
(Temporarily) Link the Excel Woorkbok (Sheet) as a Table

HTH

Pieter

Thanks for the link - I'm having trouble grasping this though - I can
get the connection ok, but how do I then run a query against it? The
query is very simple:

SELECT DISTINCT Fieldname from Table

But how do I specify this connection? When I try the FROM [Sheet1$] it
errors that [Sheet1$] is not a valid name. Obviously I'm doing
something wrong!

<feeling slow and stupid today>
Thanks again for your help,
SusanV

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
wrote in message See
http://support.microsoft.com/kb/257819

HTH

Pieter

Good morning all,

I'd create linked tables (Excel spreadsheets) programmatically to
extract certain data prior to manipulation. I don't want to import
this data, as there's much MUCH more than I need - I only need to get
some recordcounts from SQL based on these spreadsheets (identical
structure), then manipulate that data. I'll be parsing over 50
directories for files with certain names, and obviously this will be
much simpler if automated. I've got all the code done except for the
automation of the linked tables, and VBA help is not helpful, nor is
google - I've got the dir part down to find the files, but can't
quite seem to work out the actual creation of the linked table.

Ideas, links or whitepapers?

TIA,

SusanV