Using excel data as VBS output

[gediminas_rapalis]

Hi there,

i've been looking over the net trying to find solutions on how I can use excel data variables copied to vbs script and launched, but couldnt manage to make it work, so I hope someone can help me as I think solution is simple.

What I need to do is to copy Columns B:H from sheet "script_OC" to a text file named .vbs and then launch it. B:H columns holds the code already adjusted by inputs in excel. It works fine if I just copy paste to text document and launch as vbs.

Any suggestions? of course it is very important that layout is copied correctly, because I managed to get write value for each cell, but then idents are not correct.

[holger_khn · Accepted solution]

Hello.

I´m not quite sure if I got your Point. May you can upload an example how it Looks in Excel?

[gediminas_rapalis · Accepted solution]

Hi Holger,

here are attached script samples. As mentioned, I have the code written in excel which has some dynamic parts in it that changes based on input in other cells on another sheet. Columns used B:H because each column acts as an indent when copy pasting script.

So all I want to do is launch the attached script with Windows Host with the code from excel.

Edit: I am attaching txt file as I cant put excel, but if you copy code to excel, you will get 7 columns filled in

BR,

Gedis

code_sample.txt.zip

[holger_khn · Accepted solution]

Hello.

Do you have an specific reason why you want to convert this Excel range first into an vbs-file and then execute?

It´s much easier to implement this code in Excel VBA and adopt to SAP via Excel VBA.

So you can use variables which can be used as dynamic Input for an static procedure.

When you want to use different coding procedures transferred from Excel range to an vbs-textfile I suggest to seek for creating Textfile by Excel VBA. There are a lot of code snippets available in several VBA Forums.

[gediminas_rapalis · Accepted solution]

Hi Holger,

Can you help me adjusting code so that it would work from VBA? Because I tried to do it as you say with my first thought and I was unable to do it. Code would not compile and wouldnt launch SAP. Perhaps I need to make some definitions.

I did look for code snippets on the net, however all "write" commands mess up the layout when copied cell by cell. I could not find direct way of pasting cells into the text document to keep layout, thus why I am writing here.

[holger_khn · Accepted solution]

Hello.

It depence on what is the static part of your coding. Which variables got changed by Excel cell as Input data.

Is it a Loop of an coding procedure which Need to execute for several lines?

So I am not really surethat I understand your requirements in Detail.

May you can eloborate a Little bit more in Detail  below questions:

1. What is the static part of your coding?

2. Which variables are used?

3. How the layout for Input data in Excel Looks like?

[gediminas_rapalis · Accepted solution]

Hi,

I know there are some differences in VBA and VBS. My only requirement is to run VBS code that is written in excel cells. If I can run directly from excel VBA or Save as VBS file on disk, doesnt matter. I thought latter option was supposed to be easier though.

to answer questions:

1. Static part starts after first loop line: for i=0 to 1(dynamic). Everything before that is dynamic defined by excell (not including SAP object declarations and first dim variables). So in short following variables are dynamic: (ETA, text, Route, Print, ALE, Iteminput)

2. ETA, text, Route, Print, ALE, Iteminput, and for i=0 to 1 where 1 is dynamic.

3. Picture below

[holger_khn · Accepted solution]

MAke it not clearer for me.

May you can record an new script by using SAP Scripting Recorder. Please record as well in which Transaction this is executed => VA02(?).

Then attach your recording to this thread. May this will make it much more visible what is required.

Thanks.

[gediminas_rapalis · Accepted solution]

Hi,

I am not sure how new script will help? The one attached is the script that has been recorded and adjusted according to needs. All I need is to launch that script (or any other) with Excel Macros.

If I can figure out how to do it, the rest adjustments I should be able to work it out.

[holger_khn · Accepted solution]

Hello.

Ok, attached an Picture about an Excel template I have developed. May you can post an Picture how many columns and which naming them should have for your requirement.

Depence on this I Need to modify my Basic coding. Then I can upload an example where you Need to implement your coding according to your requirements.

[gediminas_rapalis · Accepted solution]

Hi Holger,

I think I got it. I used below code as a solution. It seems to do the job, so I will do some testing later on to see if I can find any bugs once everything is integrated.

thanks for your support.

Sub note()

f = FreeFile

n = 1

Sheets("script_OC").Select

Open "C:\Users\SERAPALISG\Desktop\Revolution\Automation scripts\Scripts\test2.vbs" For Output As f

LastRow = Cells(Rows.Count, "B").End(xlUp).Row + 1

'MsgBox (LastRow)

    Do

        For Each cel In Range(Cells(n, 2), Cells(n, 8))

        If cel = "" Then

            Print #f, "    ";

        Else

            Print #f, cel;

        End If

        Next

        Print #f, ""

        'Print #f, vbCrLf

        Debug.Print

        n = n + 1

    Loop Until n = LastRow

Close f

End Sub

BR,

Gedis

[holger_khn · Accepted solution]

Hello Gedis.

That sounds great. I still believe you can achieve your requirements much smarter by using Excel VBA coding and skip convertion to vbs-file.

As you can see in my screenshot you will be able to select an open (non-busy) SAP session for execution directly out of Excel template.

[gediminas_rapalis · Accepted solution]

Hi,

can you give a sample code of a script launched from VBA that does adjustments in SAP? I did look into it and saw that there are some diferences between VBA and VBS coding that would require me to do conversion. I am rather interested to see how SAP is launched from VBA code for starters. I might work on your suggestion later and integrate everything in VBA.

BR,

Gedis

[holger_khn · Accepted solution]

Hello Gedis.

As macro-enabled Excel files can´t upload here I will prepare an tutorial how to Setup an Excel macro-enabled template as basic Framework and explain how to modify for several purposes.

May this will take some days (7-14 days). But I will update this thread as soon as I have it done.

Best regards,

Holger