Access to Excel and back again

[RexesOperator]

I am using Access/Excel 2003

This may become an ongoing discussion. I'm not sure, but I might have asked this before. If I have, my apologies, but I am now at the stage where I need to begin coding this.

I have been able to export Access queries to Excel with no problem. I can also call an Excel spreadsheet from within Access. Again no problem.

What I would like to be able to do is dump the data from an Access form into the Excel spread sheet when the spreadsheet is opened (ie enter the data on the form, then hit a button that opens the spreadsheet with the data loaded). Then I need to take the results of the spreadsheet calculations and dump them back into the Access table that the form is bound to. This second button needs to be on the form as well. It would be called something like "Import Calculation Results".

I realize the immediate question is why not use Access for all the calculations. This spreadsheet is fairly complex and would be too cumbersome and time consuming to convert.

I also realize that calculated data should not be stored, but the data from the spreadsheet is not all calculated data.

 

[odin1701]

Well honestly you should do the calculations in Access because coding Access to control Excel in this manner may end up being just as cumbersome as converting the worksheet.

 

[RexesOperator]

The spreadsheet was actually converted to vba from an old Framework program.

My job is to integrate the Access program I've built and this spreadsheet.

I don't need to reinvent the wheel at this point. I just want to use Access as a single point of entry for both the database and the spreadsheet.

I forgot to add the file I want to open is a template.

 

[RexesOperator]

Perhaps a picture is a better explanation. Is it possible to have access load information from a specific Excel cell in a textbox by textbox occurrence?

If that can be done, then I only have to get the info from Excel, and can eliminate the step of loading some data up to EXcel from Access.

 

[Mike375]

I don't know how to do as all my stuff is Access to Word Bookmarks but I imagine it would be similar.

For example DDESend is very close for Access to a Word Bookmark or an Exel cell

=DDESend("Excel","c:\Letters\ModePayment.xls","R2C3",[MBTI])

=DDESend("WinWord","c:\Letters\0AMPMike.doc","a4",[TRAction])

In the Excel example data from the Access field MBTI is sent to Row 2 on Column 3. For the Word example the Access data from TRAction is being sent to the Word bookmark a4. In both case the formulas are in unbound text boxes on Access form and run when the form opens. They then open the Word or Excel document.

However, is not a good way to do things but it illustrates the similarity between Access to Word and Access to Excel.

Code for Access to Wordbookmarks looks like this, which might give you some ideas

Dim WordObj As Word.Application
Dim WordDoc As Word.Document
Dim WordRange As Word.Range
Set WordObj = CreateObject("Word.Application")
Set WordDoc = WordObj.Documents.Open _
("C:\Letters\0AIAMike.doc")
WordObj.Visible = True



Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="a2")

WordRange.InsertBefore [Forms]![PrintAndClose]![FirstLine]

Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="a3")

WordRange.InsertBefore [Forms]![PrintAndClose]![ClientAddress]

Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="a4")

WordRange.InsertBefore [Forms]![PrintAndClose]![ThirdLine]

Set WordRange = WordDoc.GoTo(What:=wdGoToBookmark, Name:="a5")

 

[RexesOperator]

Thanks for looking. That looks close to what I am thinking of doing.

I think I can actually eliminate the Access to Excel part. Or at least do that part last.

It would be a big help if I can get Excel into Access . I have imported spreadsheets, but I need to be able to transfer individual excel cell data into an Access text box.

The problem is the text box needs to be bound to the underlying table so I can use the Excel data to populate the record.

 

[Mike375]

You could probably use DDESend and it would be the reverse of sending the Access field entry to nominated cell.

Access field inserter into Excel cell is =DDESend("Excel","c:\Letters\ModePayment.xls","R2C3",[MBTI])

It might be =DDESend("Access", the Access db location, name------and something in Access that replaces R2C2)

 

[RexesOperator]

I will follow that up. For anyone else looking at this, I found this on the web:

http://msdn.microsoft.com/en-us/library/aa159040(office.10).aspx

 

[Mike375]

Here is a small thread on another site with just what you are talking about, but not at "full resolve" stage.

http://www.xtremevbtalk.com/showthread.php?t=296580

I will have a look around for my old Access 95 books as I also have a big Excel 95 book and DDE was a biggger thing so they might have something for Excel to Access.

 

[RexesOperator]

At least now I have a starting point. I may be back later with more questions ...

 

[Uncle Gizmo]

I posted some information in the FAQ page of this website: link here.

 

[RexesOperator]

Now that I've had a quick glance a few sites I realize I may have to do some rethinking on the Excel template. My original idea was to import the data in individual excel cells into textboxes on an Access form. I may have to do some kludging to make it appear transparent to the user (extra points for anyone who knows what kludiging means).

