Solved

How to run macro from .xls in .xlsx file

Posted on 2013-10-28
20
5,403 Views
Last Modified: 2013-11-19
how to run macros in .xls in .xlsx file without having to convert the file back to .xls again for the macro to work ?

Now i have to manually copy the macro code in .xls in .xlsx so that new files .xlsx can work with the macro.

any faster way ?
0
Comment
Question by:ceneiqe
  • 9
  • 8
  • 2
  • +1
20 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39604967
Try saving it as .xlsm instead of .xlsx
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39605267
You could leverage the personal workbook to store the code.  This is assuming that the code doesn't have to live in the particular workbook and justs need to be available when you need it.  This wouldn't be a good solution if you are sharing the workbook as the Personal Workbook is unique to you -- so to speak.

Tom
0
 
LVL 80

Expert Comment

by:byundt
ID: 39605427
I assume that your macros are written to use the active workbook. If so, as long as the .xls file (with macros) and the .xlsx file (that needs to use the macros) are both open, then you can select the desired worksheet in the .xlsx file and run a macro from the .xls file. You will see the macros listed in the ALT + F8 macro selector like shown below. Just pick the desired macro and run it.
'My macro workbook.xls'!Macro1
'My macro workbook.xls'!Macro2
'My macro workbook.xls'!Macro3

If you store your macros in your PERSONAL.XLS workbook as suggested by ButlerTechnology, they will appear in the macro selector list the same way, but without the single quotes (because there are no embedded spaces in the filename). Because PERSONAL.XLS is automatically opened whenever Excel launches, those macros are always available to you.
PERSONAL.XLS!Macro1
PERSONAL.XLS!Macro2
PERSONAL.XLS!Macro3

PERSONAL.XLS has to be stored in a special place for it to work correctly. If you don't already have one, to create PERSONAL.XLS, the best way to find that place and create PERSONAL.XLS is to record a macro. Make sure that you choose "Personal Macro Workbook" in the "Store Macro In" dropdown field of the macro recorder dialog. The recorded macro doesn't have to do anything, and can even be deleted immediately after you create it. You can then copy the macros from the .xls file into macro modules in your newly created PERSONAL.XLS. Excel will prompt you to save the changes to PERSONAL.XLS when you close Excel.
0
 

Author Comment

by:ceneiqe
ID: 39607921
I assume that your macros are written to use the active workbook. If so, as long as the .xls file (with macros) and the .xlsx file (that needs to use the macros) are both open, then you can select the desired worksheet in the .xlsx file and run a macro from the .xls file. You will see the macros listed in the ALT + F8 macro selector like shown below. Just pick the desired macro and run it.
'My macro workbook.xls'!Macro1
'My macro workbook.xls'!Macro2
'My macro workbook.xls'!Macro3

// no it doesnt work for me, even when both books are opened.
i need to copy the macro in .xls, and create a module in .xlsx n then paste the code in before i can run the macro.


If you store your macros in your PERSONAL.XLS workbook as suggested by ButlerTechnology, they will appear in the macro selector list the same way, but without the single quotes (because there are no embedded spaces in the filename). Because PERSONAL.XLS is automatically opened whenever Excel launches, those macros are always available to you.
PERSONAL.XLS!Macro1
PERSONAL.XLS!Macro2
PERSONAL.XLS!Macro3

PERSONAL.XLS has to be stored in a special place for it to work correctly. If you don't already have one, to create PERSONAL.XLS, the best way to find that place and create PERSONAL.XLS is to record a macro. Make sure that you choose "Personal Macro Workbook" in the "Store Macro In" dropdown field of the macro recorder dialog. The recorded macro doesn't have to do anything, and can even be deleted immediately after you create it. You can then copy the macros from the .xls file into macro modules in your newly created PERSONAL.XLS. Excel will prompt you to save the changes to PERSONAL.XLS when you close Excel.

//""Personal Macro Workbook" in the "Store Macro In" dropdown field "
where can i find them ?
could you give step by step  instructions? thks
0
 
