Converting vba into vb

[abousetta]

Hello,

I need some help converting the code below into a standalone visual basic exe file.

Simply, the code loops until it finds a window that is generated by a program called "EndNote". Once it find this window, it clicks on two buttons in sequence, which will close the windows (of course others things will happen in EndNote but that's outside the scope of this vba code). In the attached file, there is also a "Cancel" button in a userform that allows the user to exit the program, and close the workbook.

I want to share this with others, but don't want it to be hinged on having Excel installed, macros enabled, etc.

Thanks in advance.

abousetta

ThisWorkbook:

Please Login or Register  to view this content.

Standard module:

Please Login or Register  to view this content.

[abousetta]

Bump......

[abousetta]

Bump...... Day 2

[Kyle123]

I'll have a look at this in the morning for you if noones helping you. It will be C# rather than vb though since I don't have it installed. I'll try to keep it as close to your code as possible though so you can follow it if that's ok?

[abousetta]

Hi Kyle,

Thanks for looking at this for me. I'm just going to go with an easy workaround from the MVP Orlando's site that converts it to an Exe. It isn't perfect but at least it should allow me to share it without much hassle. I will PM you because I have a question about doing this large-scale and whether or not it's worth it.

Thanks again.

abousetta

[Kyle123]

No problems

FWIW, the C# would look something like (whilst staying as close as possible to the original code):

namespace clicker
{
    public partial class Form1 : Form
    {
        [DllImport("user32.dll")]
        public static extern IntPtr FindWindow(String sClassName, String sAppName);
        [DllImport("user32.dll")]
        static extern IntPtr FindWindowEx(IntPtr hwndParent, int hwndChildAfter, string lpszClass, string lpszWindow);
        [DllImport("user32.dll")]
        static extern IntPtr GetDesktopWindow();
        [DllImport("user32.dll")]
        static extern IntPtr GetWindow(IntPtr hwnd, Int32 hwndtype);
        [DllImport("user32.dll")]
        public static extern IntPtr SendMessage(IntPtr hWnd, UInt32 Msg, int wParam, [Out] StringBuilder lParam);
        [DllImport("user32.dll")]
        public static extern IntPtr PostMessage(IntPtr hWnd, UInt32 Msg, int wParam, int lParam);
        [DllImport("user32.dll")]
        static extern IntPtr ShowWindow(IntPtr hWnd, int wFlag);
        [DllImport("user32.dll", SetLastError = true, CharSet = CharSet.Auto)]
        public static extern int GetWindowTextLength(IntPtr hWnd);

        private const int WM_LBUTTONDOWN = 0x201;
        private const int WM_LBUTTONUP = 0x202;
        private const int GW_HWNDNEXT = 2;
        private const int GW_CHILD = 5;

        private Boolean cancelMe = false;

        public Form1()
        {
            InitializeComponent();
        }

        private void UpdateEndNoteReferences()
        {
            //string ClassName = "#21770"; Not actually used
            string SearchName = "Review Available Updates - ";
            cancelMe = false;
            do
            {
                IntPtr parentHandle = FindWindowLike(GetDesktopWindow(), SearchName);

                if (parentHandle.ToInt32() > 0)
                {
                    Debug.WriteLine(WindowText(parentHandle));
                    IntPtr childHandle = FindWindowEx(parentHandle, 0, "Button","Update All Fields ->");
                    clickButton(childHandle);
                    childHandle = FindWindowEx(parentHandle, 0, "Button", "Save Updates");
                    clickButton(childHandle);
                }
                Application.DoEvents();
            }
            while(cancelMe==false);
        }

        private void clickButton(IntPtr hwnd)
        {
            PostMessage(hwnd, WM_LBUTTONDOWN, 0, 0);
            PostMessage(hwnd, WM_LBUTTONUP, 0, 0);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            UpdateEndNoteReferences();
        }

        private string WindowText(IntPtr hwnd)
        {
            const int WM_GETTEXT = 0x000D;
            int ln = GetWindowTextLength(hwnd);
            if (ln > 0)
            {
                StringBuilder sb2 = new StringBuilder(ln + 1);
                SendMessage(hwnd, WM_GETTEXT, sb2.Capacity, sb2);
                return sb2.ToString();
            }
            return "";
        }

        private IntPtr FindWindowLike(IntPtr hwnd, string Caption)
        {
            Regex regex = new Regex(".*" + Caption + ".*");
            IntPtr lhwnd = GetWindow(hwnd, GW_CHILD);
            do
            {
                if (regex.IsMatch(WindowText(lhwnd)))
                {
                    return lhwnd;
                }
                else
                {
                    lhwnd = GetWindow(lhwnd, GW_HWNDNEXT);
                }
            }
            while (lhwnd.ToInt32() > 0);
            return System.IntPtr.Zero;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            cancelMe = true;
        }
    }
}  

[abousetta]

Thanks Kyle. My ignorance of C sharp comes to light ... I put the code in txt file and changed the file extension to .exe but it didn't work (I tried a few more for fun but still no cigar). Am I even remotely on the right track?

abousetta

[Kyle123]

No, not even remotely

You'd need a copy of Visual c# developer installed (the express version is free) it then needs compiling and turning into an application.

I'll do it when I get a few mins if you like, the only issue that I obviously can't check the code

[abousetta]

Thanks Kyle. I do have Visual C# express edition installed. I'll try to play with it over the weekend and see if I can get anywhere. It will be a learning experience.

Thanks again.

abousetta

P.S. Now I owe you two reps

[Kyle123]

haha no probs

[abousetta]

Thanks. This works much faster than its original vba counterpart. Makes me wonder why? I guess C# is more optimized/ in sync with the windows API. Anyways, that's a thought to investigate on another day.

abousetta

[Kyle123]

It actually works? I figured it might want a bit of tweaking which is why I posted the code.

c#/vb.net speeds are incomparable to VBA, they're much faster. They're compiled so they are much faster, VBA just runs in an Excel instance so it's not suprising really.