vba merging excel files

Posted on June 2, 2011 by admin

Offen I got calls from customers asking me to mail merge excel files with calculated data on one excel file and data feed on another sometimes with printed area on other workbook of template excel file. The best way to accumplish this task would be creating micro or small UI form with vba code accessing second excel file with ADO (ActiveX data object). In order to that we need to add reference to ADO object in VBA. Click on tools and references on menu. See screenshot below how I did it.

Adding ado references

After that we would need to write vba micro which will access data feed excel file and read line by line into appropriate excel cells then print workbook3 selected range. See code below.

Private Sub CommandButton1_Click()

Dim cn As ADODB.Connection
Dim rsT As New ADODB.Recordset
Dim intTblCnt As Integer, intTblFlds As Integer
Dim strTbl As String
Dim rsC As ADODB.Recordset
Dim intColCnt As Integer, intColFlds As Integer
Dim strCol As String
Dim t As Integer, c As Integer, f As Integer
Set cn = New ADODB.Connection
With cn
.Provider = “Microsoft.Jet.OLEDB.4.0″
.ConnectionString = “Data Source=c:\ProspectList.xls;Extended Properties=Excel 8.0;”
.CursorLocation = adUseClient
.Open
End With

rsT.Open “select * from [sheet1$];”, cn
Do Until rsT.EOF
Worksheets(1).Range(“C16″) = rsT.Fields(3).Value
Worksheets(1).Range(“C18″) = rsT.Fields(4).Value
Worksheets(1).Range(“C19″) = rsT.Fields(5).Value & ” ” & rsT.Fields(6).Value & ” ” & rsT.Fields(7).Value
Worksheets(1).Range(“E89″) = rsT.Fields(11).Value
Worksheets(1).Range(“C30″) = rsT.Fields(23).Value
Worksheets(1).Range(“C27″) = rsT.Fields(24).Value

Worksheets(3).Range(“A1:J248″).Select
Selection.PrintOut Copies:=1, Collate:=True
rsT.MoveNext
Loop

rsT.Close
cn.Close

Set rsT = Nothing
Set cn = Nothing

End Sub

The last step is to let form open on start. We need to add one line of code in ThisWorkbook

UserForm1.Show

Adding line of code to show UI form on open excel event

I hope this post give clear idea how to merge 2 or more excel files and send output to printer.

Posted in vba development | Tagged | Leave a comment

how to create ftp function with vb.net

Posted on June 1, 2011 by admin

 

I just finished another vb.net project where I need to downloaded dozens of files with ftp. I used Dos for ftp files before now I decided instead of creating bat script to do ftp use FtpWebRequest from System.Web.

 The benefits are that I can handle errors better and all work done in the same program. I store ftp credentials and site url in application configuration file so if password or URL change I can easy modify it without changing an recompiling the program.  So I wrote following function. Function is recieving several argumets host (URL), local file, remote file , UserID and password. I hope my readers find it easy to use. I use Send Email function to handle error but you may write to application error log or use other techniques to handle exceptions. I may post C# version of this function later.

Public Sub FtpGetFile(ByVal host As String, ByVal localfile As String, ByVal remotefile As String, ByVal username As String, ByVal password As String)

        ‘ Get the object used to communicate with the server.

        Try

            ’1. Create a request: must be in ftp://hostname format,

            ‘   not just ftp.myhost.com

            Dim URI As String = host & remotefile

            Dim ftp As System.Net.FtpWebRequest = _

                CType(FtpWebRequest.Create(URI), FtpWebRequest)

            ’2. Set credentials

            ftp.Credentials = New  _

                System.Net.NetworkCredential(username, password)

            ’3. Settings and action

            ftp.KeepAlive = False

            ‘we want a binary transfer, not textual data

            ftp.UseBinary = True

            ‘Define the action required (in this case, download a file)

            ftp.Method = System.Net.WebRequestMethods.Ftp.DownloadFile

            ’4. If we were using a method that uploads data e.g. UploadFile

            ‘   we would open the ftp.GetRequestStream here an send the data

            ’5. Get the response to the Ftp request and the associated stream

            Using response As System.Net.FtpWebResponse = _

                  CType(ftp.GetResponse, System.Net.FtpWebResponse)

                Using responseStream As IO.Stream = response.GetResponseStream

                    ‘loop to read & write to file

                    Using fs As New IO.FileStream(localfile, IO.FileMode.Create)

                        Dim buffer(2047) As Byte

                        Dim read As Integer = 0

                        Do

                            read = responseStream.Read(buffer, 0, buffer.Length)

                            fs.Write(buffer, 0, read)

                        Loop Until read = 0 ‘see Note(1)

                        responseStream.Close()

                        fs.Flush()

                        fs.Close()

                    End Using

                    responseStream.Close()

                End Using

                response.Close()

            End Using

        Catch ex As Exception

            SendEmail(ex.Message, “ftp problem”)

            Console.WriteLine(ex.Message & remotefile)

        End Try

    End Sub