LVL 80

Accepted Solution

by:
byundt earned 50 total points
ID: 39608766
Step by step instructions for creating a PERSONAL.XLS workbook
1. Click the Record a Macro button at bottom left of the worksheet
Record macro button2. In the resulting dialog, choose to store the macro in Personal macro workbook
Store macro in PERSONAL.XLS3. Click OK
4. Do something, such as clicking on a different cell
5. Stop macro recording by clicking the same button as in step 1

// no it doesnt work for me, even when both books are opened.
Do you mean that you can't see the list of macros when you ALT + F8?
Or that you can see the list of macros and run one, but that it doesn't do anything?
0
 

Author Comment

by:ceneiqe
ID: 39616812
// no it doesnt work for me, even when both books are opened.

Do you mean that you can't see the list of macros when you ALT + F8?
Or that you can see the list of macros and run one, but that it doesn't do anything?


can't see the list of macros when i use ALT + F8
0
 
LVL 80

Expert Comment

by:byundt
ID: 39616913
When you open the .xls workbook, does it say (Protected view) at the top of the window? You won't be able to see or run the macros in a workbook that is in Protected View.
File opened in Protected ViewTo fix this problem, you'll need to change the settings for which file types your computer opens in Protected View. Go to the File...Options...Trust Center...Trust Center Settings...File Block Settings menu item. You want to have the Open and Save boxes unchecked for "Excel 97-2003 Workbooks and Templates". You'll need to close Excel and relaunch it for the change to take effect.
0
 

Author Comment

by:ceneiqe
ID: 39623591
0
 
LVL 80

Expert Comment

by:byundt
ID: 39624021
Are you now able to see the macros in the ALT + F8 Macro Selector dialog now?

If so, you can put the macro from the two Experts Exchange threads you cited in your PERSONAL macro workbook. It will then always be available to you when you are running Excel.

If you can see PERSONAL.XLSB (or PERSONAL.XLS) in the left pane of the VBA Editor, then you can put any macros you like in a module sheet in that workbook. All of them should be listed in the ALT + F8 macro selector dialog.

The macro recording suggestion made above was only necessary if you did not already have a PERSONAL macro workbook. Recording a macro as described will create a PERSONAL macro workbook for you.
0
 

Author Comment

by:ceneiqe
ID: 39629597
Yes i can see the macros now.

i have created a Personal macro.xlsb worksbook and place all the macros in separate modules.

However, i can't run one of the macros.

Error:

"Compile error:
User-defined type not defined.

OK | Help"

The following words are highlighted :
".....FSO As New FileSystemObject"
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Assisted Solution

by:ButlerTechnology
ButlerTechnology earned 50 total points
ID: 39629799
You will need to ad the Microsoft Scripting Runtime Reference.
Tom
Scritping Runtime
0
 

Author Comment

by:ceneiqe
ID: 39632473
compile error variable not defined
0
 

Author Comment

by:ceneiqe
ID: 39632474
And why does the Scripting Runtime reference not checked for all excel workbooks that are created ?

I realised i have to keep on going to the reference to check other workbooks.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39632475
Could you post the macro that produced the most recent error?
0
 
LVL 80

Expert Comment

by:byundt
ID: 39632478
You can use late binding and avoid the need to check the reference for the Scripting Runtime. Please post the macro in question.
0
 

Author Comment

by:ceneiqe
ID: 39632517
Below is the macro that has error.


Option Explicit

