Saving excel with VB

[irGed]

Hi,

I have a java code that populates data on my XLSM file, the problem is, the java code can't handle the execution of Macro, so what I'm just doing is to populate the excel file with java and then open the excel and save it manually and then the macro will be invoked.

I wanted to do it automatically, so I created a VB script that will open the file and will save it as well but the problem is, even though it saves and closes the file, it seems that it didn't invoked the Macro.

@edit:

The macro is executed the moment I hit save (Ctrl + S) or when I click the close button and save.

Any ideas on how can I do this? I'm just new with Visual Basic.

Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\USER\Desktop\Sample.xlsm")
    objWorkbook.Save
    objExcel.Quit
 

Thanks.

[Guido Geurs]

To start a macro at the opening of an excelfile:
put this in the workbook code:

Private Sub Workbook_Open()
   Call macro1
End Sub

When this is the macro:

Sub macro1()
Worksheets("sheet3").Activate
Range("D10").Activate
End Sub

The file will opening at sheet3 and range "D10" will be selected.

[irGed]

Hi,

Thanks for the reply but after reading your reply and knowing that invoking a Macro is by means of "Call" method, I have searched all the "Microsoft Excel Objects/Worksheet codes" for a word "Call" and found nothing. I don't know now if the Macro or just the VBA method is the one responsible for filling out some of the columns on my excel sheet. if it's just the VBA method, is there a method or keyword that reacts when you're pressing the "Ctrl + S" or "Save and Exit" button?


@edit: I have found a method on the code "Workbook_beforeSave()", I copied it and changed it to "Workbook_Open()". and now, everytime I open the document, it runs the Macro/Method that fills some of the columns. And now I'm trying to convert it into a Macro so that I will just call on the Macro to do the filling of some columns.

