I'm attempting to export 8am to 10pm times from cell A2 to A16 in an excel document created in code. I can get the Array to print from A1 to O1, but I want them to print from A2 to A16 is there a way to change this easily or will I have to do it manually? The code below demonstrates A1 to O1 displaying the times. [code] I used an array of Date to begin with and the output was always a decimal number representing the time of day I assume.
View 7 RepliesHow do i create ListObject(Table) in Excel with Table Headings vertically? or any other work around instead of ListObject?
View 1 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 RepliesOK I have been gooding for over 8 hours trying to figure this out. Bare with me I do not work with arrays much.I have brought an array in from a range in excel. This works fine. Through debugging i can see the values are brought in correctly.
Issue is I want to see if a string exists within that array (or cells).I have tried lots of things and pretty much mutilated my code but here it is:
[Code]...
I have successfully constructed several one-dimensional arrays and correctly passed them to an output subroutine. I am trying to pass/copy the values of each array to an Excel spreadsheet, but have encountered 2 problems.
Problem 1: The program fails on the following line: outSheet1.Range("B2").Resize(passFile1IndMax + 1).Value = passFile1Amount with the error message: "Value does not fall within the expected range"
Problem 2: When commenting out this line (and the corresponding one for passFile2Amount), the program no longer fails, however, the results show 8 rows (passFile1IndMax and passFile2IndMax both = 7) of the same data (e.g., A2:A9 all = 3/1/2011 = passFile1Date(0))--the first item in each array.I am trying to avoid looping through each index value and assigning values on a cellby-cell basis. Help...
I have been struggling to export an array from VB into an excel doc. I have no idea what I'm doing wrong.
The code below is the data that I am attempting to capture from a device.
Can anyone supply me with code to be able to capture the array and save the array in an excel doc?
[Code]...
I am trying to load an array from an excel sheet (in xml), whic I have managed by altering some code kindly provided by Beth Massi. the trouble is that I don't really understand some of the original code, hence I cannot remove the binding to some objects in the GUI that I don't need.
[Code]...
I am writing my code in Visual Basic 2008. I am trying to store the shapes I have added to an Excel sheet in an array and get this error message.[code]...
View 4 RepliesI have a btye array that I would like to load into excel. This will do this in vb.net with visual studio 2008.
How can I load the byte array into Excel via memory or with code?
I need to save array into excel file. I'm using code taken from [URL]
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
[Code]....
But i want to let users to choose folder and filename themselves instead of determining location and filename like in those code above oBook.SaveAs(sSampleFolder & "Book2.xls") I wonder if i can use SaveFileDialog but i dont know how to do it. Or is there any other way to do this?
I tried the following : (VB.Net code)
[Code]...
I have been tasked by my boss to create an application that takes information from an excel database holding sales figures, margins, budgets etc calculates all the figures and exports the totalled values to a table and graph also in excel. Although this is fairly easy, there is some complications I am dealing with. In the database there are many territories, and each employee has 1 or more territories.I'm not sure what I have done wrong, however the totals are not adding up properly. My code is:
'Defines Excel Objects
Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
[code].....
I have loaded a .TXT file into an array, and want to display each of the posts in the array in a grid.[code]...
View 1 RepliesHow do I effectively declare a Global Array which I can access the values of that array from any procedure? I have a Userform from which I will call various sub procedures depending on the actions performed on the useform. I have one procedure (Procedure1) where I have set up a multidimensional array and input values to it. I have another procedure (Prodedure2) where I want to access the values in the array - I DO NOT want to pass the array values directly from Procedure1 to Procedure2.
View 5 Repliesim currently importing an text file from .net into excel using the following code :
Dim oExcel As Excel.Application
Dim oWorkBook As Excel.Workbook
[code].....
I have formulas in certain cells and happens to be part of the Excel range that I pull into a vb.net array and when I write the array back out to the Excel Range, I loose all the formulas in it.Is there an option to retain formulas in a .net array?
View 3 RepliesHow to save a 2-dimensional array to Excel file. The result would be two columns in one Excel sheet.
View 39 RepliesI have done some code to write to Excel from a String Array of 3 items. These 3 items are written to columns 1, 2, 17 or 3, 4, 17, depending on the value of the 3rd item in the array. For certain Excel files, this is taking too long to write. Is it because I'm writing cell by cell? Is there any other way of writing faster to Excel?
View 9 RepliesI have created a COM Object via VB .NET and exposed some functions that I wish to call in Excel using VBA. I am currently trying to pass an array of objects as a result to VBA but Excel is complaining the following:
'Function or interface marked as restricted, or the function uses an automation type not supported in Visual Basic.'
I have looked around on the internet and have seen similar issues but not to one I am trying to do.My VB .NET function, which I wish to call in Excel is declared as follows:
Public Function CallArray(ByVal serviceName As String, _
ByVal effectiveDate As String,
ByVal serviceParams() As System.Object,
ByRef ArrayRes() As System.Object) As System.Boolean
VBA is complaining about the System.Objects my question is how the heck are you supposed to pass and return arrays from .NET to VBA. Also the data that will be in the ArrayRes() is mixed type, its not just strings etc, its an array of arrays that can contain strings, integers doubles etc.I chose VB .NET because I assumed it would be possible to map to VBA easier.if System. Object and Arrays in VB .NET is not the way, ie maybe theres another way via collections or lists, then Im open to that. I just need to get this data into VBA.Also I could move over to C# if needs be.
In VB6 I could assign an Excel range directly to a variant array dim vArr as variant vArr = xlSheet.range("A2:A10").value and then work with vArr to pull out the values I needed. This doesn't work in VB 2010. Is there a replacement procedure to allow me to read in a range (perhaps large) all at once and then process the data without needing Excel?
[Code]...
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 RepliesIn VBA / VB.NET you can assign Excel range values to an array for faster access / manipulation. Is there a way to efficiently assign other cell properties (e.g., top, left, width, height) to an array? I.e., I'd like to do something like: Dim cellTops As Variant : cellTops = Application.ActiveSheet.UsedRange.Top..The code is part of a routine to programmatically check whether an image overlaps cells that are used in a workbook. My current method of iterating over the cells in the UsedRange is slow since it requires repeatedly polling for the top / left / width / height of the cells.I'm going to go ahead an accept Doug's answer as it does indeed work faster than naive iteration. In the end, I found that a non-naive iteration works faster for my purposes of detecting controls that overlap content-filled cells. The steps are basically:
(1) Find the interesting set of rows in the used range by looking at the tops and heights of the first cell in each row (my understanding is that all the cells in the row must have the same top and height, but not left and width)
(2) Iterate over the cells in the interesting rows and perform overlap detection using only the left and right positions of the cells.[code]
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 am importing data from an Excel spreadsheet into an array. I want to search the array to find the location of each "cell" with text that contains a certain string (e.g. "MyTestString"). So far the best I can do is loop through each element and test it using CStr, but the code takes too long to execute (please assume all variables are properly declared):
[Code]...
(1) Is there a better way than looping through each element, such as a built-in function or a lambda that will return an array containing *the location of* all string matches?
(2) Would it be faster to convert the Object array to a String array rather than using CStr on each element? If so, what is the fastest way to convert a 2-dimensional Object array to a String array?
I've considered using Excel's Find function instead, but would prefer to limit the number of Interop calls to Excel.
I would like to add a vertical label on my from
View 11 Repliesi need to add some extra text boxes for that i need a vertical scroll bar for that?
View 2 RepliesI wanna add a Vertical Scrollar to my Application, but can't figure out how to do it.
Here is a picture:
So i add the Vertical ScrollBar to the Form into a TabControl but don't know how to make it function?
I am developing a window based application. I want to show my menu to be separated by a vertical separator. Just like this:
View 6 Repliesis there a way to display my dgv vertically?for example, this is how it displays now:Field1 | Field2 | Field3 | Field4 | Field5i want it to be displayed as:
Field1
Field2
Field3
[code].....
My problem is that I built this program for my VB class, but I built it on my desktop at home which is a 23" monitor. When this is opened on a smaller screen, you dont get to see all of the project. I want to add a vertical scroll bar but that is beyond what I have been taught and beyond what my book teaches...
how to get a vertical scroll bar to scroll the whole form?