1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Automate Excel Macro in Personal.xls

Discussion in 'Business Applications' started by blackburne, Oct 15, 2008.

Thread Status:
Not open for further replies.
  1. blackburne

    blackburne Thread Starter

    Joined:
    Oct 15, 2008
    Messages:
    1
    Hi all. This is my first question. I've searched the forums and can't find anything quite like I'm trying to do, even though it seems a fairly simple thing.

    Background:
    I am writing an application which will pick up csv files from a folder and put them in a database. This needs to be mainly done in IS in SQL Server 2005 for corporate reasons. However, the problem is that the data is not editable in IS. I am using XP and Office 2003 and SQL Server 2005.

    Problem:
    I have written an Excel macro to edit the data into a form that imports into IS and then a database, but the requirement is to automate the process. The source csv files will arrive with unknown names and at unknown times. Therefore the macro cannot be included in the individual files, and has been put in Personal.xls. Eventually, the IS routine will loop round and apply the editing and import to every file found in the particular folder.

    Now, forget all about the IS side - I can do that later. What I'm trying to do is to Open Excel with a given spreadsheet name and then run the macro from personal.xls. I do not want this to run every time I open any spreadsheet, just named ones. The macro has a shortcut key - (^+)Q as it happens - and one idea I had was to try to send this as a key to Excel. I can do it from an Access macro, which would do, except that I would have to enter the spreadsheet name into the Access macro every time, which defeats the object.

    The closest I've got was to overwrite the spreadsheet with '^+Q'! I managed to aceive this by Echo "^+Q" < Excel Csvfile.csv (Well, it was a long shot!) :)

    Now if there was just a SendKeys statement in Windows PowerShell or similar, I think that would crack my problem, but I can't find one except in certain proprietary pakages.

    Any ideas gratefully received!
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/759389

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice