vb.net,excel,visual-studio-2010 , How to output a single file for each row of an excel file read in?

How to output a single file for each row of an excel file read in?


Tag: vb.net,excel,visual-studio-2010

How can I output a file for each row of excel data? Right now it outputs the correct number of files but has the rows of data incremented, so file 1 is correct, file 2 has row 1 and row 2, etc.

 Dim smNum As Integer = 0

        If rowct > 0 Then

            For rr As Integer = 10 To rowct
                For cc As Integer = 1 To colct
                    val = CType(r.Cells(rr, cc), Excel.Range).Value

                    If val = "" Then Exit For

                    str.Append(ht((cc - 1).ToString)).Append(",")

                    'assigning the sample managaer num in column 1
                    If cc = 1 Then
                        smNum = val
                        file_name = selectedFile.Substring(13, 16) & "_" & smNum & "_" & todays_date & file_count & ".csv"
                        full_path = save_file_path & file_name
                        MessageBox.Show("Sample Manager Number: " & val & full_path)
                    End If

                    If cc < colct Then
                    End If

                    My.Computer.FileSystem.WriteAllText(full_path, str.ToString, False)


        End If


Use StringBuilder.Clear before each row.

        For rr As Integer = 10 To rowct

            For cc As Integer = 1 To colct

I would also suggest calling My.Computer.FileSystem.WriteAllText once per row since you are writing to the file on each columns.


VB.Net DateTime conversion

I have a jquery datepicker in a usercontrol. On the usercontrol i have a property that will return the selected date to the page which contains my usercontrol. I try to convert the string to a date: Dim MyDate As System.DateTime = System.Convert.ToDateTime(txtFromDateF.Value, System.Globalization.DateTimeFormatInfo.InvariantInfo) i keep getting the error: String...

timestamp SQL to Excel

If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

Removing Alert When Using DeleteFile API

I'm writing a VBA application which involves looping a large number of directories recursively. I am using the FindFirstFile API to to achieve this, as it offers a substantial performance boost over the FileSystemObject. In order to remove the FSO from my code entirely, I need a routine to delete...

Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In

I have a user-defined function in Excel that I run in multiple sheets. I am trying to use Cells(i, j) to pull the value of cells by their row and column in the sheet in which my function is called. Instead, Cells(i, j) pulls the value of cell [i,j] in...

NullReference Error while assiging values of Modeltype in MVC View (Razor)

I have the following MVC Model: Public Class Employee Public Property EmployeeID As Integer End Class Controller: Namespace Controllers Public Class EmployeeController Inherits Controller Function Details() As ActionResult Dim employee As Employee employee = New Employee employee.EmployeeID = 101 Return View() End Function End Class End Namespace View: @ModelType MVCDemo.Employee...

Visual Basic Datagrid View change row colour

Every other change to the datagrid view works fine but for some reason the row color just wont change. Ive debugged and my application goes through the loop to change the row color. Also I have a button that gives the datagrid view a new list and colors the rows...

Get XML node value when previous node value conditions are true (without looping)

Sample XML - <?xml version="1.0"?> <Root> <PhoneType dataType="string"> <Value>CELL</Value> </PhoneType> <PhonePrimaryYn dataType="string"> <Value>Y</Value> </PhonePrimaryYn> <PhoneNumber dataType="string"> <Value>555-555-5554</Value> </PhoneNumber> <PhonePrimaryYn dataType="string"> <Value>Y</Value> </PhonePrimaryYn> <PhoneType dataType="string"> <Value>HOME</Value> </PhoneType>...

Custom drawing using System.Windows.Forms.BorderStyle?

I want to mimick drawing of default border based on value of property BorderStyle. Instead of single border around the control, my control is visualised as four adjacent custom-drawn boxes (2×2), each having standard border drawn individually. So for example, if Control.Border is set to FixedSingle value I want to...

Comparing cell contents against string in Excel

Following is my table file:*.css file:*.csS file:*.PDF file:*.PDF file:*.ppt file:*.xls file:*.xls file:*.doc file:*.doc file:*.CFM file:*.dot file:*.cfc file:*.CFM file:*.CFC file:*.cfc file:*.DOC I need a formula to populate the H column with True or False if it finds column G in column F (exact case). I used following but nothing seems to...