Posted in .Net development | Tagged | Leave a comment

upload excel file with php

Posted on May 28, 2011 by admin

Just couple days ago one of my clients ordered php script which would upload excel file for processing so I was breaking my head how can process the file using php. I did it many times in .NET using OLEDB. I found php class in sourceforge use this link to download it http://sourceforge.net/projects/phpexcelreader/ sample code is attached. Below is a sample code to iclude class file and process rows in the loop.

require_once ‘Excel/reader.php’;
     $data = new Spreadsheet_Excel_Reader();
  $data->setOutputEncoding(‘CP1251′);
  $data->read($_FILES["file"]["tmp_name"]);
  error_reporting(E_ALL ^ E_NOTICE);
  //echo “Reading file”;
 �
 �
  for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
 �
   $firstname =$data->sheets[0]['cells'][$i][1];
   $lastname =$data->sheets[0]['cells'][$i][2];
   $address =$data->sheets[0]['cells'][$i][3];
   $zip =$data->sheets[0]['cells'][$i][4];
   $phone =$data->sheets[0]['cells'][$i][5];
   $WorkPhone=$data->sheets[0]['cells'][$i][6];
   $email =$data->sheets[0]['cells'][$i][7];
   $err=0;
   $errord=”;
  }

Posted in php web development | Tagged | Leave a comment

Autocomplete text box with C# and Jquery

Posted on May 19, 2011 by admin

Me and my partner working on business directory project http://www.placenearme.com . One of the project requirements was implement auto complete to populate keywords like google search. So if user type “re” it would bring up all keywords starting with re

 We made autocomplete text box using Jquery autocomplete plugin and c# generic handler

Here are the steps to get it done.

1 Add jquery library in the head of aspx file then aouto complete plugin libaraty and autocomplete css file

2  write small Jquery code to call autocomplete function passing name of the generic handler file

<script>

