I have tried to run below macro in excel 2010, but the error pop up and after press debug button, the line "expected_loss = application.worksheetfunction.average(final_loss)" was highlighted. May I know how to solve this problem?
Sub test ()
'Aim: to store data in array and calculate the average of the data in the array
Dim final_loss(1 to 70000) as double
[code].....
I found a few examples online on how to import excel into sql via vb. but can i do it via stored procedure?
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D: esting.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
So like, can I pass in:
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D: esting.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
As a parameter to SQL Server SP?
I'm working with an API, for this I'm using this particular call: [URL]
If i use this, I will get all the .price values:
For i = 0 To .bets.Length - 1
With .bets(i)
Print(.price)
End With
Next
Can I calc the average .price value? I will need to sum all the values as well.
I have put together an array for data from two text boxes to be stored in. This works fine at the moment. I am now looking to build some form of array that will have some stored data in it already, so that someone can select the information they require from a listbox.
View 7 RepliesI have a macro run program I have created using excel 2007.The excel version makes use of VBA designed userforms and macro code to run and it stores data within a worksheet.It populates some of the fields in the userforms from drop down boxes whose pre-populated values are stored within another worksheet in the workbook.If I was to create this within vb.net how would the drop down box values be stored? and also: would it be easy to have the userform on-close click event send the data to be stored in an excel workbook ?
View 3 RepliesI have some data, stored in a byte array, compressed with LZSS compression. I've crawled the net for the last 2 days trying to find some code to decompress this data without success. Has anyone come across a suitable routine that can help?
View 2 RepliesI want to reading in a excel file and extract the sCodenumber sDescription ans the sStatus and store them in a array : StatusComposeArray(5200) As MyCompose. After this I need this array for comparing outside this class. But as can you see the array is defined inside the sub: ReadingIn_ExcelFilesCompose [Code]
View 2 RepliesIn my desktop, I have folder containing some word document files. I want the filenames of the documents in that folder. So, I used:
Dim strFiles() As String = IO.Directory.GetFiles("X:Documents and SettingsXXXDesktopvvvvv", "*.doc")
All filenames are stored in that array. But it would also include the temp document that Word generates. I am somewhat confused on how to remove this from array. I only need full filenames of the actual documents, which excludes the temp document.
I am working on an inventory managment project for a lumber mill. I am currently writing a piece of software to be used on the sorting section of the mill. The problem I am having is when a stick of lumber needs to be cut down in lengt. My current setup has a combo box that is populated with lengths of lumber that we might have (2' - 60'), a "Add" button and a data control (homemade solution with lots of labels, and panels).
The user will scan a tag into the system, and the system pulls up the info from the db. If the piece need to be cut, the user will select the length of the cut, and click the add button. The system will then populate the first line of the data control with the info from the cut (previous piece info, and new length). It also populates a pieceObject struct. This happens for any additional cut that might happen.
The problem I am having is that everytime I press the "Add" button, the length variable will update every length in the piece array.
Here is a section of the code I am dealing with.
piece is a lumberPiece, and pieceAry is a array of 15 lumberPieces
Each time the add button is pressed, the current data is stored into the array, and then the pieceNum is incremented.
[code]...
Recently I have learned visual basic 2008 express edition and developed programs for data crunching and analysis. The VB 2008 express edition has microsoft visual studio 2008 v. 9.0.30729.1 SP and .NET Framework version 3.5 SP1. Now I want to create x-Y (date-Y value) graphs using the crunched data stored in an array list simulating a data base access. I download and installed the "Microsoft Chart Control for .NET Framework " and try to learn to use it within the VB 2008 express edition using the tutorial. It seems that the chart control did not get installed?
1. Do I need at VB 2008 professional to allow using the chart control?
2. Why after installing chart control, there is no change on the items under the NET components within the Toolbox>ChooseItem>.
3. How do I get to use the chart control under VB 2008 express edition.
4. Any code available to generate versatile graphics without using the chart control? Because the chart control has tons of classes already it seems the best is to just use the existing and downloadable chart control?
how to calculate numeric values in a sql table in vb.net. For example I am developing a system which returns the required fields in GridView, I then copied the "NetValue" field in a list box. Is ther a way of calculating (adding them together) all the Net values listed in the list box? or is there a better way around it?
View 2 RepliesI am writing a pizza order program for class and have gotten everything put together, even got my array setup and displaying but when i go to calculate it will not calculate the values coming from the array in any of the calculations.
[highlight="vb"]Option Strict On Public Class Form1 'declare structure and module-level variables Structure PizzaSale Dim SizaString As String Dim StyleString As Stri]
I am trying to open an exisiting excel sheet and then I want to copy a range of cells in to an array in one shot instead of looping thru cell by cell.And then I want to do some calculations and then move them to database tables.[code]...
View 3 RepliesI've come up with the following and it's partly working.
Public serial1, serial2, serial3, serial4, serial5 As SerialPort
Public serialports() As Object = New Object() {serial1, serial2, serial3, serial4, serial5}
dComPort = "COM4"[code]....
The above code works fine! But im having trouble trying to now retreive whats stored in the array.In the above example, an entry will be stored in serialports(4), all other array elements will be blank.If I do this it works.
MsgBox(serialports(4).PortName) ' this retuns the value COM4
But I'd like to loop though all array elements and print out ALL the PortNames, The below code doesnt work, I get an error Object variable or With block variable not set.
For i = 0 To serialports.Length - 1
MsgBox(serialports(i).portname)
Next
I use this simple code to export data from my dataset to excel. Now i want to export that data with a condition from the "cbVecka" combobox. I can't figure out how to do that. Any Ideas?
View 13 RepliesI am using visual studio 2012 to generate data. I am not using ASP.NET. My programs are on a CDROM disk. How can I use a button to save data into an excel file on the CDROM.
View 8 RepliesI want to calculate the average value of excel table readings 1 hr once... how do i import the sheet and how do i calculate the avg in vb.net?
View 4 RepliesI have the following columns : (Using Excel Formula)
A B
------------------------
1 | Date | Value |
------------------------
2 | 8/20/2011 | 92.8 |
3 | 8/21/2011 | 92.4 |
[code]....
I want to calculate the difference between 1st Value (B2) and last Value (last populated row in column B)
Edited :
Using formula : =B2-B6 is not what's required. (I want diff in Cell C2)
I want when the user enters the value in B7 it automatically shows the difference between B2 and B7, when he populates B8 then it shows the diff between B2 and B8 and so on.. I don't want some direct method to do this in Excel and not by iterating all values to check the last value.
I decided to to convert my excel macro in a exe file using visual studio 2010. In excel macro method find is present like this:
[Code]...
I need some code to calculate the CRC16 of array of bytes.
Example: 00 or 0x0.
The problem I run into is that the CRC I get with a calculator I use is: 78F0.
So 00 = 78F0.
Problem is I dont know what type of CRC16 they are using or anything else. But I would like to make the same calculator in Visual Basic.
Although I think for the Polynum they are using: 0x8408 and I believe the initial value is: 0xFFFF
Here is some code that supposibly is the calculation:
// this function calculates a CRC16 over a unsigned char Array with, LSB first
// @Param1 (DataBuf): An Array, which contains the Data for Calculation
// @Param2 (SizeOfDataBuf): length of the Data Buffer (DataBuf)
[Code].....
I finally got this code to work:
vb
Dim path As String = OpenFileDialog1.FileName
Dim myDataset As New DataSet()
Dim strConn = New OleDbConnection("Provider=Microsoft.ACE.Oledb.12.0;Data Source=" & path & ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1""")
Dim myData As New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn)
myData.Fill(myDataset)
DataGridView1.DataSource = myDataset.Tables(0).DefaultView
Now that I figured that out I was going to try and place the data in a specific location. On my application I have a datagridview set up with 4 columns. What I would like to do is put column A of the excel file under the 1st column of the datagridview and column C of the Excel File in the second column of the datagridview.
So replace:
vb
DataGridView1.DataSource = myDataset.Tables(0).DefaultView
With:
vb
DataGridView1.columns(0) = myDataset.Tables(0).columns(0)
DataGridView1.columns(1) = myDataset.Tables(0).columns(2)
Obviously this doesnt work, and something tells me I might need a for loop to import the data, but I have never imported information from an Excel file before and to make it worse I have never worked with datagridviews before so I have no idea how to go about this.
I would like to do something like this if I could:
vb
For x = 1 To xldoc.rows.length - 1
DataGridView1.Item(0, x).Value = CType(xlDoc.Cells(0, x + 1), Excel.Range).Text
Next
I have imported a set number of rows (17) into a DataGridView but how do im import like for example thousands of rows? Am i suppose to have thousands of code lines to import row by row?! Is there an easier way to import many rows without doing it row by row? Can someone please share with me how to do this i'm trying to learn more about VB programming as i find it quite intresting excuse me if this is something simple as im still a newbie at VB programming this is my code to import the excel rows
Private Sub Btn_Import_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Import.Click
Dim objExcel As New Excel.Application
Dim objBook As Excel.Workbook = objExcel.Workbooks.Open(excelPathName)
Dim objSheet As Excel.Worksheet = objBook.Worksheets(1)
[Code]...
I want to import multiple cell datas from excel and into different labels for each data. Is this even possible ? I have tried out so many different codes but just can't get it . My apologies in advance as i am new at vb the attached file is an example of the data i want to import to the labels.
View 3 RepliesI'm trying to import excel file into sql sever and then get some values that I decide.I read several posts but anyway I don't succed to proceding.First:
mypath=textbox1.text
Excelconnection = New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=" + myPath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'")
Excelconnection.Open()
[Code]...
i having trouble with exporting data from a dataset into an excel file, mainly i have included references to excel 12.0 and included the import line:Imports Microsoft.Office.Interopand i try to export a dataset to excel using a button that contains the following:
Dim row As Integer
Dim col As Integer
Dim rowCount As Integer
[code].....
I have some trouble with opening a excel sheet and getting some data out of it.
I want my program to have a button, when pressed, gives a "select excel file" popup and then load the data from some cells into my sql database.
So, when opening a excel file the program should get the data from cell D3 to D8 and put that into the sql database.
I now have the following, but that will only open the excel file.
Public Class Form1
Dim oConn As ADODB.Connection
Private Sub ConnectDB()
[Code]....
I have an Excel workbook which I created a sub-routine to insert data from the selected worksheets to a Sql database with the Sql stored procedure. I tested the sp using the Sql Management Studio and it works fine. However when I tried to call that store procedure and it failed. [Code]
View 1 RepliesExcel call to Sql Stored Procedure
View 2 RepliesI've got a stored procedure that right now generates a gridview. From that gridview, users want to be able to click on a button, and export it to Excel, where they can manipulate etc locally.But I used this
Dim sw As System.IO.StringWriter = New System.IO.StringWriter
Dim hw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
gv.RenderControl(hw)
Response.Write(sw.ToString)
Response.End()
and from that I get a message every time it goes to excel,"The file you are trying to open,'Defauly.xls', is in a different format than specified by the file extension. Verify that file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" Users don't want that message.. but I can't find any way of getting rid of it.any solutions that does not involve tinkering with system registries (it's not feasible for this project)?
My other option is to convert the gridview to a datatable, and have another process do the export. But I'm stuck on the part where I dynamically create the columns and column names from the gridview (or stored proc for that matter).. . it won't always be the same amount of columns etc, so I can't hard code that in.
I am trying to use parallel.for in order to calculate the summation of all the values in an multidimensional array. I googled and found Click Here. However, this is just for one dimensional arrays. The LINQ does not work for multidimensional array. And when I use parallel.for it gives me different answer than the sequential approach.
how to implement parallel.for for summing up multidimensional array.