Gridview items not populating correctly

I have data I am trying to input into a gridview. I am looking up the number of rows for the gridview and adding data into them like this: My "test" however does not get populated into the Submitted and Variance BoundFields in the Gridview. All that populates is the...

Can't output Guid Hashcode

I'm trying to use the hashcode of a guid as a random voucher name for a website, and have been trying to write new records to a SQL table with the Hashcode variable but it doesn't work. command.Parameters.Add("@voucherName", SqlDbType.NVarChar) command.Parameters("@voucherName").Value = Guid.NewGuid().GetHashCode() When using that code it just puts a...

Using a stored integer as a cell reference

Dim x As Integer Dim y As Integer For y = 3 To 3 For x = 600 To 1 Step -1 If Cells(x, y).Value = "CD COUNT" Then Cells(x, y).EntireRow.Select Selection.EntireRow.Hidden = True End if If Cells(x, y).Value = "CD Sector Average" Then Cells(x, y).EntireRow.Select Selection.Insert Shift:=xlDown Cells(x +...

Regex to check if string is alphanumeric separated by commas or a single alphanumeric string

I have the following right now which works fine if the string is alphanumeric and separated by commas, but it doesn't work if I only have a single string. "^([a-zA-Z0-9]+,)+[a-zA-Z0-9]+$" Works for something like "1A,1B,1C", doesn't work on "1A" Sorry, I don't know much about regex and I only got...

Using VLOOKUP formula or other function to compare two columns

I have one table like this: SHORT TERM BORROWING 1/6/2009 94304 12/31/2010 177823 6/30/2011 84188 12/31/2011 232144 6/30/2012 94467 9/30/2012 91445 12/31/2012 128523 3/31/2013 83731 6/30/2013 78330 9/30/2013 70936 12/31/2013 104020 3/31/2014 62345 6/30/2014 62167 9/30/2014 63494 12/31/2014 104239 3/31/2015 69056 I have another column which lists each date from...

Connecting to database using Windows Athentication

I would like to use window authentication in my program to connect to my sql server. users already have certain permissions on the SQL server and I would like to leverage that in my program. The way I currently connect to the server is using this connection string. Dim ConnectionString...

Scraping Javascript webpage (script error occurred)

I am scraping a dynamic webpage which is a javascript based webpage. I have done codes which is used to load the webpage first in the program: Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load WebBrowser1.Navigate("http://www.changiairport.com/flight-info/flight-status/passenger-departures") End Sub However, each time I run the program, script...

Excel VBA Loop Delete row does not start with something

I have some data at work looks like this: 00 some data here... 00 some data here... 00 some data here... 00 some data here... Other data I want to remove Other data I want to remove Other data I want to remove Other data I want to remove 00I...

Copying sheet to last row of a sheet from another workbook

I'm stuck in this block of code that copies sheet("Newly Distributed") to the last row of sheet("Source") from another workbook. The error is runtime error 9. What's wrong with my code? Any response would be appreciated. Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long...

Return index of word in string

This code: Module Module1 Sub Main() ' Our input string. Dim animals As String = "cat, dog, bird" ' See if dog is contained in the string. If Not animals.IndexOf("dog") = -1 Then Console.WriteLine(animals.IndexOf("dog")) End If End Sub End Module Return start position 5 in string But how to return...

how can i use parameters to avoid sql attacks

I have a project without any parameters used in SQL queries. Is there any solution so that i don't have to change the function and validate parameters from the Query string itself? Query = "select * from tbl_Users where userName='"& textbox1.text &"' and password='"& textbox2.text &"' " ds = obj.ExecuteQueryReturnDS(Query)...

adding variables into another variable vba

Dim x As Long Dim y As Long Dim CDTotal As Double Dim CSTotal As Double Dim ETotal As Double Dim FTotal As Double Dim HTotal As Double Dim ITotal As Double Dim ITTotal As Double Dim MTotal As Double Dim TTotal As Double Dim UTotal As Double Dim TotalValue...

Converting column from military time to standard time

I'm trying to convert a column showing the time of road traffic accidents from military time to standard time. The data looks like this: Col1 Time..24hr. 1 1404 2 322 3 1945 4 1005 5 945 I'd then like to convert to 12hr so for '322' I'd like to make...

How to insert excel formula to cell in Report Builder 3.0

There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

How do I do to count rows in a sheets with filters? With a suppress lines

I have a sheet with lots of columns, but when I filter and use count = Application.WorksheetFunction.CountA(Range("A:A")) It returns all the rows non Empty. Not only the rows I filtered....

Filtering Last Duplicate Occurrence In A Datatable

I am sorting a datable with the following code.. Dim sortedExtRecords1 As DataTable sortedExtRecords1 = parsedDataset.Tables("Detail").Clone Dim dvParsedDataset1 As New DataView(parsedDataset.Tables("Detail")) dvParsedDataset1.Sort = AuthorizatonConstants.Authorization_ID sortedExtRecords1 = dvParsedDataset1.ToTable("Detail") I can further filter the results to only return non duplicates and if there is duplicates I want the last record of the...

ZipEntry() and converting persian filenames

In my vb.net project I'm trying to add a file with a Persian name to a zip file and I do this with the code bellow: Dim myentry As New ZipEntry(dr.Item("MyFile").ToString()) zipOut.PutNextEntry(myentry) however when I open the zip file I see the file name is changed to a gibberish Is...

Set Label From Thread

Form1.vb Imports System.Threading Public Class Form1 Dim demoThread As Thread Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim Start As New Class1 Me.demoThread = New Thread( _ New ThreadStart(AddressOf Start.ThreadProcSafe)) Me.demoThread.Start() End Sub Delegate Sub SetTextCallback([text] As String) Public Sub SetText(ByVal [text] As String) ' InvokeRequired required...

Using a cell's number to insert that many rows (with that row's data)

