Jump to content
xisto Community
Kart

Running Vba Script In Excel

Recommended Posts

Hi,I need help.Im generating a excel file using jakarta poi.I have added a vba to my excel template so thatit creates a chart on file open using the data populated using the poi map.Now this works fine in a windows machine.But on a linux machine the vba does not execute. I need to release my prod in linux too and im losing time.Is there any workaround for this????Thanks in advanceKarthik

Share this post


Link to post
Share on other sites

yes i need to run a microsoft vbscript on linux machine.
like i can open a microsoft .xls file in openoffice but why not some method to run a
script in openoffice




Are you talking about Microsoft Visual Basic ? Do you mean that you want to run a microsoft thing on a Linux machine ?


Share this post


Link to post
Share on other sites

I see this is a truly old post. None the less Since the Feedbacker (whatever that is) has decided to raise this zombie of a post I thought I may as well add some insight.

Are you talking about Microsoft Visual Basic ? Do you mean that you want to run a microsoft thing on a Linux machine ?

No he was talking about Visual Basic for Applications. It is close but not quite VB. Features put in with VBA will only function in Office. VBA is not a fully compiled language like C++ or even VB. Nor is ti a scripted language like HTML or JavaScript. Instead it is compiled on load. So when a document with VBA coding is opened it is at that point that the VBA is compiled. Thus the only application that will use the VBA coding is the only one that can compile the code in question MS Office.

Access works slightly differently.

Share this post


Link to post
Share on other sites

VBA Trial Script for Excel

Running Vba Script In Excel

 

Hi There,

 

 

 

I created a excel sheet with some vb scripting for a client. I want to include a trial period in this so I can give it out to clients to test and if they like it they can buy it. Does anyone know how to do this?

 

 

 

 

 

Swannie

 

 

 

-reply by Gert Swanepoel

Share this post


Link to post
Share on other sites

connecting unix with excel

Running Vba Script In Excel

 

Hi,

Can anyone suggest me how to connect a file present in unix server to load data in excel using vba code.

 

 

-question by Sweta Kumari

Share this post


Link to post
Share on other sites

How can i set auto email notifications in excel?

Running Vba Script In Excel

 

I have data I excel currently, and I am looking for a solution to setup auto email notification to users when a certain check is conducted and it mataches the condition. Lets say when it check for each row what is the date and when a certain condition matches, it will send an email telling users to folow up with customers etc. I know VBscript might be able to help but is there any alternatives as I am poor in programming. Thank you.

 

-question by wendy chew

Share this post


Link to post
Share on other sites
VBA Excel Script for Moody´s Merton ModelRunning Vba Script In ExcelHi!I need the script in visual basic (excel) for the Moody´s Merton Model. Or maybe books or links related to this topic. 
 
Thanks-question by Jeffry

Share this post


