Access/Excel question 1

[GTPSean]

Hello mates,

Hope that all is well. I would like to ask for some assistance with this, because i've been pulling my hair out fighting with this. I'm writing a little database that involves both an Access Data Entry.mdb and Excel Data Entry.xls. Access Data Entry has a process where I am able to enter some costs into an Access Table and save. Excel Data Entry has a sheet that is linked to the Access table I've updated (with costs), so upon opening the Excel Data Entry, the information updates another sheet, Export Data with values. What I'd like to do is have Access open Excel Data minimised, update in the background, and export the 2nd sheet as a CSV file, then close. Here's the code I have so far... This code kind of works, but it's not updating, and it saves the CSV the first time, but if I run it again, it errors with a "Object Variable or With Block Variable not set" Please help.

Thanks,

Sean

 

[GTPSean]

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim xlApp As Excel.Application
Dim xlWkbk As Excel.Workbook
Dim xlSht As Excel.Worksheet

Set xlApp = New Excel.Application

xlApp.Visible = True

Set xlWkbk = xlApp.Workbooks.Open("C:\Users\Desktop\Data Entry System.xls")

xlApp.Sheets("Export Data").Select

ActiveWorkbook.SaveAs FileName:= _
"C:\Users\Desktop\aspen.CSV", FileFormat:=xlCSV _
, CreateBackup:=False

xlApp.Application.ScreenUpdating = False
xlApp.Application.DisplayAlerts = False
xlApp.Application.Save
xlApp.Application.Quit

Set xlApp = Nothing
Set xlWkbk = Nothing
'Set xlSht = Nothing'

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub

 

[PHV]

if I run it again, it errors
Replace this:
ActiveWorkbook.SaveAs
with this:
xlApp.ActiveWorkbook.SaveAs

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GTPSean]

That works... Thank you SO much mate! Only thing is, I'd like to surpress the dialogue that states that the file already exists, replace? I wanna save it automatically. Any suggestions?

 

[GTPSean]

Also, forgot to mention, why do I have to open Excel Data manually to refresh the data? The data in all sheets are not automatically refreshing unless I manually open it. Please help.

Thanks,

Sean

 

[ck1999]

why not delete the file before doing a saveas

also have you tried

xlApp.Application.calculate

ck1999