Solved

How to run VBA code from another MS Excel File

Posted on 2016-07-31
16
34 Views
Last Modified: 2016-09-07
How to run VBA code from another MS Excel File. I have the below code and I need to convert multi excel file from xlsx to xlsm and insert macro and run the macro after that. I managed to change and insert but I don't know to run. Please find all the files attached.

Regards,

Dallag
import-export4.xlsm
XXXX_1_1NO1.xlsx
Module1.bas
0
Comment
Question by:dallagmm
  • 6
  • 6
  • 2
  • +2
16 Comments
 
LVL 33

Expert Comment

by:Norie
ID: 41736529
Can't you not run the code on the xlsx files from the file it's in?
0
 
LVL 17

Assisted Solution

by:Roy_Cox
Roy_Cox earned 500 total points
ID: 41736532
As I keep saying the best way is to create an Excel addin. I've pointed this out to you several times in your previous questions.

Creating VBA Add-ins to Extend and Automate Microsoft Office Documents
0
 

Expert Comment

by:Enrique Noe Fukman
ID: 41736595
I use Asap utilities addin ( web ) ,
u can save different worksheets to separeted files
u can save them as XLSB , this type of file run VBA

Enrique
0
 

Author Comment

by:dallagmm
ID: 41736868
I need to run the macro from the import-export4.xlsm file on the other files on the folder.

Regards,

Dallag
0
 
LVL 33

Expert Comment

by:Norie
ID: 41736963
You do not need to 'insert' the macro in those files.
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41736985
This question has been asked several times. The OP has been told to run the code from the master workbook or create an addin in the previous posts.
0
 

Author Comment

by:dallagmm
ID: 41745665
Roy_Cox

How can I run the code from the master workbook. That is my question and tell go and search and read.

Regards,

Dallag
0
 
LVL 17

Assisted Solution

by:Roy_Cox
Roy_Cox earned 500 total points
ID: 41745964
In this question You asked how to insert a macro to another file. I posted some code and advised that you needed to read Chip Pearson's excellent article on moving with modules. I also said that it is a complex subject and you needed to read it.

I also provided code to run a macro from another workbook.

I didn't tell you to search and read, I said to read the article that I provided a link to

Programming The VBA Editor
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:dallagmm
ID: 41746681
Dear Roy_Cox,

Could you provide me with the code?

Regards,

Dallag
0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41747513
I posted the code in your previous question

How did you insert the code? To run a macro from another workbook would require something like

 Application.Run ("FileName.xlsm!MacroName")

Select all
 
Open in new window

Add your file name and macro name.
0
 

Author Comment

by:dallagmm
ID: 41747517
Dear Roy_Cox,

I did that but it is not working. did you try it in my attached files.

Regards,

Dallag
0
 
LVL 17

Assisted Solution

by:Roy_Cox
Roy_Cox earned 500 total points
ID: 41747586
You do not appear to have set a Reference to the VBA Extensibility library in IMport-Export workbook - see attached.

None of your code in the .bas file refers to a specific workbook so it will work on the workbook containing the code. I am guessing that this refers to the correct workbook

Sub Macro()
    strTitle = "Test_Date;Remarks;Chk_Sz1;US_Desander_Pres;US_Filter_Pres;US_chk_pres1;US_Desander_temp;DS_CHK_pres1;DS_CHK_Temp1;Chk_Sz2;DS_chk_pres2;" & _
        "DS_chk_Temp2;Gas_Velocity;BSW_chk;Prod_Line_Pres;Pres_Sep;Gas_Temp_Sep;Diff_pres_sep;BSW_Online_sep;Orif_plate_sz_sep;" & _
        "Oil_temp_sep;Oil_Meter_Correction_Factor_MV;Water_Meter_Correction_Factor_MV;Gas_Rate;Oil_Rate;Water_Rate;CGR;GOR;IPR;Est_Gas_Rate_New;" & _
        "Est_Gas_Rate_Old;F35;F36;F37;Gas_Gravity;CO2;H2S;Z;Visc;Oil_Gravity;Oil_Shrink;PH;CL;Oil_Gross_Rate;Water_Gross_Rate;Gas_Cum;Oil_Cum;" & _
        "Water_Cum;TCA_Pres;TCA_Temp;CCA_9_13_Pres;CCA_9_13_Temp;CCA_13_18_Pres;CCA_13_18_Temp;Static_Pres_VM;Diff_Pres_VM;Recovery_Type;Sand_Percent;" & _
        "Prop_Percent;Temp_VM;Sand;Prop;Sand_Cum;Prop_Cum;Weight;Weight_Cum;Delta_Weight_per_HR;Delta_Weight;Target_Solids;Criteria"
        
    Set ws = ActiveWorkbook.Sheets("Deliverability Test SMS 15 Min")
    Set myRange = ws.Range("B14", ws.Range("BS" & ws.Range("B" & ws.Rows.Count).End(xlUp))).Row
    Worksheets.Add before:=ws.Sheets(ws.Sheets.Count)
    Range("A1:BR1") = Split(strTitle, ";")
    myRange.Copy
    ActiveSheet.Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste Link:=True
    Columns("A:BR").EntireColumn.AutoFit
End Sub

Open in new window


If not then you need to explain more fully what you are intending the code to do
import-export4.xlsm
0
 

Author Comment

by:dallagmm
ID: 41747910
Dear Roy_Cox,

Thank you for your help.

The code (Macro) is the one saved on the Module1.bas file. The idea is to import this code to another excel file (XXXX_1_NO1.xlsm) in the C:\test folder, save the excel file as XLSM and then run the code.

The other excel file is attached here.

Regards,

Dallag
XXXX_1_1NO1.xlsx
0
 
LVL 17

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41748249
You have been told repeatedly that it unnecessary to import the macro. The simplest most efficient way is to write the code so that it can work on the other workbook from the main workbook.
0
 

Author Comment

by:dallagmm
ID: 41749145
Dear Roy_Cox,

Thank you for your advice but in my work I have to import the macro. This is the workflow we are trying to do.

Regards,

Dallag
0
 
LVL 14

Expert Comment

by:frankhelk
ID: 41787462
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Roy_Cox (https:#a41748249)
-- Roy_Cox (https:#a41747586)
-- Roy_Cox (https:#a41745964)
-- Roy_Cox (https:#a41736532)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk
Experts-Exchange Cleanup Volunteer
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
highlight duplicate entry 16 30
Consolidate xl 2010 worksheets with text 2 24
Highlighting cells in Excel 9 17
Create Excel formula on dynamic data 5 35
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now