$(document).ready(function() {

$(“#<%=txtSearch.VlientID%>”).autocomplete(‘search.ashx’);

});

</script>

3 Next step is to code search.ashx file

using System;

using System.Web;

using System.Data.SqlClient;

using System.Configuration;

using System.Text;

public class Search : IHttpHandler {

    public void ProcessRequest (HttpContext context) {

        string prefixText = context.Request.QueryString["q"];

        using (SqlConnection conn = new SqlConnection())

        {

            conn.ConnectionString = ConfigurationManager

                    .ConnectionStrings["affiliatesConnectionString"].ConnectionString;

            using (SqlCommand cmd = new SqlCommand())

            {

                cmd.CommandText = “select distinct CityStateZip from listings where ” +

                “CityStateZip like @SearchText + ‘%’”;

                cmd.Parameters.AddWithValue(“@SearchText”, prefixText);

                cmd.Connection = conn;

                StringBuilder sb = new StringBuilder();

                conn.Open();

                using (SqlDataReader sdr = cmd.ExecuteReader())

                {

                    while (sdr.Read())

                    {

                        sb.Append(sdr["CityStateZip"])

                            .Append(Environment.NewLine);

                    }

                }

                conn.Close();

                context.Response.Write(sb.ToString());

            }

        }

    }

    public bool IsReusable {

        get {

            return false;

        }

    }

}

Posted in .Net development, Uncategorized | Tagged , | Leave a comment

asp .net create excel file with couple lines of code

Posted on May 18, 2011 by admin

I got very simple asp.net project. The task was to display gridview with data below button to download data in excel file than file upload so user can upload updated data and store it in sql database. So I use several different methods to create excel file. Here is the best way to do it. You need to download excellibrary.dll copy (you can download it from here http://code.google.com/p/excellibrary/ ) it to project bin folder add reference to it. Below code snippet how to create new excel file and download.

Imports ExcelLibrary.SpreadSheet 

context.Response.Clear()

context.Response.Charset = “”

context.Response.AddHeader(“content-disposition”, “attachment;filename=Sample2.xls”)

context.Response.ContentType = “application/vnd.ms-excel”

Dim file As String = context.Server.MapPath(“”) & “/sample2.xls”

Dim workbook As New Workbook()

Dim worksheet As New Worksheet(“Sheet1″)

 worksheet.Cells(0, 1) = New Cell(“FullName”)

 worksheet.Cells(0, 2) = New Cell(“Title”)

 worksheet.Cells(0, 3) = New Cell(“Location”)

workbook.Worksheets.Add(worksheet)

workbook.Save(file)

context.Response.TransmitFile(“sample2.xls”)

Posted in .Net development | Tagged , | Leave a comment

vb.net unzip files made easy

Posted on April 20, 2011 by admin

Couple weeks ago I started new project data feed processing. I should download several files unzipped the files. I have found easy way to do it utilizing ICSharpCode.SharpZipLib.dll then I add reference to my project.

After that I added imports statement at beginning my vb.net module and wrote procedure to unzip files. It accept 2 argument path and name  of unzipped file and second path where to store files.

Public Sub ExtractArchive(ByVal zipFilename As String, ByVal ExtractDir As String)
        Try
            Dim Redo As Integer = 1
            Dim MyZipInputStream As ZipInputStream
            Dim MyFileStream As FileStream
            MyZipInputStream = New ZipInputStream(New FileStream(zipFilename, FileMode.Open, FileAccess.Read))
            Dim MyZipEntry As ZipEntry = MyZipInputStream.GetNextEntry
            Directory.CreateDirectory(ExtractDir)
            While Not MyZipEntry Is Nothing
                If (MyZipEntry.IsDirectory) Then
                    Directory.CreateDirectory(ExtractDir & “\” & MyZipEntry.Name)
                Else
                    If Not Directory.Exists(ExtractDir & “\” & _
                    Path.GetDirectoryName(MyZipEntry.Name)) Then
                        Directory.CreateDirectory(ExtractDir & “\” & _
                        Path.GetDirectoryName(MyZipEntry.Name))
                    End If
                    MyFileStream = New FileStream(ExtractDir & “\” & _
                      MyZipEntry.Name, FileMode.OpenOrCreate, FileAccess.Write)
                    Dim count As Integer
                    Dim buffer(4096) As Byte
                    count = MyZipInputStream.Read(buffer, 0, 4096)
                    While count > 0
                        MyFileStream.Write(buffer, 0, count)
                        count = MyZipInputStream.Read(buffer, 0, 4096)
                    End While
                    MyFileStream.Close()
                End If
                Try
                    MyZipEntry = MyZipInputStream.GetNextEntry
                Catch ex As Exception
                    MyZipEntry = Nothing
                End Try
            End While
            If Not (MyZipInputStream Is Nothing) Then MyZipInputStream.Close()
            If Not (MyFileStream Is Nothing) Then MyFileStream.Close()
        Catch ex As Exception
        End Try
    End Sub

Posted in .Net development | Tagged | Leave a comment

Another cool API City and State look up by IP Address done in PHP

Posted on April 18, 2011 by admin

I been asked to implement city and state lookup based on client browser IP. I searched the web and found cool free web service provided by ipinfodb.com  so I downloaded ipinfodb.class.php file from their web site http://ipinfodb.com then added snippet below to php form I developed. You can implement this service within couple minutes. You only need register and obtain API key in order to use it. 

include(‘ipinfodb.class.php’);
//Load the class
$ipinfodb = new ipinfodb;
$ipinfodb->setKey(‘your-api-key-here);
�
//Get errors and locations
$locations = $ipinfodb->getGeoLocation($_SERVER['REMOTE_ADDR']);
$errors = $ipinfodb->getError();
�
if (!empty($locations) && is_array($locations)) {
       foreach ($locations as $field => $val) {
                  if ($field==’RegionName’) {
                               $region=$val;
                  }
      }
}

Posted in php web development | Tagged , | Leave a comment

PHP snippet to get city and state using Yahoo API

Posted on April 18, 2011 by admin

One of my clients asked me to store City and State into database if user just enter zip code. I found free Yahoo web service  to accomplish this task. you don’t have to obtain api keys and service is unlimited. I found very easy to implement it. You can use in your php web development I will post asp.net snippet as soon as someone order it. Below is a snippet   and screenshot of the form I was working on.

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, “http://where.yahooapis.com/geocode?location=” . $zip );
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
$output = curl_exec($ch);
curl_close($ch);

$parser=simplexml_load_string($output);
$State=$parser->Result->statecode;
$City= $parser->Result->city;

 customer info form without city and state

Posted in php web development | Tagged , , | Leave a comment

Sending SMS from MS Access using google voice

Posted on April 18, 2011 by admin

Couple weeks ago I got call from my client who asked me to add function into his call managment application to send SMS with one click. His call agents used to add call into application then copy and paste call information into google voice and send SMS to tech on the field. So I found php code on internet http://sudocode.net/article/66/sending-a-google-voice-sms-using-php/ to access google api I copied files to my server and modified couple line to accespt arguments ( google signin email password phone number and finally SMS text. Below is a VBA code I place in my client Access application and screenshot of how to add Microsoft XML referense.

Private Sub Command0_Click()
myresponsestring = http_Resp(“http://esoftcoder.com/example.php?email=” & email & “&password=” & p & “&phone=” & phone & “&body=” & body)
End Sub
Public Function http_Resp(ByVal sReq As String) As String

    Dim byteData() As Byte
    Dim XMLHTTP As Object

    Set XMLHTTP = CreateObject(“MSXML2.XMLHTTP”)

    XMLHTTP.Open “GET”, sReq, False
    XMLHTTP.send
    byteData = XMLHTTP.ResponseBody

    Set XMLHTTP = Nothing

    http_Resp = StrConv(byteData, vbUnicode)

End Function

set referense to Microsoft XML dll

Posted in MS-Access-Developmen, Uncategorized | Tagged , | Leave a comment