[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

Using VBA to export to Excel from ADP View (Access 2000)

Hi folks,

I have an ADP which connects to a SQL Server database. My main objective is to use the info in a view to power a mail merge in MS Word (2000), but I've been unsuccessful in a direct connection from Word to the data source.

Short of that, I'm trying to create some code so that a user can click a button and automatically export the view (called NewClient) to an Excel sheet.

How would I go about that?

Bonus points if someone can assist me in the direct connection from Word.

Thanks in advance,

Charlie T.
0
charlietou
Asked:
charlietou
  • 4
  • 3
1 Solution
 
rockiroadsCommented:
Have u tried DoCmd.OutputTo, u can run and dump the results in a variety of formats

e.g.

DoCmd.OutputTo acOutputQuery, "QueryName", acFormatXLS, "C:\test.xls"
there is also

acFormatRTF (Word)
acFormatSNP (Snapshot)
0
 
rockiroadsCommented:
actually, if your using a View, you may need to create a link to it, either as SQL Passthrough query or table link
0
 
charlietouAuthor Commented:
Hi,

Thanks for the quick reply.  Sorry to say it's not working, though.

How would I create a link to the view?

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
charlietouAuthor Commented:
By the way, if I choose Export from the File menu, I can export to a spreadsheet just fine.

I just can't see yet how to automate the process.
0
 
rockiroadsCommented:
Regarding your view, in SQL Server
If you do File/Get External Data/Link
then link it in via ODBC,
this then creates a kind of table link to that view (I know it works with Oracle as I have linked tables in Access which are actually Oracle Views - same principle should apply to SQL Server)

now since they are tables, u need to use

DoCmd.OutputTo acOutputTable, "tablename", etc

0
 
charlietouAuthor Commented:
Sorry, I was the one who was mistaken.

I had mistyped the directory name when Outputting the View, which was why it failed.

I discovered after I linked the table as you suggested that I was able to output the View directly without further complication.

Thanks for your help!

0
 
rockiroadsCommented:
No probs
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now