473,890 Members | 1,405 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Saving excel with VB

18 New Member
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.

Expand|Select|Wrap|Line Numbers
  1. Set objExcel = CreateObject("Excel.Application")
  2.     objExcel.Visible = True
  3. Set objWorkbook = objExcel.Workbooks.Open("C:\Users\USER\Desktop\Sample.xlsm")
  4.     objWorkbook.Save
  5.     objExcel.Quit
  6.  
Thanks.
Jul 5 '11
12 4580
irGed
18 New Member
Hi,

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

Expand|Select|Wrap|Line Numbers
  1. Set objExcel1 = CreateObject("Excel.Application")
  2. Set objWorkbook1 = objExcel1.Workbooks.Open("C:\Validation.xlsm")
  3. Set objExcel2 = CreateObject("Excel.Application")
  4. Set objWorkbook2 = objExcel2.Workbooks.Open("C:\Original.xlsm")
  5.     objExcel1.Application.Run ("ValidateExcel")
  6.     objWorkbook2.Save
  7.     objExcel1.Quit
  8.     objExcel2.Quit
  9. Set    objExcel1 = Nothing
  10. Set    objExcel2 = Nothing
  11.  
  12.  
**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.


Expand|Select|Wrap|Line Numbers
  1.  Set objExcel1 = CreateObject("Excel.Application")
  2. Set objWorkbook1 = objExcel1.Workbooks.Open("C:\Validation.xlsm")
  3. Set objExcel2 = CreateObject("Excel.Application")
  4. Set objWorkbook2 = objExcel2.Workbooks.Open("C:\Original.xlsm")
  5.     objExcel2.Application.Run ("ValidateExcel")
  6. On Error Resume Next
  7.     objWorkbook1.Save
  8.     objWorkbook2.Save
  9.     objExcel1.Quit
  10.     objExcel2.Quit
  11. Set    objExcel1 = Nothing
  12. Set    objExcel2 = Nothing
  13.  
**Macro failed to execute because there's no ValidateExcel.

Expand|Select|Wrap|Line Numbers
  1. Set objExcel1 = CreateObject("Excel.Application")
  2. Set objWorkbook1 = objExcel1.Workbooks.Open("C:\Validation.xlsm")
  3. Set objExcel2 = CreateObject("Excel.Application")
  4. Set objWorkbook2 = objExcel2.Workbooks.Open("C:\Original.xlsm")
  5.     objExcel2.Application.Run ("C:\Validation.xlsm!ValidateExcel")
  6. On Error Resume Next
  7.     objWorkbook1.Save
  8.     objWorkbook2.Save
  9.     objExcel1.Quit
  10.     objExcel2.Quit
  11. Set    objExcel1 = Nothing
  12. Set    objExcel2 = Nothing
  13.  
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.
Jul 15 '11 #11
irGed
18 New Member
Hi,


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

For future reference, this is the code:

Expand|Select|Wrap|Line Numbers
  1. Set objExcel1 = CreateObject("Excel.Application")
  2. Set objWorkbook1 = objExcel1.Workbooks.Open("C:\Validation.xlsm")
  3. Set objWorkbook2 = objExcel1.Workbooks.Open("C:\Original.xlsm")
  4.     objExcel1.Run ("Validation.xlsm!ValidateExcel")
  5.     objWorkbook1.Save
  6.     objWorkbook2.Save
  7.     objExcel1.Quit
  8. Set objExcel1 = Nothing
  9.  

Thanks.
Jul 16 '11 #12
Guido Geurs
767 Recognized Expert Contributor
For me (XP SP3 and Office 2003) it's working with:
Expand|Select|Wrap|Line Numbers
  1. Set objExcel1 = CreateObject("Excel.Application") 
  2. Set objWorkbook1 = objExcel1.Workbooks.Open("D:\EXCEL Saving excel with VB\Validation.xls") 
  3. Set objWorkbook2 = objExcel1.Workbooks.Open("D:\EXCEL Saving excel with VB\Original.xls") 
  4. rem    objExcel1.Visible=True '§ can be deleted or set REM
  5.     objWorkbook1.Activate
  6.         objExcel1.Application.Run ("Original.xls!ValidateExcel") 
  7.         objWorkbook1.Save 
  8.         objWorkbook2.Saved=True '§ no need to save => no msgbox for SaveAs
  9.         objExcel1.Quit
  10.  
