Access data to Excel cell

[solar]

Hi,

I need to send Ms Access data to Ms Excel cell. In Ms Excel, i have
Pre-printed Invoice where i have to modify the data frequently. The modify
data are more. Its difficult to me to identify the cell and change the data
in the necessary places.

So, I try to send the necessary data from Ms Access to Ms Excel by single
click as it would save more time in find out the cell and modify the data.

I heard it could be possible by "Office Automation". And, somebody suggested
to do by "DDESend" function. I tried to do so. But, I am not able to find out
the way to do that.

Can anybody guide me & give me an example to make my task easy...?. So that,
I can save my time LOT...

Your guidence & example would be appreciated GREATLY.

Thanks in advance.

ngr.

 

[Klatuu]

I would not recommend using DDE at all. To use Automation, you will need to
be competent in VBA. It involves creating an instance of Excel in VBA code
in Access and manipulating the Excel object model.

How much experience do you have using VBA?

 

[solar]

Hi,

Sorry for the late reply.

Thanks for your suggestion.

And ,the experience that i have in VBA is bit & better...

So..I tried with below codes in which i get some error. The errors are
specified with comments mark....

Here, I just tried to open the specific XL file & paste the sample data.

My Applications all are OFFICE’2000 …

My Operating system is Windows XP

The Codes are....with error message...
---------------
Private Sub Command0_Click()

On Error GoTo OpenErr

Dim objExl As Excel.Application


‘’ Error Msg No.1----9 Subscript out of range
The Codes were….
Set objExl = CreateObject("Excel.Application")

‘’ Error Msg No.2----429 ActiveX component can’t create object.
The Codes were….
Set objExl = CreateObject("Excel.Application")
Set objExl = CreateObject("Excel.Application.GetOpenFilename(C:\Documents
and Settings\ngovindaraj\Desktop\General\Hints\Ms_Access\Book1.xls")

‘’ Error Msg No.3----’13 Type mismatch
The Codes were….
Set objExl = CreateObject("Excel.Application")
Set objExl = GetObject("C:\Documents and
Settings\ngovindaraj\Desktop\General\Hints\Ms_Access\Book1.xls")


With objExl
objExl.Application.Visible = True
objExl.Application.WindowState = xlMaximized
objExl.Parent.Windows(1).Visible = True
objExl.Parent.ActiveWindow.WindowState = 2

objExl.Worksheets.Application.Range("B2").Value = Text1.Text

OpenEnd:
Set objExl = Nothing
Exit Sub

OpenErr:
Select Case Err.Number
Case 1004 ' can't write to protected cell
Resume Next
Case Else
MsgBox Err.Number & " " & Err.Description
Resume OpenEnd
End Select

End With
End Sub
------
And, anylink has to be done from Tools menu-->Reference to automate the
function...?

I coult get idea & source only this much. I would need to idenfity more and
solve the issues. Its very important for me.

Please suggest me to proceed further.

Your solution would be appreciated greately.

Thanks in advance.

ngr.

 

[Klatuu]

Here is some sample code from one of my applications. First, you will want
to put this code in a standard module:

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub


Then, here is the code for establishing the Excel object and opening an
existing spreadsheet:

Private xlApp As Object ' Reference to Microsoft Excel.
Private blnExcelWasNotRunning As Boolean ' Flag for final release.
Private xlBook As Object 'Workbook Object
Private xlSheet As Object 'Worksheet Object

'Open Excel
On Error Resume Next ' Defer error trapping.
Me.txtStatus = "Opening Spreadsheet"
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
'Open the Workbook
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
Set xlSheet = xlBook.Worksheets("Actuals_res_export")
xlSheet.Activate

The code above uses what is called "Late Binding". Dimming the object as an
Excel Application causes "Early Binding". Late binding is preferred because
it doesn't expect a specific version of Excel. With Early binding, the
object is bound to the code and expects the same version of Excel to be
installed as the machine on which the code was written.

Good Luck.