automate excel historian values in excel

[jeannier1975]

I have a function i have run into a issue and i need help i have a function in an excel sheet that input value in excel depending on a start date and end date i need to have a button that inserts the data the function is retreiving into the nextline.
the function is =

http://wwWideHistory3("LEWC2012INSQL",

Sheet1!$A$1:$A$63,"Row100",AFStartBinding,AFEndBinding,254,0,0,0,0,3,0,"",3,"",-1,0,"","NoFilter",20480)
In layman's terms I need a vba code to insert into a button that does following but not sure where i can get the answers i seek. first i need the VBA or macro to inter the function listed above.
Second get that function to convert to values.
thirdly and last refresh the worksheet.
I have attached the excel sheet to my email. If you have the time please advise.

 

[SkipVought]

Hi,

First [highlight #FCE94F]correct[/highlight] your formula...
[tt]
strFunc = "=

http://wwWideHistory3(""LEWC2012INSQL"",

Sheet1!$A$17:$A$21,""Row7"",AFStartBinding,AFEndBinding,254,0,0,0,0,3,0,[highlight #FCE94F]""""[/highlight],3,"""",-1,0,"""",""NoFilter"",20480)"
[/tt]

Now it should be free of syntax errors.

However it may have logic errors, which you must determine. The AFStartBinding and AFEndBinding dates may not be as the function expects. I can't determine that.

ALSO, Your

http://wwWideHistory3()

function???

ALSO, ExtractDate is a misnomer as what you seem to have is DaysDuration.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jeannier1975]

i was just guessing at VBA code i need to put in there.
What i wanted to do is come up with a VBA that inserts the formula on the next line and calculate the value on the sheet.
Im not that well with excel VBA just learning.
The AFStartBinding is where the user enters the start date and the AFEndBinding is where the user enters the end date.
The function i put in to enter the date in the cell. not sure if i need that

 

[SkipVought]

Well I have several questions.

First off, seems you are using an Addin for this History application that can report on all kinds of factory machines and processes. Do you have this Addin installed in your Excel?

Then you have a function in your VBA that actually calculates the difference of two Date/Time values. HOWEVER, you also seem to have if History function, that was missing some quotes in on argument, that I fixed for you. Did you try that fix?

However, to run the fix, you need a new function for

http://wwWideHistory3,

that uses all those arguments, not just two!

So I'm very confused about your VBA.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jeannier1975]

The formula works when i copy and paste it in the call but i do not know the VBA to write so the user can just click on the button to retrieve the results.

 

[SkipVought]

What exact formula are you referring to?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Look! If you want to post a very similar question in stackoverflow and Eng-Tips (which is considered bad manners) then just delete your original post and we can forget it.

Pick a site and stay with it until you're done.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jeannier1975]

Do you have this Addin installed in your Excel?
Ans: Yes i have the add in installed.
Then you have a function in your VBA that actually calculates the difference of two Date/Time values. HOWEVER, you also seem to have if History function, that was missing some quotes in on argument, that I fixed for you. Did you try that fix? I fixed the issue with missing quotes. thank you.

However, to run the fix, you need a new function for

http://wwWideHistory3,

that uses all those arguments, not just two!
how do i do that VBA is not my greatest knowledge i am a newbie and i am trying to get it to work but not sure every online resource doesnt show me exactly what I am looking for. Why i have reached out to so many sources.

 

[SkipVought]

You got this function from somewhere. What did that documentation say? If the addin is a .xlam, then it most likely has the

http://wwWideHistory3

with 19 defined arguments. There should be an explanation of each argument. What are the descriptions of the 19 arguments?

Depending on that documentation, it should tell you how to use that function. How do you use this function?

A function returns one value. What value is your function returning in each row?

If you are entering the function into multiple rows, then one or more of the arguments must have a value to differentiate it from the function in some other row. What argument is that?

Please post the actual formula from a cell in your sheet.

I don't know how much I can help you without knowing some of the answers.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jeannier1975]

I didnt get it from somewhere i got from facebook group but i beleive there an easier way Since its not working

 

[jeannier1975]

Jeannie,



Just like in Access, pressing the F11 key will take you right to the VBA interface we all know and love! It looks and works just the same as it does in Access… the only real difference is that building user forms is very clunky compared to Access. Alternatively, you can go to the Developer tab (on the Ribbon) to run macros, open the VBA editor, and other options. By default, the Developer tab is usually hidden… to make it visible, you need to go to the Backstage view (the File button in the upper left corner), and the Customize Ribbon option. Just check the Developer checkbox in the right-hand pane to show it on the Ribbon. You don’t necessarily have to use the Developer tab on the ribbon to start recording a macro… you can also start a macro recording by clicking the little icon in the lower left corner of the Excel interface (see last screenshot below).



Macros in Access are entirely different than they are in any other application in the MS Office suite. In Excel, Word, Outlook, etc… when you record a macro, it is actually capturing all of your keystrokes from clicking buttons etc... and creating VBA code to perform these operations. In fact, using the Macro Recorder in Excel is a great way to start digging into Excel VBA… just start the recorder and do something with a worksheet… write a formula, add conditional formatting, change the font or cell fill color. It is all written to a VBA module, and you can see all of the structure to make these changes. Of course, there is a lot of code that is actually unnecessary, and it is in no way dynamic, but it works. I use this all the time since I am not quite as familiar with the Excel object model as I am with Access.



Another option I like to use instead of putting a shape or button on a worksheet to call a VBA function, is to place an icon on the QAT (Quick Access Toolbar) to do this job. The one caveat to this is that the function will only be available to you… not any other users. It all depends on how you want your workbook to be used.



Have fun with this! Once you get to know Excel better, I think you will come to love it. There are some things it just does better than Access.

 

[SkipVought]

What was that?

You never answered my questions.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jeannier1975]

The formula on my sheet that historian addin creates is =

http://wwWideHistory3("LEWC2012INSQL",

Sheet1!$A$17:$A$27,"Row7",AFStartBinding,AFEndBinding,254,0,0,0,0,3,0,"",3,"",-1,0,"","NoFilter",20480)

 

[SkipVought]

And what value(s) are returned and in what cells?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein