Results 1 to 2 of 2

Thread: Is there a way to connect or link a vbscript to an excel file?

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2017
    Posts
    1

    Is there a way to connect or link a vbscript to an excel file?

    Hello Everyone,

     let me explain what I'm trying to get done, about once every 2 weeks I need to go into the warehouse write down every product's serial number we have and its location from there I must go into our system's inventory and manually enter the serial number and then change its location (our system is old by the way). My idea was to create vbscript that auto types and navigates through my system which I was able to do and save me some time but I still have to edit the script with the updated locations and it takes a while to edit that huge pile of text... (I'm still new to vbscripting) so I was wondering is there a way to link my vbscript to an excel file. for example, have it nice and organized like:

    Item Number   Description           Location    update

    FP38466     DB1D Gloss Black        BW111    yes or true

    then have the vbscript check to see if a certain item needs to be updated and if it does it will update it and if not, it will just skip that item. Here is how my script mostly looks like:

    WshShell.SendKeys "FP38466{ENTER}"
    WScript.Sleep 200
    WshShell.SendKeys "{ENTER}"
    WScript.Sleep 300
    WshShell.SendKeys "e"               (This is to go to the edit screen)
    WScript.Sleep 200
    WshShell.SendKeys "{ENTER}{ENTER}{ENTER}{ENTER} " (This is so I can get to the location)
    WScript.Sleep 200
    WshShell.SendKeys "BW111{ENTER}"
    WScript.Sleep 300
    WshShell.SendKeys "s"               (This is to save my changes)
    WScript.Sleep 300

    I have the navigation down but is there a way to instead of writing the location number in the script, it will go into the excel file look at that location cell and type that instead? also since I basically have this for every item is there a way to tell the script to look to see if this item need to be updated and if it doesn't to just skip it. I just don't know if you can link certain areas of the script with certain excel cells. If anyone knows a better method to do this I am open to suggestions.

    I've been in other forms and I've been told it's not possible because Excel uses VBA so i was wondering as an alternative can the VBA get the information i need and open up notepad and then type everything i asked for in VBScript form? i know this sounds like a huge mess but it really doesn't help that the database program I'm using is from 1998 and i have to connect to a VN to even use it, the VN is also restricted from going online so i don't have many options sadly. The program i use is called SWIDS.

    Thank you in advance.

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Is there a way to connect or link a vbscript to an excel file?

    A better idea might be to use a VBA macro on the Excel file to generate the VBScript you need to update the Locations. I am assuming that your VBScript has some fixed code and some code that changes each time for the serial numbers and locations update process.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width