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

  • Question

  • 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.

    Thank you in advance.

    Thursday, September 28, 2017 6:01 PM

All replies

  • Omnistia,
    Re: "I just don't know if you can link certain areas of the script with certain excel cells."

    Excel has a built-in programming capability using VBA - visual basic for applications.
    Most vb.script can be used within the Excel code modules and within VBA code.   

    To see a quick demo, right-click an Excel workbook sheet tab and choose "View Code" from the popup menu.
    Paste the following code into the larger, white upper right window that appears and tap the F5 key.
    Sub Demo()
      Dim vArr As Variant
      Dim vArr2 As Variant

      vArr = Array("Item  Number", "Description", "Location", "Update")
      vArr2 = Array("FP38466", "DB1D Gloss Black", "BW111", "yes or true")

      Range("B2:E2").Value = vArr
      Range("B2:E2").Font.Bold = True
      Range("B3:E3").Value = vArr2
    End Sub
    I doubt if I can provide help beyond the above.

    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)
    Friday, September 29, 2017 4:00 AM
  • Are you entering the data (Serial #/Location) into a database?  Excel VBA has a lot of options to connect to databases.  You could use your phone or tablet to enter the data in Excel mobile or a text file.  Have Excel desktop read the Excel file or text file, connect to the database and enter data.
    Friday, September 29, 2017 1:14 PM
  • thank you for responding Jim
    I'm using vbs because it was able to auto type for me, is thee a way for vba to auto type on my computer or on another program? if so i can just switch to vba instead to make things easier. I just want to find a way for my computer to auto type information for me. updating an excel file would be extremely easy for me which is why i would like to use excel.
    Friday, September 29, 2017 3:02 PM
  • I am entering data into a program called SWIDS which is extremely old since it's from 1998 and can't connect it to any other program/software in order for me to even use the program i have to go through a VN that doesn't have access to the internet... so my option's are very limited.. i guess my company doesn't like change very much.
    Friday, September 29, 2017 3:11 PM
  • O,
    re: "is there a way for vba to auto type"

    VBA includes...
    SendKeys:  Sends one or more keystrokes to the active window as if typed at the keyboard.
    Application.SendKeys:  Sends keystrokes to the active application
    Application.OnKey:  Runs a specified procedure when a particular key or key combination is pressed

    I ran some code, using the above statements/methods about 20 years ago and have not used them since.
    I am not the one to ask about their use.

    Your existing code may work in Excel VBA.  You will have to test.
    Jim Cone

    • Edited by James Cone Friday, September 29, 2017 3:45 PM
    Friday, September 29, 2017 3:44 PM
  • thank you Jim I'll give it a try and if it works i will just need to find a way to link the auto typer with certain excel cells.
    Friday, September 29, 2017 3:49 PM
  • What program are entering data into?
    Saturday, September 30, 2017 1:05 AM
  • I have to enter data to a program called SWIDS

    I believe a great work around for this to work would be to have Excel open notepad using VBA and having it auto type a VBScript for me, so instead of having a vbscript look up information from a certain cell, the VBA would then get the information i need and put it in the correct format then auto type everything onto notepad which i will then save as a vbscript so i can move to my vn and have do what i needed it to do.

    In theory this sounds like it can work but i have no idea how to type this out in VBA form... so looks like i have to hit the books and try to figure this out..

    Monday, October 2, 2017 10:46 PM
  • You should look at AutoIt (free).  I've used it automating GUI input into numerous programs.  It uses a basic type language.  It can read a text or csv file and input the data into SWIDS.
    Monday, October 2, 2017 11:02 PM
  • thank you i will look into this program and let you know how it goes.
    Monday, October 2, 2017 11:26 PM