Link to post
Share on other sites
VB Script in Excel does not compute in Pcomm 3270. Why?Running Vba Script In ExcelThere are 3 modules below. Why it does not work. It worked before.Sub mjs_Input()'***************************************************************************Dim ExcelCommand As Object 'The object that will perform Excel controlsDim SystemCommand As Object 'The object that will perform system (I.E.HUB) actionsDim ConvDDE As String 'String to be used in creating ConversationsDim Temp As Variant 'Misc FieldDim Transaction As Integer 'The number of the row in excel that the trx data is presentDim transactionrow As IntegerDim sAVEStatusbar 'The number of transaction being processed to be displayed on excel bar'Initialize connection Set SystemCommand = CreateObject("PCOMM.AutECLPS") Set ExcelCommand = Workbooks(ActiveWorkbook.Name).Worksheets("MJS Input Screen")Transaction = 3 'Transactions start from row number 2 in this spreadsheetTransactionrow = 1SAVEStatusbar = Application.DisplayStatusBar 'In order to display the transaction numberApplication.DisplayStatusBar = True'set up errorhandling On Error GoTo ErrorHandler ' Enable error-handling routine. Application.EnableCancelKey = xlErrorHandler 'Cancel key will trigger errorhandling 'Initialise the conversationTemp = UCase(Left(Trim(InputBox("Which Session is DBOS in? (Valid answers, A,B,C etc)", "Session", "A")), 1))SystemCommand.SetConnectionByName (Temp) 'As new windows of PCOMM are opened,'each new window is called a new session and each one is assigned a new, unique session'name from A-Z, the correct session needs to be pointed out for Excel to input/read data from.'In order to do this an inputbox pops-up for the user to input the session name.'Check that we are in correct screenIf SystemCommand.Gettext(2, 39, 4) <> "8001" Then MsgBox "You are Not in the Right Session!" & vbCrLf & _ "Transfer Aborted!", vbCritical + vbOKOnly, "Not in 8001!!" GoTo ErrorHandlerEnd If'loop through all records (where there is a Security Number) - and send to the system to input in th HUBWhile ExcelCommand.Range("b" & Transaction) <> 0 'Will continue as long as there is a security number in column AConvDDE = CreateTrans(ExcelCommand, Transaction, transactionrow, SystemCommand)'This will take the code to the function that is task specific (I.E. Input Security Number, Input Price, Input Bid Price'etc.)If ConvDDE = False Then GoTo ErrorHandler'The function will return "True" if the'transaction is completed successfully or "False" if there is an error, in this case this'will take us to the error-handling routineNextTrans: 'Increase countersTransaction = Transaction + 1Application.StatusBar = "Number of Securities processed :" & Transaction - 3WendMsgBox "Transfer Completed!"'When there is no entry in'column A, this message will be displayed and when "OK" is clicked transfer is stopped.GoTo The_End 'Exit Sub before error handler.ErrorHandler: 'Error-handling routine.MsgBox "Application Crashed!! Sorry!", vbCritical 'Terminate link with systemThe_End: Set SystemCommand = Nothing Application.DisplayStatusBar = sAVEStatusbar Application.DisplayStatusBar = TrueEnd Sub'***************************************************************************Function CreateTrans(ExcelCommand As Object, Transaction As Integer, transactionrow As Integer, SystemCommand) As Boolean'***************************************************************************'The purpose of this function is to read one security number from Excel and transfer it to the system'Define variablesDim acc As StringDim dc As StringDim amt As StringDim aq As StringDim ds As StringDim qq As StringDim desc As StringDim dte As StringCreateTrans = True'define addressesAcc = ExcelCommand.Range("d" & Transaction)Dc = ExcelCommand.Range("b" & Transaction)Desc = ExcelCommand.Range("j" & Transaction)Amt = Format(ExcelCommand.Range("G" & Transaction), "0.00")'aq = ExcelCommand.Range("d" & Transaction)Ds = ExcelCommand.Range("I" & Transaction)'qq = ExcelCommand.Range("e" & Transaction)Dte = Format(ExcelCommand.Range("c" & Transaction), "ddmmmyyyy")Select Case transactionrow'1Case 1SystemCommand.SendKeys acc, 5, 9SystemCommand.SendKeys dc, 6, 42SystemCommand.SendKeys amt, 6, 64SystemCommand.SendKeys "ntrf", 7, 6SystemCommand.SendKeys ds, 7, 16SystemCommand.SendKeys "28DU", 7, 35SystemCommand.SendKeys dte, 6, 49SystemCommand.SendKeys desc, 8, 6'SystemCommand.SendKeys "V", 8, 46Transactionrow = transactionrow + 1'2Case 2SystemCommand.SendKeys acc, 11, 9SystemCommand.SendKeys dc, 12, 42SystemCommand.SendKeys amt, 12, 64SystemCommand.SendKeys "ntrf", 13, 6SystemCommand.SendKeys ds, 13, 16SystemCommand.SendKeys "28DU", 13, 35SystemCommand.SendKeys dte, 12, 49SystemCommand.SendKeys desc, 14, 6'SystemCommand.SendKeys "V", 14, 46Transactionrow = transactionrow + 1'3Case 3SystemCommand.SendKeys acc, 17, 9SystemCommand.SendKeys dc, 18, 42SystemCommand.SendKeys amt, 18, 64SystemCommand.SendKeys "ntrf", 19, 6SystemCommand.SendKeys ds, 19, 16SystemCommand.SendKeys "28DU", 19, 35SystemCommand.SendKeys dte, 18, 49SystemCommand.SendKeys desc, 20, 6'SystemCommand.SendKeys "V", 20, 46SystemCommand.SendKeys "[enter]"Transactionrow = 1SystemCommand.SendKeys "[eof]"End SelectEnd FunctionSub mjs_INHIBIT()'***************************************************************************Dim ExcelCommand As Object 'The object that will perform Excel controlsDim SystemCommand As Object 'The object that will perform system (I.E.HUB) actionsDim ConvDDE As String 'String to be used in creating ConversationsDim Temp As Variant 'Misc FieldDim Transaction As Integer 'The number of the row in excel that the trx data is presentDim transactionrow As IntegerDim sAVEStatusbar 'The number of transaction being processed to be displayed on excel bar'Initialize connection Set SystemCommand = CreateObject("PCOMM.AutECLPS") Set ExcelCommand = Workbooks(ActiveWorkbook.Name).Worksheets("MJS Input Screen")Transaction = 3 'Transactions start from row number 2 in this spreadsheetTransactionrow = 1SAVEStatusbar = Application.DisplayStatusBar 'In order to display the transaction numberApplication.DisplayStatusBar = True'set up errorhandling On Error GoTo ErrorHandler ' Enable error-handling routine. Application.EnableCancelKey = xlErrorHandler 'Cancel key will trigger errorhandling 'Initialise the conversationTemp = UCase(Left(Trim(InputBox("Which Session is DBOS in? (Valid answers, A,B,C etc)", "Session", "A")), 1))SystemCommand.SetConnectionByName (Temp) 'As new windows of PCOMM are opened,'each new window is called a new session and each one is assigned a new, unique session'name from A-Z, the correct session needs to be pointed out for Excel to input/read data from.'In order to do this an inputbox pops-up for the user to input the session name.'Check that we are in correct screenIf SystemCommand.Gettext(2, 40, 4) <> "5109" Then MsgBox "You are Not in the Right Session!" & vbCrLf & _ "Transfer Aborted!", vbCritical + vbOKOnly, "Not in 8001!!" GoTo ErrorHandlerEnd If'loop through all records (where there is a Security Number) - and send to the system to input in th HUBWhile ExcelCommand.Range("d" & Transaction) <> 0 'Will continue as long as there is a security number in column AConvDDE = CreateTrans(ExcelCommand, Transaction, transactionrow, SystemCommand)'This will take the code to the function that is task specific (I.E. Input Security Number, Input Price, Input Bid Price'etc.)If ConvDDE = False Then GoTo ErrorHandler'The function will return "True" if the'transaction is completed successfully or "False" if there is an error, in this case this'will take us to the error-handling routineNextTrans: 'Increase countersTransaction = Transaction + 1Application.StatusBar = "Number of Securities processed :" & Transaction - 3WendMsgBox "Transfer Completed!"'When there is no entry in'column A, this message will be displayed and when "OK" is clicked transfer is stopped.GoTo The_End 'Exit Sub before error handler.ErrorHandler: 'Error-handling routine.MsgBox "Application Crashed!! Sorry!", vbCritical 'Terminate link with systemThe_End: Set SystemCommand = Nothing Application.DisplayStatusBar = sAVEStatusbar Application.DisplayStatusBar = TrueEnd Sub'***************************************************************************Function CreateTrans(ExcelCommand As Object, Transaction As Integer, transactionrow As Integer, SystemCommand) As Boolean'***************************************************************************'The purpose of this function is to read one security number from Excel and transfer it to the system'Define variablesDim acc As String'Dim dc As String'Dim amt As String'Dim aq As String'Dim ds As String'Dim qq As String'Dim desc As String'Dim dte As StringCreateTrans = True'define addressesAcc = ExcelCommand.Range("d" & Transaction)'dc = ExcelCommand.Range("b" & Transaction)'desc = ExcelCommand.Range("j" & Transaction)'amt = Format(ExcelCommand.Range("G" & Transaction), "0.00")'aq = ExcelCommand.Range("d" & Transaction)'ds = ExcelCommand.Range("I" & Transaction)'qq = ExcelCommand.Range("e" & Transaction)'dte = Format(ExcelCommand.Range("c" & Transaction), "ddmmmyyyy")SystemCommand.SendKeys acc, 5, 30SystemCommand.SendKeys "[enter]"SystemCommand.SendKeys "[eof]"While SystemCommand.Gettext(1, 42, 7) <> "DETAILS"WendIf SystemCommand.Gettext(6, 66, 14) = "* INHIBITED *" Then ExcelCommand.Range("d" & Transaction).Select With Selection.Interior .ColorIndex = 37 .Pattern = xlSolid End WithEnd IfSystemCommand.SendKeys "[enter]"SystemCommand.SendKeys "[eof]"While SystemCommand.Gettext(1, 41, 9) <> "PARAMETER"WendEnd FunctionSub mjs_Release()'***************************************************************************Dim ExcelCommand As Object 'The object that will perform Excel controlsDim SystemCommand As Object 'The object that will perform system (I.E.HUB) actionsDim ConvDDE As String 'String to be used in creating ConversationsDim Temp As Variant 'Misc FieldDim Transaction As Integer 'The number of the row in excel that the trx data is presentDim transactionrow As IntegerDim sAVEStatusbar 'The number of transaction being processed to be displayed on excel bar'Initialize connection Set SystemCommand = CreateObject("PCOMM.AutECLPS") Set ExcelCommand = Workbooks(ActiveWorkbook.Name).Worksheets("MJS Input Screen")Transaction = 3 'Transactions start from row number 2 in this spreadsheetTransactionrow = 1SAVEStatusbar = Application.DisplayStatusBar 'In order to display the transaction numberApplication.DisplayStatusBar = True'set up errorhandling On Error GoTo ErrorHandler ' Enable error-handling routine. Application.EnableCancelKey = xlErrorHandler 'Cancel key will trigger errorhandling 'Initialise the conversationTemp = UCase(Left(Trim(InputBox("Which Session is DBOS in? (Valid answers, A,B,C etc)", "Session", "A")), 1))SystemCommand.SetConnectionByName (Temp) 'As new windows of PCOMM are opened,'each new window is called a new session and each one is assigned a new, unique session'name from A-Z, the correct session needs to be pointed out for Excel to input/read data from.'In order to do this an inputbox pops-up for the user to input the session name.'Check that we are in correct screenIf SystemCommand.Gettext(2, 39, 4) <> "8002" Then MsgBox "You are Not in the Right Session!" & vbCrLf & _ "Transfer Aborted!", vbCritical + vbOKOnly, "Not in 8001!!" GoTo ErrorHandlerEnd If'loop through all records (where there is a Security Number) - and send to the system to input in th HUBWhile ExcelCommand.Range("b" & Transaction) <> 0 'Will continue as long as there is a security number in column AConvDDE = CreateTrans(ExcelCommand, Transaction, transactionrow, SystemCommand)'This will take the code to the function that is task specific (I.E. Input Security Number, Input Price, Input Bid Price'etc.)If ConvDDE = False Then GoTo ErrorHandler'The function will return "True" if the'transaction is completed successfully or "False" if there is an error, in this case this'will take us to the error-handling routineNextTrans: 'Increase countersTransaction = Transaction + 1Application.StatusBar = "Number of Securities processed :" & Transaction - 3WendMsgBox "Transfer Completed!"'When there is no entry in'column A, this message will be displayed and when "OK" is clicked transfer is stopped.GoTo The_End 'Exit Sub before error handler.ErrorHandler: 'Error-handling routine.MsgBox "Application Crashed!! Sorry!", vbCritical 'Terminate link with systemThe_End: Set SystemCommand = Nothing Application.DisplayStatusBar = sAVEStatusbar Application.DisplayStatusBar = TrueEnd Sub'***************************************************************************Function CreateTrans(ExcelCommand As Object, Transaction As Integer, transactionrow As Integer, SystemCommand) As Boolean'***************************************************************************'The purpose of this function is to read one security number from Excel and transfer it to the system'Define variablesDim acc As String'Dim dc As String'Dim amt As String'Dim aq As String'Dim ds As String'Dim qq As String'Dim desc As String'Dim dte As StringCreateTrans = True'define addressesAcc = ExcelCommand.Range("d" & Transaction)'dc = ExcelCommand.Range("b" & Transaction)'desc = ExcelCommand.Range("j" & Transaction)'amt = Format(ExcelCommand.Range("G" & Transaction), "0.00")'aq = ExcelCommand.Range("d" & Transaction)'ds = ExcelCommand.Range("I" & Transaction)'qq = ExcelCommand.Range("e" & Transaction)'dte = Format(ExcelCommand.Range("c" & Transaction), "ddmmmyyyy")Select Case transactionrow'1Case 1SystemCommand.SendKeys acc, 5, 9SystemCommand.SendKeys "RE", 5, 78'SystemCommand.SendKeys amt, 6, 64'SystemCommand.SendKeys "ntrf", 7, 6'SystemCommand.SendKeys ds, 7, 16'SystemCommand.SendKeys "28DU", 7, 35'SystemCommand.SendKeys dte, 6, 49'SystemCommand.SendKeys desc, 8, 6'SystemCommand.SendKeys "V", 8, 46Transactionrow = transactionrow + 1'2Case 2SystemCommand.SendKeys acc, 11, 9SystemCommand.SendKeys "RE", 11, 78'SystemCommand.SendKeys amt, 12, 64'SystemCommand.SendKeys "ntrf", 13, 6'SystemCommand.SendKeys ds, 13, 16'SystemCommand.SendKeys "28DU", 13, 35'SystemCommand.SendKeys dte, 12, 49'SystemCommand.SendKeys desc, 14, 6'SystemCommand.SendKeys "V", 14, 46Transactionrow = transactionrow + 1'3Case 3SystemCommand.SendKeys acc, 17, 9SystemCommand.SendKeys "RE", 17, 78'SystemCommand.SendKeys amt, 18, 64'SystemCommand.SendKeys "ntrf", 19, 6'SystemCommand.SendKeys ds, 19, 16'SystemCommand.SendKeys "28DU", 19, 35'SystemCommand.SendKeys dte, 18, 49'SystemCommand.SendKeys desc, 20, 6'SystemCommand.SendKeys "V", 20, 46SystemCommand.SendKeys "[enter]"Transactionrow = 1SystemCommand.SendKeys "[eof]"End SelectEnd Function-question by dboo

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.