Load macro

[LonnieP]

?I’m having a problem duplicating one of my old Lotus macros using Excel and VBA. It records OK when I do it manually but it’s not very versatile in that form.

The intent is to type a “filename” (without extension) into the currently active cell. Then manually move the cursor (Marque) back to the “filename”(if required), then trigger the macro using CTRL L. The macro will copy “Range1", from the named file on disk, to the currently active cell, run two additional macros (A & B) from the same file on disk, close the file on disk, then restore the screen to it’s original status. Two other cells already have range names, “Driveletter” = “C:” or any other valid logical drive, and “Directory” = “\XLshts\pool\”.

The following needs to be done by macro CTRL L:

Name the active cell “Temp1" - - (range name to be deleted at end of macro)

Open file “Driveletter”+”Directory”+contents of "Temp1"+”.XLS” - - (will always be an .XLS (single sheet) file )

Copy range “Range1" to the clipboard - - (or to cell named “Temp1" if possible to do that in one step, in which case delete the “Paste” line below)

Minimize file named in cell “Temp1"

Paste contents of clipboard to cell named “Temp1" - - (must retain formulas and external links)

Call subroutine “A” from file named in cell “Temp1" - - (Subroutine may only have a Return)

Call subroutine “B” from file named in cell “Temp1" - - (Subroutine may only have a Return)

Close file named in cell “Temp1"

Delete range name “Temp1" from current sheet

Make any screen adjustments required to regain original appearance.

Cursor (Marque) should end up where it started, over the “filename”.

End of macro

I suspect a macro like this already exists out there, I just don't know how to find it. Any help is appreciated.

LonnieP

 

[MintJulep]

Sounds doable, but not very efficient as described.

Don't use the clipboard, it's not needed. Just use variables in your routine to store the information.

You don't need to name the "Temp1" cell in the worksheet. Just find the address of the activecell and store it in a routine variable.

Set application.screenupdating = off before you start, then you don't need to restore any screen appearance afterwards.

Take a stab at it, and post back with specific questions.

 

[MintJulep]

Ok, this is sloppy, but it does most of what you ask.

Public Sub AProc()
Dim OtherBook As Workbook
Application.ScreenUpdating = False
grabbook = Application.ActiveWorkbook.Name
grabsheet = Application.ActiveSheet.Name
grabcell = Application.ActiveCell.Cells.Address
myfile = Range(grabcell).Value
Set OtherBook = Workbooks.Open("C:\Path\" & myfile & ".xls")
Application.Run (OtherBook.Name & "!mysub")
'Do your data copying here
Workbooks(grabbook).Activate
Worksheets(grabsheet).Activate
Range(grabcell).Activate
Application.ScreenUpdating = True
End Sub

Your SRS regarding copying "Range1" is not clear.

 

[LonnieP]

Mintjulep

Sorry for the delay in trying your code. It seems to work up to the point where it opens the other file where "range1" is located, then just STOPS.

In your code you have the following line:
Application.Run (OtherBook.Name & "!mysub")

I'm assuming this line attempts to run a macro named "mysub" in the just opened file. I don't yet have a macro in it yet and maybe that's why it stopped (without an error).

Question - Is it possible for the macro in the original file to keep running once the second file is open such that it can copy "range1" to the clip board and then minimize the second file. Otherwise I'll have to add a macro in the second file, something I'll need to do for other purposes anyway.

Please forgive some simple questions, but for me the answers aren't always obvious, at least not right away. I bought Walkenbach's VBA book for Excel and am beginning to remember why I gave up programming in Basic (in the early 80's) in favor of spread sheets. I'm here to drain the swamp, not play with the alligators.

Thanks for all the fish.
LonnieP

 

[MintJulep]

I'm assuming this line attempts to run a macro named "mysub" in the just opened file. I don't yet have a macro in it yet and maybe that's why it stopped (without an error).

Correct.

Question - Is it possible for the macro in the original file to keep running once the second file is open

Yes. If you had "mysub" in the second workbook that would run, then the procedure that called it would continue.

can copy "range1" to the clip board

You don't need to (and shouldn't) use the clip board. Just work directly with the range values.

 

[LonnieP]

MintJulip,

I recently came across a "help" item for the copy command. It indicated that copy to the clipboard is the default option but didn't give a very clear example of sending the copy info to a particular absolute and/or range named address. I assume if the destination is on another workbook a full file spec would be required. Could you please provide an example of copying to: 1) an absolute address in the current workbook 2) a named range in the current workbook 3) the same two in a different workbook. I've tried 1) and 2) several times but can't seem to get the syntax right.

Thanks, LonnieP

 

[MintJulep]

You don't have to copy. Just tell it

This = That

workbooks("book1").sheets("sheet1").range("A1:A3").value = workbooks("book2").Sheets("sheet1").range("A1:A3").value