Excel VBA - choosing another Excel application

[gerok]

How can I in a macro run in one Excel application select an workboo
running in another Excel appl. and save this workbook?

Thanks in advance

Georg Rokn

 

[Frank Kabel]

Hi Georg
try the following: Opens a different workbooks, put some infos in the
first sheet, saves the wbk and closes it

Option Explicit

Private Sub open_save_other_wbk(Cancel As Boolean)
Dim log_wbk As Workbook
Dim log_wks As Worksheet
Dim last_log_row As Long
Dim path As String
Dim log_filename As String
Dim source_wbk As Workbook
Dim source_wks As Worksheet


'Initialization
Application.ScreenUpdating = False
path = "D:\Temp\"
log_filename = "log.xls"
Set source_wbk = ActiveWorkbook


'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
If log_wbk Is Nothing Then
Workbooks.Open filename:=path & log_filename
Set log_wbk = Workbooks(log_filename)
End If
Set log_wks = log_wbk.Worksheets("sheet1") 'change this

'get last used row
last_log_row = log_wks.Cells(Rows.Count, "A").End(xlUp).row

'log data - change to your needs
With log_wks
.Cells(last_log_row + 1, 1).Value = Application.UserName
.Cells(last_log_row + 1, 2).Value = _
Format(Now, "MM/DD/YYYY hh:mm:ss")
End With


' save the changes
Application.DisplayAlerts = True
log_wbk.Save
log_wbk.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub