Transmitting Record information From Access to Excel

Mordhel

New member
Local time
Today, 17:37
Joined
Nov 14, 2002
Messages
8
I am trying to set up a button on an access 2000 form which when clicked will open a specific excel 2000 file, and transfer the name and address of the current company being viewed in the access form.

I have already done something similar with Word 2000, but cannot find a similar method with excel.

I have tried using the query wizard in Excel, but it does not work, it does not let me complete the query says their are not enough variables.

If I have the access query use a static criteria I can get the excel documnet to open and put the name and address in it, but it puts it in as columns and I need to to put it in as rows.

I also need it to transfer the information when the file opens, and not have to have the operator activate anything.

In short the operator need only:
Open form on Access
Find record of Company desired
Press button on Form

Then automatically it should
Open excel file
inport/add company details to specific rows (ie address label look)

Note I do not know VBA, or excel macros for that matter.

Any help would be appreciated
 
I would either import or link the specific spreadsheet. If you link, it will be up to date with any change you make in Excel.

Then you can use the table in a query to show the matching company name.

In the query add the linked table and the table/query you now use for the form.

Drag a join to the company names (data must be the same in both) SET THE JOIN properties to return all records from the table your form is bound to..and only the records that match.


This query could then be used as the control source of the form.

On the form you use the "where" condition with the button to print the selected record. The query can also be used for the report.

If you don't understand what I mean I will clarify it for you.

Ziggy
 
I don't think I explained what I wanted correctly.

I am trying to set up a system by which I can go to a particular company record using a form in Access (which is where the company records are kept) and then be able to click on a button which will then open an Invoice (which has been created in Excel) and place the name and address from Access into the Invoice in Excel in the right locations.

At the moment I have working

:) The Access Form to see different Company details
:) The Access Query which creates a listing of a single company depending on which company is being viewed at the time the button is pressed.
:) A button which will open the invoice in Excel.

What I do not know how to do

:( Transfering the information from the Access query onto the Excel Invoice automatically when the Button in Access is pressed.
:( Have the transfered data appear in rows A4,A5,A6,A7,A8 instead of appearing in A4,B4,C4,E4,F4
:( Save the Invoice from the Template to a new worksheet.
:( Set it so that once the information is in Excel it will not change if the Query in Access is re-run to get a different company details. But still allow it to change on the Template so that I can create a new Invoice for a different company.

I have done a few experiments and can transfer the data manually, but it puts it in columns instead of rows, and will only work with a query which has a static criteria (ie "Bob Inc" rather than a formula (ie "[forms]![Details].[Company]")

I have no practically knowledge in programing and have set up the database using macros to attain the automated effects.
 
Do you have to use Excel, can't you create your invoice in Access? I can't really visualize, why you want to do it that way.

Ziggy
 
I would love to create the Invoice using access :D, unfortunately it isnt my decision :mad:. The operator has always used excel for the Invoices (along with all the other accounts), and does not want to use anything else.

The systems we use are not very integrated, and my current task is to link them into the main customer database we have, without changing the software each system uses. I have managed to sort out sending letters, emails, opening websites, viewing visit records, amongst other things, but excel is causing problems.
 
Hopefully someone with more vb experience can help you more, but I'll try and give you some ideas. I use excel macros all the time, even if you've never done it won't take long to get the hang of it

Here is a good Excel link http://216.92.17.166/board/index.php


Set your Access macro to export the selected data to a new spreadsheet.

Then using the macro recorder(Excel) open that sheet copy a cell, then open your invoice and paste(special/values). Do this for each cell, the Macro will record all the steps.

This code can be set to run automatically when the work book is opened.

So you click the button in Access which exports the data to excel sheet

Using the runapp action in the access macro, open the spread sheet. If setup correctly will open the 2 spread sheets and copy/paste the data.


the command line in the Runapp action would be:

"C:\Program Files\Microsoft Office\Office\Excel.exe" "C:\Path to your excel file\your auto open excel file.xls"

The first part in quotes is the pathe to the excel.exe, the second part is the path to the excel file you want to open.

If you view the code on the Excel macro that you want to auto open you need to change the name from

Private sub yourmacro()
to
Private Sub Workbook_Open() or auto_open()

(right click on the Excel icon top left of workbook and select "View code")

Sounds like a lot of work but the macro will do it quickly, you have to experiment with how you want the macro to work and what is best for you.

Ziggy
 
Thanks for the Help. I think I can work it out from what you've given me.
 
I just completed a db that links to excell to create 57 different charts based on user defined criteria. You are well on your way to what you want. When you send the query to excell the old spreadsheet is destroyed and a new one with the same name is created. So what you need to do is let the spreadsheet you send the query to be a "buffer" if you will. Then use a second spreadsheet with your invoice formatted the way you want it and reference the cells in the buffer spreadsheet to auto fill your query ie. =('C:\qcdatabase\[5HourTestChartBuff.xls]qry5HourTestChart'!$F$2). This will allow you to automatically populate your Invoice every time. If necessary you can save the completed Invoice as a different file name (create a copy so you dont lose your formulas.)

I think you are allready there but here is code that will export your query then open 2 spreadsheets, the buffer Minimized and the Invioce Maximized and visible so you can print.

Dim objExcel As Object
Dim strFile As String
Dim strFile2 As String
strFile = "C:\qcdatabase\chartbuffer.xls"
strFile2 = "C:\qcdatabase\scrapcharts.xls"
'Is the query populated?If so send, if not MsgBox
If DCount("*", "[qryScrapChart]") > 0 Then
DoCmd.OutputTo acOutputQuery, "qryScrapChart", acFormatXLS, strFile, False
'Open Buffer minimized
Set objExcel = GetObject(strFile)
objExcel.Application.Visible = True
objExcel.Windows(1).Visible = True
objExcel.Windows(1).WindowState = xlMinimized
'Open Chart Maximized
Set objExcel = GetObject(strFile2)
objExcel.Application.Visible = True
objExcel.Windows(1).Visible = True
objExcel.Windows(1).WindowState = xlMaximized
Else
MsgBox "There is no data to chart."
End If
ExitHere:
Exit Sub

Hope that helps and good luck
 

Users who are viewing this thread

Back
Top Bottom