Automation Project #1

[MiskaTorn]

First let me start by introducing myself and explaining a bit about what I am doing here.

My name is Scott and I am mainly a PHP/MySQL programmer, but recently I began working for a company that does lead genertation and such like that over the internet. Before I came they had been doing everything manually, and my task at hand is to completely automate their backend processing.

A week ago I began hitting the books and trying to learn VBA coding so that I could utilize Office and Excel to do most of the work and I am very impressed with the functionality they both offer.

I don't want you guys to feel like I am using you and just taking off with the code to save me time and money, this is not the case. I am trying to learn VBA much better for my personal use as well, and everything I post here I assure you I have spent hours myself contemplating and attempting to come up with solutions myself.

Now let's get down to the dirty work.

I have a script that functions great for my needs and have no need to improve upon it for my work, but I see a lot of things it would be nice if it could do and would also be very useful to me to learn these features myself.

Currently I recieve a CSV file that looks like this.

180_2004-3-1_2004-3-30.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1		DateTime	IPAddress	Source	AffiliateID	Campaign	Transactiontype	Name	Address	Address2	City	State	ZipCode	Country	Email	EmailLocation	EmailAuthorization	PermissionField1	PermissionField2	PermissionField3	PermissionField4	PermissionField5	PermissionField6	PermissionField7	PermissionField8	PermissionField9	PermissionField10	GatekeeperUsername	GatekeeperPassword	ResponceCode	Term	Value	NumberofInstallments	AutoRenews	PaymentType	CreditCardNumber	CreditCardExpire	ProductCode	MatchCodeorSERVAccountNumb	SystemuseOnly	AgeRange
2		########	68.161.224.124	http%3A%2F%2Finnovationads.directtrack.com%2Fprogram_signup%2Fpopup.html%3Fprogram_id%3D180%26banner_id%3D0%26affiliate_id%3D1	CD1	CDS	1	TestName	1234TestingDr.		NewYork	NY	10005	USA	test@testing.com	0	Y	Y												I49K111	10	24.94		N	6
180_2004-3-1_2004-3-30

Now this file must be processed and sent in a text delimited document which must be named with an FTP extension. And given a custom header with the number of leads in it.

So the end result would look like this in an .FTP file.

3
1Test Name 1234 Testing Dr. New York NY10005 USA test@testing.com 0YY I49K111 010024.94 N6
1Test Name 1234 Testing Dr. New York NY10005 USA test@testing.com 0YY I49K111 010024.94 N6
1Test Name 1234 Testing Dr. New York NY10005 USA test@testing.com 0YY I49K111 010024.94 N6

Now I have a script usig the Microsoft fix to export the long strings to a text delimited format, and I have altered it with the help of "jmiskey" to make the columns automatically get set to their appropriate width, and export to the correct file format.

The code for that is here:

   Sub ExportText()
   
   Dim delimiter As String
   Dim quotes As Integer
   Dim Returned As String
   
    Columns("A").ColumnWidth = 1
    Columns("B").ColumnWidth = 24
    Columns("C").ColumnWidth = 24
    Columns("D").ColumnWidth = 24
    Columns("E").ColumnWidth = 13
    Columns("F").ColumnWidth = 2
    Columns("G").ColumnWidth = 6
    Columns("H").ColumnWidth = 50
    Columns("I").ColumnWidth = 50
    Columns("J").ColumnWidth = 1
    Columns("K").ColumnWidth = 1
    Columns("L").ColumnWidth = 1
    Columns("M").ColumnWidth = 1
    Columns("N").ColumnWidth = 1
    Columns("O").ColumnWidth = 1
    Columns("P").ColumnWidth = 1
    Columns("Q").ColumnWidth = 1
    Columns("R").ColumnWidth = 1
    Columns("S").ColumnWidth = 1
    Columns("T").ColumnWidth = 1
    Columns("U").ColumnWidth = 1
    Columns("V").ColumnWidth = 10
    Columns("W").ColumnWidth = 10
    Columns("X").ColumnWidth = 9
    Columns("Y").ColumnWidth = 3
    Columns("Z").ColumnWidth = 6
    Columns("AA").ColumnWidth = 1
    Columns("AB").ColumnWidth = 1
    Columns("AC").ColumnWidth = 1
    Columns("AD").ColumnWidth = 17
    Columns("AE").ColumnWidth = 4
    Columns("AF").ColumnWidth = 1
    Columns("AG").ColumnWidth = 21
    Columns("AH").ColumnWidth = 17
    Columns("AI").ColumnWidth = 2

    
     
    
      delimiter = ""
   
      quotes = MsgBox("Surround Cell Information with Quotes?", vbYesNo)
   
      Returned = WriteFile(delimiter, quotes)
   
      Select Case Returned
         Case "Canceled"
            MsgBox "The export operation was canceled."
         Case "Exported"
            MsgBox "The information was exported."
      End Select
   
   End Sub
   
Function WriteFile(delimiter As String, quotes As Integer) As String
   

Dim CurFile As String
Dim SaveFileName
Dim CellText As String
Dim RowNum As Integer
Dim ColNum As Integer
Dim FNum As Integer
Dim TotalCols As Double
   
   If Left(Application.OperatingSystem, 3) = "Win" Then
      SaveFileName = Application.GetSaveAsFilename(CurFile, _
      "Text Delimited (*.txt), *.txt", , "Text Delimited Exporter")
   Else
       SaveFileName = Application.GetSaveAsFilename(CurFile, _
      "TEXT", , "Text Delimited Exporter")
   End If
   
      If SaveFileName = False Then
         WriteFile = "Canceled"
         Exit Function
      End If
      FNum = FreeFile()
   
      Open SaveFileName For Output As #FNum
   
      TotalRows = Selection.Rows.Count
      TotalCols = Selection.Columns.Count
   
      For RowNum = 1 To TotalRows
         For ColNum = 1 To TotalCols
            With Selection.Cells(RowNum, ColNum)
            Dim ColWidth As Integer
            ColWidth = Application.RoundUp(.ColumnWidth, 0)
            Select Case .HorizontalAlignment
               Case xlRight
                  CellText = Space(ColWidth - Len(.Text)) & .Text
               Case xlCenter
                  CellText = Space((ColWidth - Len(.Text)) / 2) & .Text & _
                             Space((ColWidth - Len(.Text)) / 2)
               Case Else
                  CellText = .Text & Space(ColWidth - Len(.Text))
            End Select
            End With
            Select Case quotes
               Case vbYes
                  CellText = Chr(34) & CellText & Chr(34) & delimiter
               Case vbNo
                  CellText = CellText & delimiter
            End Select
            Print #FNum, CellText;
   
            Application.StatusBar = Format((((RowNum - 1) * TotalCols) _
               + ColNum) / (TotalRows * TotalCols), "0%") & " Completed."
   
         Next ColNum
         If RowNum<> TotalRows Then Print #FNum, ""
      Next RowNum
   
      Close #FNum
   
      Application.StatusBar = False
      WriteFile = "Exported"
   End Function

Now you can probably tell a few problems. The header must be deleted from the CSV and a few columns must be deleted from the document.

So this is where the next part of the project begins. I will try to keep you all updated with anything I figure out, and if anyone else would like to contribue please feel free to.

Alternately, on the backend this process once processed is thrown into another peice of software named Robo-FTP which renames the file specific to the date, puts it in a zip named date specific, and automatically uploaded at 5 pm every day.

Another script I would be willing to share if anyone here uses Robo-FTP

THanks,
Scott