Jul 16 '11 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

3
12382
by: Vikram | last post by:
Hi, Given below is my task. An user can open the xls file from my website (loaded on the top frame). After filling the Excel, he can click a send button at the bottom frame. By clicking the button i have to save the file in a shared directory on the web server by a unique name. Is it possible ?
1
2652
by: Arjun Diwan | last post by:
hi! Could anyone please help me out as to how we can save the excel workbook in the database and read it back anytime we want. I was able to convert the text files and image files into binary format and save them to the DB and finally able to retrive them back in the same format. But was unable to do same for the excel object. Any kind of help will be greatly appreciated.
0
6401
by: dd | last post by:
I am kind of puzzled with this problem. I am trying to open an xls file from C# and then save it as text or comma delimited. Here is the code: Excel.ApplicationClass oExcelApp = new Excel.ApplicationClass(); Object oMissing = System.Reflection.Missing.Value; Excel.Workbook oWB = oExcelApp.Workbooks.Open(sFileName, oMissing, oMissing,oMissing, oMissing,oMissing, oMissing, oMissing, oMissing,oMissing,oMissing, oMissing, oMissing); object...
2
1988
by: Amy B | last post by:
Hi, I've been trying to figure out how to fix this for several days but nothing I have done so far seems to work. I have posted this message in a Google Excel Programming group, but did not receive any answers. I have a web page page that is in a frame with top and side nav bars. When I click on my "Export to Excel" button, I have code that pops up a "Loading, Please Wait" page while the Excel is being created. When the file is...
1
2046
by: Mark | last post by:
Hello - I'm opening/saving an Excel workbook from ASP.Net. When I try to save the workbook I get the error: System.Runtime.InteropServices.COMException: The remote procedure call failed. Code: Dim oExcel As New Excel.Application() Dim oBooks As Excel.Workbooks Dim oBook As Excel.Workbook
0
1180
by: Kewal | last post by:
Hi All, I have a requirement of saving Excel files as HTML using C#. I have managed to write code for saving as HTLM file. however this seems to be not working in the once perticular case . For e.g I hav the following values are in column say A1 & A2. A1A2 D B
2
3837
by: DPK | last post by:
Hello friends!!! I made a prog. to make the excel. It is running well but when it saves the excel file it open the save dialog box to save the file becasue I am using SAVE function here instead of SAVE AS. So pls can anyone tell me how to use SAVE AS function in it so that i could give File name from my Programe. If I am using "Save As" Function then I am getting Error. My be I am not giving appropriate parameters. ERROR IS :
0
2028
by: Speilman_54 | last post by:
Hi, I'm converting an excel Macro into visual basic 2005 express, as I don't have a copy of VB 6 and trying to make and executable from it, I know this version doesn't have the save file as .exe, but I have a 3rd party software that will convert vb projects into .exe files. My issue is in the the saving of the excel file. The macro bassicly imports a tab delimeted file and manipulates the file and then saves it, as a template to work...
1
3227
by: johndoyle | last post by:
I've seen a few posts on other sites discussing how to save an excel file, word file, or other office doc as a pdf document. In general, seems the solutions have been pretty complicated or involved trying unsuccessfully to use the undocumented AdobePDFMakerForOffice class. One quick fix that could save time for others, and is not too difficult, is to use the Execute() method of the Excel.Application.CommandBars class. It's definitely a...
3
2615
by: RobT | last post by:
Ok, here's what I've got. I have a form that exports to an Excel worksheet (sheet1) and it works great, no issues there or anything. It's not a template, just a regular .xlsx spreadsheet, but I use it as my template. Now, the goal is for the end user to be able to fill out the form, and save it as todays date. How does it get todays date? In the Excel sheet, N2 is todays date, and it gets it from the form. Then save it in a specific...
0
9826
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10830
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10925
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
8018
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5855
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6061
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4682
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4276
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3283
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.