Excel: macro fails consistently

[terrypin]

Anyone using Excel in Win 10?

The sort of macro that used to work smoothly in Excel 2000 on my XP PC now fails and despite a couple of hours work I cannot fathom why.

If I peform these operation manually I can successfully copy the link as fast as I can hit Ctrl+C. But the macro fails.

It's possibly connected with the fact that, after giving the command to open the hyperlink editing dialog, the blue circular 'spinner icon' continues for several seconds (longer from the macro command) until the folders appear. Yet the link itself appears immediately, and as mentioned can be manually copied to the clipboard immediately.

// Assumes cell with hyperlink is selected.
Text Type (Simulate Keystrokes): <SHIFT><F10> // Context menu
Delay: 0.1 seconds
Text Type (Simulate Keystrokes): h // Select 'Edit hyperlink'.
Delay: 0.1 seconds
Text Type (Simulate Keystrokes): <ENTER> // Open the hyperlink for editing.
Delay: 1 seconds // Regardless of duration (tried 10 s) still not working.
Clipboard Copy
Delay: 0.2 seconds
Text Type (Simulate Keystrokes): <ESC> // Close hyperlink dialog.
Delay: 0.1 seconds
Variable Set String %tHyperlink% from the clipboard contents
Text Box Display:

Any ideas please?

[acantor]

I am using Windows 7, not 10, and Excel 2007, and not something newer. So things may be a little different on my computer. A few thoughts:

1. Try <Ctrl>k to open the hypertext link instead of opening it via the context menu.

2. I need to select the email address from the Edit Hypertext dialog box: <Ctrl>a should work, but so should <Home><Shift><End>.

3. Instead of opening the hypertext link, try editing the cell, something like this...

<F2>
<Home><Shift><End>
<Ctrl>c

4. How about copying the cell directly, without editing it?

<ARROW UP><ARROW RIGHT>  // navigate to a cell
<Ctrl>c

 

[terrypin]

Thanks Alan. Ctrl+K is a nice shortcut I'd forgotten and makes the macro neater. But nothing so far will make the full macro work. Did you try something similar? I'd be interested to know if it's just a Win 10 or Excel 365 issue (or both). The exasperating thing is that it's so easy manually, using exactly the same operations! Ctrl+K, Ctr+C, done, full link is on clipboard.

The hyperlink is selected automatically when the edit dialog opens, but I also tried re-selecting it using the mouse, with same result. Namely, tHyperlink is empty, so nothing was copied to clipboard.

It's rather like the behaviour I get when trying to run a macro immediately after opening my video editor, Movie Edit Pro: none will respond to activation until 10-15 seconds later.

I can't simply use the text content.The cells don't hold the full text of the hyperlink, because I deliberately  display only the filename.  In these three examples I've shown the full text only in the last one.

USA2016-Final-3-50fps.mp4
Jersey2017-Final.mp4
D:\Videos+Projects\PROJECTS\Bolzano 2017 Proj\Bolzano2017-Full-3.mp4

I've resorted to seeking help on using Excel VBA to do it (and the rest of the task I'm attempting to automate) and am close to success. But I'd dearly like to know why ME Pro won't cut the mustard.

Terry, East Grinstead, UK

[acantor]

Hi Terry,

I recently completed a project of automating aspects of Excel using a different scripting tool (i.e., not Macro Express). For the first part of the project, the client's PC was running Windows 7. For the second part, the PC was a Windows 10 machine.

A handful of scripts that worked beautifully under Windows 7 failed under Windows 10. So it would appear Microsoft has thrown curveballs to those of us who use third-party scripting tools.

In every case I found workarounds; but the workarounds were not always elegant.

In your situation, the next thing to try is alternative ways to copy the field. For example, instead of sending Ctrl + C, try copying the text via the context menu.

Or cut the email address, pause for half a second, and paste (which will allow you to see whether the clipboard operation was successful.)

Or just cut it, and forget about pasting it back.

Or substitute Macro Express's clipboard commands when copying and pasting instead of Ctrl + C and Ctrl + X; or vice versa.

If none of these approaches are successful, I would delete the macro and re-create it from scratch. On second thought, I would do this next!

And if you are still not successful, report the problem to the good people at Insight Software. This may be a Windows 10 compatibility issue.

[terrypin]

Thanks Alan. Curiosity and my discomfort over unsolved puzzles may get me back on the case later. But happily (after great help from the VBA experts) I now have a working VBA macro that extracts the full hyperlink and uses it to select that file (i.e. highlight it) in its File Explorer folder. And all very fast, of course. What I'm about to do next (hopefully with Macro Express) is to open that in MediaInfo, capture its size (in GB to 1 dp) and enter that into my worksheet. If you're curious, I've attached an illustration.

It might have been possible to approach it another way, which may even have worked with Macro Express. Namely to open the video file directly from the hyperlink, which by file association plays it in MPC HC, right click to get the context menu, select Properties and copy paste the size from there, etc.

 

 

[Cory]

Terry I don't know if it would work for you but if you work with delimited text files with MEP you can link to them in Excel. This allows MEP to edit with things like ASCII file process effortlessly and use that data in Excel. Excel can link to so many cool things, including web pages. They even actively update.

[terrypin]

Thanks Cory. In the couple of months or so since I posted I've made a bunch of changes.The main cause of that particular problem was the sheer size of the folder containing my workbook: 320 GB and 19,000 files in 720 subfolders. When I moved it out of that (logical) location to an almost empty folder the problems were resolved.

I agree about Excel and text files being useful companions. I recently speeded up a task greatly by using MEP to create and save a text file containing a list of some 50 variables I'd generated and then using VBA to import and process them.

[Cory]

You might also consider using databases. MS SQLExpress and MySQL are free. And for basic use, they're easy to learn. And Excel loves to connect to Dbs.