Question Need help on MS Access to Automate with Excel

wind20mph

MS Access User Since 1996
Local time
Today, 19:30
Joined
Mar 5, 2013
Messages
50
I have encountered PC Lockdown when I tried this onClick Code.

I am trying to post a data from MSAccess field into a specific cell in Excel. The Excel File is formatted already as document. It is submitted for Attendance records. But I couldn't find the problem and fix to automate it properly.

here is the code, hope somebody who has ample experience in excel-access automation could give a hand...
Code:
Private Sub cmdRequery_Click()
On Error GoTo Err_AttSum
Dim xlApp As Excel.Application

Set xlApp = CreateObject("Excel.Application")
With xlApp
    .Visible = True
    .Workbooks.Open "\\SCDCNET\ASP\Att.xls"
    .Sheets("Att").Select
End With
    
    xlApp.Range("C,5").Value = Me.EmpNo
    xlApp.Range("D,5").Value = Me.EmpName
    xlApp.Range("D,6").Value = Me.PayrollPeriod
    xlApp.Range("E,7").Value = Me.PayrollCutOff
    xlApp.Range("A,44").Value = Me.Remarks1
    xlApp.Range("A,45").Value = Me.Remarks2
    xlApp.Range("A,46").Value = Me.Remarks3
    xlApp.Range("A,47").Value = Me.Remarks4
    
xlApp.DisplayAlerts = False
xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.DisplayAlerts = True
xlApp.ActiveWorkbook = Nothing

Exit_AttSum:
    Exit Sub
    
Err_AttSum:
    MsgBox Err.Description, vbInformation + vbOKOnly, "Error Attendance"
    Resume Exit_AttSum
End Sub
 
Remove the comma's from the range eg: xlApp.Range("C5").Value = Me.EmpNo
 
thank you pwbrown for the quick reply.

Still the screen freezes and I have to press the reset button everytime i click the command button.

Code:
Private Sub cmdRequery_Click()
On Error GoTo Err_AttSum
Dim xlApp As Excel.Application

Set xlApp = CreateObject("Excel.Application")
With xlApp
    .Visible = True
    .Workbooks.Open "\\SCDCNET\ASP\Att.xls"
    .Sheets("Att").Select
End With
    
    xlApp.Range("C5").Value = Me.EmpNo
    xlApp.Range("D5").Value = Me.EmpName
    xlApp.Range("D6").Value = Me.PayrollPeriod
    xlApp.Range("E7").Value = Me.PayrollCutOff
    xlApp.Range("A44").Value = Me.Remarks1
    xlApp.Range("A45").Value = Me.Remarks2
    xlApp.Range("A46").Value = Me.Remarks3
    xlApp.Range("A47").Value = Me.Remarks4
    
xlApp.DisplayAlerts = False
xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.DisplayAlerts = True
xlApp.ActiveWorkbook = Nothing

Exit_AttSum:
    Exit Sub
    
Err_AttSum:
    MsgBox Err.Description, vbInformation + vbOKOnly, "Att Error"
    Resume Exit_AttSum
End Sub
 
Have you tried debugging your code going through each line at a time to see where it freezes?
The main code looks perfectly fine to me, it could be your workbook.
 
It does run on MS Access97 now I include Microsoft Excel 2007 in the VBA reference's and also replaced Microsoft Jet 4.0 with microsoft Jet 3.51 but The Excel won't close after posting the data. It now fits in the specific cell. I'll post again. Thanks for enlightening me with VBA codes. I can now move on to placing subForm Data into Excel. :).
 
Try adding this after "xlApp.ActiveWorkbook = Nothing":
xlApp.Quit
Set xlApp = Nothing
 
thanks its now working fine.

Can you help me with the data in a subform to paste in excel?

its these thing:
Code:
    xlApp.Range("C5").Value = Me.EmpNo
    xlApp.Range("D5").Value = Me.EmpName
    xlApp.Range("D6").Value = Me.PayrollPeriod
    xlApp.Range("E7").Value = Me.PayrollCutOff
    [B][COLOR="DarkRed"](insert data from: b:10 to n:10  growing up to b:42 to n:42 sheet here depends on the data from the subform)[/COLOR][/B]
    xlApp.Range("A44").Value = Me.Remarks1
    xlApp.Range("A45").Value = Me.Remarks2
    xlApp.Range("A46").Value = Me.Remarks3
    xlApp.Range("A47").Value = Me.Remarks4

do you have any idea how is it done?
 
Can you explain a bit better what you are trying to do now?
Maybe someone else knows and can help.
 
Ah ok its like this one...

That current Example is on the Main Employee Form. and there is a subform for Attendance that has 13 columns in data sheet format that has records of up to 31 days depending on the data entered in it.

All I understand is a recordcount command and the rest is I cant figure out how many records to be printed and how many cells to be used. Thus how to transfer the specific data from the subform records into the specified cells B:10 to N:10 down to B:41 to N:41 if up to 31 days. thanks again.
 
Ok, I'm not sure.
You may need to run a query to select the records from the subform.
 

Users who are viewing this thread

Back
Top Bottom