Once I get something going I will come back here to see if I'm headed in the right direction. But I may have to put the whole idea on hold for a week or two. My darn job keeps interferring in my Access fun.

Thanks for your help - as always.

 

[RexesOperator]

After doing some research on this it looks like DDESend is what I am after. However it is not recognized by Access 2003 (the edition I'm using), nor can I find a reference for it in Microsoft's reference. Do you know of an equivalent command in Access 2002/2003?

 

[DJkarl]

After doing some research on this it looks like DDESend is what I am after. However it is not recognized by Access 2003 (the edition I'm using), nor can I find a reference for it in Microsoft's reference. Do you know of an equivalent command in Access 2002/2003?

I think you would need to use DDEExecute and DDEInitiate.

Honestly though I've never used either one, when I've needed to push/pull data to/from Excel I use automation.

Create an Excel application object then you can do whatever you need to in a more managed fashion than DDE statements.

Short Example

Dim exapp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
dim mValue as String
 
Set exapp = New Excel.Application
Set wb = exapp.Workbooks.Open("C:\YourFileHere.xls")
Set ws = wb.Worksheets("Sheet1")
mValue = ws.Range("F30").Value

 

[Mike375]

After doing some research on this it looks like DDESend is what I am after. However it is not recognized by Access 2003 (the edition I'm using), nor can I find a reference for it in Microsoft's reference. Do you know of an equivalent command in Access 2002/2003?

It works in 2003 (at least mine and I have 5 copies) but that is for Access to Word and Access to Excel.

I had a look for my old 95 books and the Access 95 ones are here but could not find the Excel book, but it is somewhere. As expected the Access books were all Access to Word and Access to Excel.

On Excel Help you will get stuff come up if just enter DDE

The a big Excel forum with a name like Excel Man or similar, I don't have a link but it is the opposite to these type of forums in that Access is the small part and Excel is the main part.

 

[Mike375]

PS,

This what I get as the top of the list from entering DDE from Excel 2003 Help. Looking at the paste in a diagram looks like it is not included

Create a link using Dynamic Data Exchange (DDE)
Show All
Hide All

Security Dynamic data exchange (DDE) is an older technology that is not secure. If possible, use a more secure alternative to DDE, such as object linking and embedding (OLE).


The program that contains the data you want to link to must support Dynamic Data Exchange (DDE) (Dynamic Data Exchange (DDE): An established protocol for exchanging data between Microsoft Windows-based programs.). To determine whether your program supports DDE or to learn the DDE name and topic information for the program, contact the software vendor of the program. For more information about DDE, see the Microsoft Office 2003 Editions Resource Kit.

How to obtain the Office 2003 Editions Resource Kit

The Office 2003 Editions Resource Kit is the definitive guide to installing, configuring, and supporting Microsoft Office in your organization. Designed for system administrators, consultants, and power users, this guide offers complete coverage whether you're running Microsoft Office on Windows or the Macintosh.

You can obtain the Office 2003 Editions Resource Kit wherever computer books are sold, or order direct from Microsoft Press, or online at the Office 2003 Editions Resource Kit Web site.

To locate your nearest source for Microsoft Press products worldwide, visit the Microsoft Press Web site.

Select the cell you want to link from.
Enter a formula using the following syntax: application name; document name or topic; and cell range, value, field, or data that's referred to.
The following example shows the parts of a remote reference formula and the required separator characters:



A pipe character (|) separates the program name from the document or topic name. An exclamation point (!) separates the document or topic name from the cell range, value, field, or data referred to.

Press ENTER.
Note If the name of the application, document, topic, or data item contains spaces or characters such as colons ) or minus signs (–) that have special uses in a formula, or if a name resembles a cell reference, enclose the name in single quotation marks.


See Also

 

[Mike375]

This worked. I just tried it.

=DDE("Excel","C:\Letters\Exp.xls","R3C3")

Enter the above in an unbound textbox on Access form. When you open the form the message box opens asking if wnat to link etc. Click Yes and then the Access form opens and the Excel cell Value is there.

So you would need code or a macro the set the value of bound fields with the vlues in the unbound text boxes.

 

[Mike375]

Here you go. For the attached the Excel file is located in c:\Letters

 

[RexesOperator]

So much for that idea. I checked with our IT people and they recommended not using any downloads at the moment. Our network may not support them.

I will see what else I can find on this.

 

[Mike375]

It is just a demo

It simply has a form with 3 unbound text boxes

=DDE("Excel","C:\Letters\Exp.xls","R3C3")

=DDE("Excel","C:\Letters\Exp.xls","R11C10")

=DDE("Excel","C:\Letters\Exp.xls","R15C10")

The third one for R15C10 was to see if it would insert a calculated cell from Excell which it did.

The attached .xls file is just has 123 in R3C3 and 456 in R11C10 and =(C3*J11) in another cell, the latter being for R15C10