Sub getData()
   Dim FSO As New FileSystemObject
   Dim textFile As TextStream
   Dim textLine As String, folder As String, currentFile As String
   Dim currentRow As Long, count As Long
   Dim dateValue As Date
   Dim commentLine As Boolean

   ' Get file name from the user
   folder = getFolder
   If folder = "-1" Then Exit Sub
   
   'Set first file
   currentFile = Dir(folder & "\*.txt")
   
   Do While currentFile <> ""
   
      ' Get first empty row
      currentRow = Range("A" & Rows.count).End(xlUp).Row + 1
      
      Set textFile = FSO.OpenTextFile(currentFile)
      
      count = 0
      commentLine = False
      
      ' Loop through each line of text
      Do Until textFile.AtEndOfStream
         textLine = textFile.ReadLine
         If textLine <> "" Then
            If commentLine Then
               Range(Comment & currentRow).Value = Trim(textLine)
               currentRow = currentRow + 1
               commentLine = False
            ElseIf Left(textLine, 2) = "of" Then dateValue = CDate(Mid(textLine, 10, 11))
            ElseIf count = 1 And Left(textLine, 3) <> "---" Then
               Range(SA & currentRow).Value = Trim(Left(textLine, 3))
               Range(PACK & currentRow).Value = Trim(Mid(textLine, 4, 9))
               Range(Order & currentRow).Value = Trim(Mid(textLine, 14, 10))
               Range(Line & currentRow).Value = Trim(Mid(textLine, 25, 5))
               Range(Item & currentRow).Value = Trim(Mid(textLine, 31, 25))
               Range(COL1 & currentRow).Value = Trim(Mid(textLine, 57, 2))
               Range(COL2 & currentRow).Value = Trim(Mid(textLine, 60, 3))
               Range(SASS & currentRow).Value = Trim(Mid(textLine, 64, 11))
               Range(REQ & currentRow).Value = Trim(Mid(textLine, 75, 4)) 'NOTE : amended
               Range(DEL & currentRow).Value = Trim(Mid(textLine, 79, 5))  'NOTE: amended
               Range(DTE & currentRow).Value = dateValue
               commentLine = True
            ElseIf Left(textLine, 3) = "---" Then
               count = count + 1
               If count > 1 Then Exit Do
            End If
         End If
      Loop
      
      textFile.Close
      
      ' Get next file
      currentFile = Dir
   Loop
   
End Sub


Function getFolder() As String
   Dim fd As FileDialog
   Dim selection As Variant
   Dim fileName As String

   Set fd = Application.FileDialog(msoFileDialogFolderPicker)
   
   With fd
      .AllowMultiSelect = False
      If .Show = -1 Then
         getFolder = .SelectedItems(1)
      Else
         getFolder = "-1"
      End If
   End With

End Function

Open in new window

0
 
LVL 80

Expert Comment

by:byundt
ID: 39632541
There are quite a few variables that aren't defined in sub getData. These include:
Comment, SA, PACK, Order, Line, Item, COL1, COL2, SASS, REQ, DEL, DTE

I'm afraid that you'll need to post the entire workbook so I can figure out which variables really aren't defined. The ones mentioned above might be dimensioned as public variables and defined elsewhere, for example.

The late binding I was mentioning would look like:
   Dim FSO As Object, textFile As Object
   Set FSO = CreateObject("Scripting.FileSystemObject")

Open in new window

0
 

Author Comment

by:ceneiqe
ID: 39643508
Ok it is solved.

but why doesn't the following macro appear on the macro list when i do Alt+F8?

Function ConsecutiveCount(Letter As String, Occurence As Integer, TheRange As Range) As Integer
Dim i As Long
For Each c In TheRange
    If c.Value Like "*" & Letter & "*" Then
        i = i + 1
        If i >= Occurence Then
            ConsecutiveCount = ConsecutiveCount + 1
            i = 0
        End If
    Else
        i = 0
    End If
Next
End Function

Open in new window

0
 
LVL 80

Expert Comment

by:byundt
ID: 39643619
1.  Subs with parameters are never listed in the ALT + F8 macro list.
2.  User defined functions (such as ConsecutiveCount) will appear in the function wizard. You launch this dialog by clicking the fx icon to left of formula bar. You'll find ConsecutiveCount after selecting User defined (last item in list) in the Category dropdown.
0
 

Author Comment

by:ceneiqe
ID: 39661565
hi byundt, ok got it.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

746 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

10 Experts available now in Live!

Get 1:1 Help Now