I have data in excel that looks like this {name} {price} {quantity} joe // 4.99 // 1 lisa // 2.99 // 3 jose // 6.99 // 1 Would it be hard to make a macro that will take the quantity value ("lisa // 3.99 // 3") and add that many...

excel search engine using vba and filters?

I am using the following vba code to filter my rows in excel based on the value in my cell C5 Sub DateFilter() 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 ActiveSheet.Range("C10:AS30").AutoFilter Field:=1, Criteria1:="*" & ActiveSheet.Range("C5").Value & "*" Application.ScreenUpdating = True End...

Converting ADODB Loop into DAO

Hi I've been developing a vba project with a lot of help from examples here. I'm trying to access a MS Access database from Excel VBA and import large data sets (500-100+ rows) per request. Currently, the following loop works using ADODB however, the Range("").Copyfromrecordset line is taking very long...

Syntax error in Insert query in Mysql in VB.Net

I am doing project in VB.NET and backend is mysql Can you please tell me where the error is occured Public Sub ins() con.Open() Dim cmd1 As New OdbcCommand("insert into party values('" + pcode_txt.Text + "','" + Trim(UCase(name_txt.Text)) + "','" + Trim(UCase(addr_txt.Text)) + "','" + phone_txt.Text + "','" + combo_route.SelectedItem...

Excel - select a cell based on adjacent cell value

I have the following excel spreadsheet and I am trying to work out how I can write a formula in order to provide the values in column D. In each row, there is a test date, I am trying to calculate the day difference from each test date to the...

VBA - Unable to pass value from Private to Public Sub

I have a tool which I am designing to present a number of questions to a user in a set of userforms. The form will generate a score via passing an integer result from the userform to a main sub, which passes the code to a worksheet. My problem is...

Which is faster in Excel, an if formula giving 1 or 0 instead of true/false or --?

I've got a large spreadsheet that I'm trying to optimise as it has over 12,000 lines of data, with in excess of 28 columns. It currently takes a significant amount of time to execute and I'm therefore starting to pare it down. As part of this I've started looking at...

Excel - Pulling data from one cell within a list

I use PowerPoint as a graphics template to type up football player names and there squad numbers. It can be a long procedure and so far following YouTube tutorials i have managed to create a form in Excel which can update the text boxes in PowerPoint at the click of...

12 Characters Including leading and following zeros

I am finding this difficult to explain, but ultimately I am wanting a cells value to be 12 characters long including +/- a decimal point and following zeroes. Examples are 1200 would become +1200.000000 -20 would become -20.00000000 99999999 would become +99999999.00 I have tried FIXED, LENGTH, and formatting rules...

NoClassDefFoundError: UnsupportedFileFormatException while using apache poi to write to an excel file

I am trying to write to an excel(.xlsx) file using Apache poi, I included the apache poi dependencies in my pom.xml file. But I am getting the following exception in execution. Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:800) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142) at java.net.URLClassLoader.defineClass(URLClassLoader.java:449) at...