I think for my next task, I think I need to append/add a certain function(like copying the content of Workbook_beforeSave() and create Workbook_Open() with the same function on an excel file so that it will still behave the same way like my previous excel. Is this attainable through VB Script?


Thanks.

[Guido Geurs]

This will open Excel, run the macro, save the file and close the workbook.

Private Sub Workbook_Open()
   Call macro1
End Sub

Sub macro1()
Dim MIN As Integer
Dim SEC As Integer
   On Error GoTo EXITMACRO '§ press Esc to exit macro and let workbook open
'§ Enter here your code:
 
 
 
   With ThisWorkbook
      .Save
      .Close
   End With
Exit Sub
EXITMACRO:
End Sub

Because this is a macro who runs on opening the file and closes immediately after, it's not possible to see the file !
The only way to let the file open and see the results is to press Esc on opening and let the macro generate an error=> exit macro.
Attached is an example with a code to fill the time in the corresponding cell(just for testing).

[irGed]

Hi,


Thanks for the reply and all the help. And now, I have a separate question, should I start a new topic again or not?

The scenario is, I have a new excel now, but the difference with this excel file is I am not the one who generated/created this file, it comes from another source. And I want to perform a macro on it every time I ran a VB Script on it. If I just simply "hard-code" the macro on the VBA Excel, it wouldn't become a "practical solution" because this excel file is always changing, but the method of macro isn't changing.

The question is, what is the best approach for this one?

1. Should I VB Script that will create a Macro and will be stored on the Excel file so that my VB Script will simply invoke it.

2. Create a VB Script that behaves like the Macro and simply invoke that VB Script?

And how can I achieve this or where I should start first?

Thanks.

[Guido Geurs]

It's not neccesary to start a new call because it's still about the problem of starting a macro in Excel from a VBscript.

If I understand the question: it's starting a macro via a script and each time in a different Excel file.

I have to think about it and do some tests because this is also new for me.

[irGed]

Hi,

Thanks for the prompt reply. Yes, you are correct about the understanding of the question.

[Guido Geurs]

I think it's possible to start the macro with the two methods:
- write the macro in VBS and send all commands to the new sheet.
- write the macro in Excel and call the macro in the new sheet with VBS.

The last is the best way because it's essayer to test the macro when it's created in Excel.

But there is a 3th option: write an application in VB6 and call the macro in the new sheet!
This is a flexible option because it's always a new file and with VBS you have to rewrite the script each time with the new filename.
In the VB6 application you have only click the file and start the macro.

Are there different macro's ?
Because its also possible in VB6 to change to the macro you want!

I have attached an example of such an application.
It's still possible to adapt it to any needs in selections, commands, ...

[irGed]

Hi,

Thanks for all the help, although I paused for finding a solution for this problem because I got a task that has higher priority to finish. I think I would go for the second method, correct me if I'm wrong. First, I will write the macro on a separate excel ExcelB(an excel file that doesnt change no matter what), then since my main excel ExcelA file is always changing, I will invoke the macro on ExcelB that will affect ExcelA. Is that correct?

Thanks.

[Guido Geurs]

Yes, it's also the method I have used in VB6.
This is the code for VB6.
Maybe you can use some of the commands in VBS:

Private Sub Command1_Click()
Dim objexcel As Excel.Application
Dim FILEidx As Integer
   For FILEidx = 0 To File1.ListCount - 1
      If File1.Selected(FILEidx) Then
         Set objexcel = CreateObject("Excel.Application")
         With objexcel
            .Visible = True
            With .Workbooks
   On Error GoTo Error_File
               .Open App.Path & "\Saving excel with VB_MACROS_1.0.xls", , True
               .Open File1.Path & "\" & File1.List(FILEidx)
            End With
            .Run ("'" & App.Path & "\Saving excel with VB_MACROS_1.0.xls'!macro_test")
            .SaveWorkspace File1.Path & "\" & Left(File1.List(FILEidx), InStrRev(File1.List(FILEidx), ".") - 1) & "_calc_" & ".xls"
            DoEvents
            .Quit
         End With
         Exit Sub
      End If
   Next
   MsgBox "Select a file"
Exit Sub
Error_File:
   MsgBox "Error opening file"
   objexcel.Quit
End Sub

[irGed]

Hi,

I have a few codes here that I've tried but all of them failed.

Set objExcel1 = CreateObject("Excel.Application")
Set objWorkbook1 = objExcel1.Workbooks.Open("C:\Validation.xlsm")
Set objExcel2 = CreateObject("Excel.Application")
Set objWorkbook2 = objExcel2.Workbooks.Open("C:\Original.xlsm")
    objExcel1.Application.Run ("ValidateExcel")
    objWorkbook2.Save
    objExcel1.Quit
    objExcel2.Quit
Set    objExcel1 = Nothing
Set    objExcel2 = Nothing
 
 

**Invokes the ValidateExcel Macro that is stored on Validation.xlsm but doesnt affects Original.xlsm plus it gets runtime error when the macro is executed
because the macro is not meant to be executed on that workbook.

 Set objExcel1 = CreateObject("Excel.Application")
Set objWorkbook1 = objExcel1.Workbooks.Open("C:\Validation.xlsm")
Set objExcel2 = CreateObject("Excel.Application")
Set objWorkbook2 = objExcel2.Workbooks.Open("C:\Original.xlsm")
    objExcel2.Application.Run ("ValidateExcel")
On Error Resume Next
    objWorkbook1.Save
    objWorkbook2.Save
    objExcel1.Quit
    objExcel2.Quit
Set    objExcel1 = Nothing
Set    objExcel2 = Nothing
 

**Macro failed to execute because there's no ValidateExcel.

Set objExcel1 = CreateObject("Excel.Application")
Set objWorkbook1 = objExcel1.Workbooks.Open("C:\Validation.xlsm")
Set objExcel2 = CreateObject("Excel.Application")
Set objWorkbook2 = objExcel2.Workbooks.Open("C:\Original.xlsm")
    objExcel2.Application.Run ("C:\Validation.xlsm!ValidateExcel")
On Error Resume Next
    objWorkbook1.Save
    objWorkbook2.Save
    objExcel1.Quit
    objExcel2.Quit
Set    objExcel1 = Nothing
Set    objExcel2 = Nothing
 

Got an error that Validation.xlsm is locked for editting, and then the Macro failed to execute because there's no ValidateExcel.

What am I missing? I have also tried to set them into Visible, but still, it failed.

I tried the procedure manually, opening the Original.xlsm and the Validation.xlsm and invoke the ValidateExcel Macro from Original.xlsm and it performed the macro procedure.

Thanks.

[irGed]

Hi,


I have already solved the invoke macro from another excel sheet. Thanks for the help.

For future reference, this is the code:

Set objExcel1 = CreateObject("Excel.Application")
Set objWorkbook1 = objExcel1.Workbooks.Open("C:\Validation.xlsm")
Set objWorkbook2 = objExcel1.Workbooks.Open("C:\Original.xlsm")
    objExcel1.Run ("Validation.xlsm!ValidateExcel")
    objWorkbook1.Save
    objWorkbook2.Save
    objExcel1.Quit
Set objExcel1 = Nothing
 

Thanks.

[Guido Geurs]

For me (XP SP3 and Office 2003) it's working with:

Set objExcel1 = CreateObject("Excel.Application") 
Set objWorkbook1 = objExcel1.Workbooks.Open("D:\EXCEL Saving excel with VB\Validation.xls") 
Set objWorkbook2 = objExcel1.Workbooks.Open("D:\EXCEL Saving excel with VB\Original.xls") 
rem    objExcel1.Visible=True '§ can be deleted or set REM
    objWorkbook1.Activate
        objExcel1.Application.Run ("Original.xls!ValidateExcel") 
        objWorkbook1.Save 
        objWorkbook2.Saved=True '§ no need to save => no msgbox for SaveAs
        objExcel1.Quit