Saving in Excel (Not what you think)

[Handcopy]

We have a macro that writes to Excel from Attachmate and then saves it. At least, that's what it's supposed to do. It only works when Excel is closed. However, we want it to save while the Excel window is open. But, it does not work, it ends up saving to the My Documents (default file location according to Excel). So, we tried changing location of the file to My Documents. As a result, it crashes saying that the file is read-only when it tries to save, so we scrapped that.

Basically, is there a way to save to an excel file while the window is opened?

P.S. We know about DisplayAlerts, ActiveWorkbook.Save (didn't help), and AppExcel.Save (Also,doesn't seem to help).

Thanks in advance.

 

[SkipVought]

Hi,

You really need to post your code.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Handcopy]

Here you go

Dim wbExcel As Object
Dim aSheet As Object
Dim queueID As String
Dim filePath as String
filePath="C:\documents and settings\assadk\desktop\Copy of tests2.xls"
Set AppExcel = CreateObject("Excel.Application")
Set wbExcel = AppExcel.WorkBooks.Open(filepath)
queueID= Sess0.Screen.GetString(17,18,9)
aSheet.Cells(1,9).value=queueID
AppExcel.DisplayAlerts = False
AppExcel.Save  
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

 

[SkipVought]

Set wbExcel = AppExcel.WorkBooks.Open(filepath)
queueID= Sess0.Screen.GetString(17,18,9)
[b]'where did you set aSheet???[/b][s]
aSheet.Cells(1,9).value=queueID[/s]
wbExcel.SOMESHEET.Cells(1,9).value=queueID
AppExcel.DisplayAlerts = False
[b]'the SAVE method has a WORKBOOK OBJECT as the reference[/b][s]
AppExcel.Save [/s] 
wbExcel.Save

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Handcopy]

Sorry, I left out the line declaring aSheet. Here it is:

Set aSheet = wbExcel.Sheets(Sheet1)

But, the program did not act any different. Anything else?

 

[SkipVought]

please post your code again.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[Handcopy]

Ok, the code was re-written specifically tp post here. Since the same error is still apparent, I don't think there's a difference.

Sub Main
    Dim appExcel As Object
    Dim wbExcel As Object
    Dim aSheet As Object
    filePath="C:\documents and settings\assadk\desktop\Copy of tests2.xls"
    Set AppExcel = CreateObject("Excel.Application")
    Set wbExcel = AppExcel.WorkBooks.Open(filepath)
    Set aSheet = wbExcel.Sheets("2.11.08")
    aSheet.cells(1, 1).value = "yo"
    AppExcel.DisplayAlerts = false
    wbExcel.Save
    AppExcel.Quit
End Sub

 

[IknowMe]

If the workbook is already open:
Set wbExcel = AppExcel.WorkBooks.Open(filepath)
opens a read only copy of the workbook.

This is probably the cause of your frustration. Try:
Set wbExcel = AppExcel.WorkBooks("Copy of tests2")




[small]Sometimes you gotta leave your zone of safety. You have to manufacture Inspirado. You gotta get out of the apartment. You've got to run with the wolves. You've got to dive into the ocean and fight with the sharks. Or just treat yourself to a delicious hot fudge sundae........ with nuts. - Jack Black[/small]

 

[JeaShe]

I always perform the SaveAs when I want to determine the location. I turn off the alerts, but it's the best way I find to be specific in the save location.