How do I use VB.NET to send an email from an Outlook account?

I'm trying to send an email via an outlook email account through a vb.net program. When I run the code I get an error telling me that I don't have a secure connection. I've been searching online and have tried all the adjustments I've found but I'm still not having...

check if a list contains all the element in an array using linq

I have a list of objects and I have an array of keywords. I need to check if all the elements of the array are present inside the list of objects. The user enters the data to be searched like this pain+fever+thirst+itching My code looks like: //i check if the...

If cell value starts with a specific set of numbers, replace data

My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.) If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are...

EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range

I want to change the next cell in same row in a if cell.value="word" in a range. I have defined the range, using 'for' loop. In my code, if cell.value="FOUND THE CELL" then cell.value+1="changed the next right side cell" cell.value+2="changed the second right side cell" end if I know this...

Comparing arrays with numbers in vb.net

I need a way to compare two arrays in vb.net and save result in third array: Dim KonRes(3) As Integer Dim UserRes(3) As Integer Dim YelRed(3) As Integer KonRes(0) = 1 KonRes(1) = 2 KonRes(2) = 3 KonRes(3) = 4 UserRes(0) = 4 UserRes(1) = 3 UserRes(2) = 2 UserRes(3)...

Retrieve full path of FTP file on drag & drop?

I can read the filename using next code when dragging a file from an Ftp folder browsed on Windows explorer. But is there a way to retrieve the full Ftp path? Private Sub DataGridView1_DragDrop(sender As Object, e As System.Windows.Forms.DragEventArgs) Handles DataGridView1.DragDrop Dim filename As String = "" If e.Data.GetDataPresent("UniformResourceLocator") Then...

Convert Double from String

When converting string to floating, the converter creates wrong results. ?Global.System.Convert.ToDouble("635705821821928755").ToString("0") "635705821821929000" ?Global.System.Convert.ToSingle("635705821821928755").ToString("0") "635705800000000000" I am working with VB.Net Visual Studio 2012, Framework 4 on ASP.Net Webpage. Is there any solution for converting huge numbers from string into floating?...

How to pass all value of ListBox Control to a function?

I am writing a simple application to read the value a textbox and add to a listbox control . But i have to pass the listbox control to function . Any suggestion ? Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load test("E:\Satyajit.txt") End Sub Public Function...

Get List of Elements in Tree with specific Field Value

I have an Interface like this: Public Interface TreeSelectorAttributes Property selectedInTreeSelector As Boolean Property Name As String ReadOnly Property childs As IEnumerable(Of TreeSelectorAttributes) End Interface and I have a TreeView which has a List of this TreeSelectorAttributes: Public Property rootList As IEnumerable(Of TreeSelectorAttributes) now after the User chooses which elements...

Convert date to string format

I have successfully imported data from xls into dataGridView. The date column in dataGridView is showing the DateTime format. I want this date column to be displayed in "dd MMM yyyy" format. I have tried as shown below: connExcel.Open() Dim dtExcelSchema As DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) Dim SheetName As...

VBA “Compile Error: Statement invalid outside Type Block”

I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

Identifying cell in Openpyxl

I've been working on a project, in which I search an .xlsx document for a cell containing a specific value "x". I've managed to get so far, but I can't extract the location of said cell. This is the code I have come up with: from openpyxl import load_workbook wb...

Interface Controls for DoEvent in Excel

I have a macro to loop through a range and return emails to .Display based on the DoEvents element within my module. I iterate that: row_number = 1 'And Do DoEvents row_number = row_number +1 'Then a bunch of formatting requirements Loop Until row_number = 'some value I am wondering...