Build a text file in VB at run-time

Posted on 2002-06-25
Last Modified: 2010-05-02
I want to build a text file that will be used by ftp to send files to individual companys’ own ftp sites.   I want to get the company information from fields in a Microsoft Access database.  One company may have one file or many files to be sent with one file listed per database record.   So I need to gather all the company information (Company site name, userID, password, etc) and also each file they are to be sent, and to build the text file.

This web site explains the question.  Thank you for your help!
Question by:psmithphil
  • 6
  • 5
  • 2
  • +4
LVL 44

Expert Comment

ID: 7108344
there are two ways to do this:  

1) the "OLD" way, using the Open#, Write# (or Print#) and Close# statements (look these up in the VB Help System)

2) the "NEW" way, making use of the FileSystemObject (part of the VB Scripting Runtime library)

either approach will build a TEXT file, which can then be sent to anywhere you desire.

getting the data from the database is completely independent of how you decide to generate the file.

Are you looking for help in creating the program, or are you looking for someone to write the program for you?

Arthur Wood

Author Comment

ID: 7108391
Thank you, Arthur, as you can see on the web page, I've been putting the program together.  No, I don't want a hand-out where someone just writes the program for me, but I've run into a snag.  The place I'm running into a snag is how, once I loop through all the records for a company, how do I gather all the filenames for that company into one array (or whatever) and add it to my text file, then add the other companies too?

I don't know if an array would work for this or how I'd apply it.  That's the snag I've run into.

I appreciate any guidance you can provide.
LVL 44

Expert Comment

ID: 7108467
why not create an array (of varaible dimension, so you can add to oit as needed), to hold the filenames as they are created:

Dim MyFile() as String

then as you need to add a new element to the array, use the ReDim Preserve statement to increase the size of the array by 1, (The PRESERVE keyword KEEPS all of the existing rows in the array, and simply adds a New empty row at the end)

look up the Redim statement in the VB Help System.  This allows you to have truly DYNAMIC arrays, which can grow as do NOT need to KNOW the size of the array, first.

that should point you in the right direction.


Expert Comment

ID: 7108478
You could write up a SQL query such that all the files that are to be sent to one particular company are grouped together, along with company information (Company site name, userID, password, etc) in the resultset.

Now, is that a requirement that the name of the file that you will be sending across should have DealerFTPSend.TXT name? Else you could include a counter and append that to the name of the file you are sending across.
i = 0
Do Until Rs.EOF
  If Rs.DealerFTP_ID <> "" Then
     i = i + 1
     Call createTheTextFile(Rs.DealerFTP_Site, Rs.DealerFTP_ID, Rs.DealerFTP_PW, Rs.DealerFTP_FDR, i)
  End If
Rs.MoveNext 'Move to next record in recordset.

Sub createTheTextFile(theSiteName, theUserID, thePassword, DealerFolder, iCount)
Dim strFileName
'Fso is the FileSystemObject which will be dim'd earlier:
Set strFileName = Fso.OpenTextFile("d:\cpdfiles\autolog\DealerFTPSend" & iCount & ".TXT", ForAppending, True)
End Sub
LVL 38

Expert Comment

ID: 7108552
Here I would write a query that retrieves company names that have the DealerFTP_ID field set.  Then I would loop through the query, using the company names to retrieve the individual records and write my file.

In DAO, I would probably set an index and use seek to retrieve all records which have a particular company name.  In ADO, I would probably create a new query/recordset for each company name as I am looping through.

To create a query that selects a company only once even if there are multiple records use the distinct keyword:

Select distinct companyname, DealerFTP_ID
Where DealerFTP_ID = True

Or whatever.  Then on the second query, you do not use the distinct keyword, but you filter by company name

Do while not rsAll.EOF
  strSQL = "Select * from blahblahblah Where companyname = '" & rsAll("Companyname") & "' And DealerFTP_ID = True"
  'build secondary rs and write file here.


Accepted Solution

gencross earned 500 total points
ID: 7108702
How about something like this...

Select all records from database where FTP_ID is not NULL or "" ordering them by the companyname.  Then loop through each record collecting the data into variables for the company info and an array for the file.  When the company name changes write the file out.  The code would look something like this...

Dim sarr(1 to 500) as string
Dim sCompany as string

'Open RS
'Example SQL statement

Do Until Rs.EOF
If Rs.CompanyName = sCompany Then
    iCnt = iCnt + 1
    sarr(iCnt) = Rs.FTPFile
    sCurrentCompany = Rs.CompanyName
    sFTPSite = Rs.DealerFTP_Site
    sFTPID = Rs.DealerFTP_ID
    sDealerFTPPW = Rs.DealerFTP_PW
    sDealerFTPFDR = Rs.DealerFTP_FDR
    'Set any other variables you need
    Redim preserve sarr(1 to iCnt)
    Call createTheTextFile (sFTPSite,sFTPID, sDealerFTPPW, sDealerFTPFDR)
    sCompany = Rs.Company
End If
Rs.MoveNext 'Move to next record in recordset.

There may be a few bugs in this code, I'm writing off the top of my head in the EE Textbox, but I think you get the idea.  This way you don't need multiple loops or lookups.  Only one.  When the company name changes you know you have all the info you need.  Write the record, clear the variables (not shown above), and start on the next company.

Expert Comment

ID: 7108705
FYI, I Dimmed the Array for 500, assuming this would be more files than any company would have.  You can dim it do whatever you like, or not dim it at all, just using the redim (minor code changes of course).

Author Comment

ID: 7108745
What a bunch of awesome, wonderful answers!

I am a little overwhelmed with these excellent answers and will have to study them tonight to (hopefully) write the code that will make it work.  I'm getting a lot of customer calls now and can't give it the proper concentration at the moment.

I will get back with you all asap.  Thank you all for your responses!
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.


Author Comment

ID: 7108822
Gencross, in your example, when all the records for one company have been gone through and "createTheTextFile" is called, would I do something similar to what I show below?  It seems I need to loop through the array and get all the filenames.  If so, what do I put after the dot (.) in ".WriteLine ("put " & sarr.)"?  Is the rest of my For-Each correct?  Or am I off-track?

Sub createTheTextFile(theSiteName, theUserID, thePassword, DealerFolder)
Dim strFileName
'Fso is the FileSystemObject which will be dim'd earlier:
Set strFileName = Fso.OpenTextFile("d:\cpdfiles\autolog\DealerFTPSend.TXT", ForAppending, True)
'The following then uses the WriteLine method to write lines of text to the
'created text file.

dim element

With strFileName
.WriteLine ("ftpproxy")
.WriteLine ("prompt")
.WriteLine ("quote site " & theSiteName)
.WriteLine ("user " & theUserID)
.WriteLine (thePassword)
.WriteLine ("cd " & DealerFolder)
.WriteLine ("lcd d:\cpdfiles\file\newup\")

For Each element in sarr
.WriteLine ("put " & sarr.)

.WriteLine ("")
End With

Expert Comment

ID: 7109885
U can use file system object in vb to create text file at runtime.

U want to give reference for the file system obiject in the vb project references.

Then u can used function in that references
LVL 43

Expert Comment

ID: 7109945


Advice for Experts on Providing Solutions Through Comments or Answers
Experts may post solutions to your question in the form of a comment or answer, so be sure to check
every email notification as soon as it arrives. Often, an Expert posts the solution to your problem
as a comment because it is early in the collaboration process and other issues may be involved. In addition,
it is common etiquette in some topics areas to always post comments and not answers. There are several
reasons behind this thinking:

Questions do not become prematurely locked and the collaboration process is not inhibited when the question
is indexed as open for proposed solutions.
The question-asker has the power to select the best solution rather than one that is simply suggested
as an answer.
Watch out for unclear or multiple-part questions. You can send a comment to the member requesting that
they rephrase or break up the question before you submit an answer.
If you don't know the answer, leave the question for someone else.
If you are unsure of your solution, post it as a comment rather than an answer. Members can accept comments
as solutions and award points for them.
You are only responsible for providing one reasonable answer.
You may review information about a member, including their grading history, by clicking on their member
If you're having a problem with a Member, you can refer it to community support

You are not new to this site so should be aware of the above and the guidelines that are also displayed at the bottom of every question. If you continue to propose answers which have no value then your account will be referred to customer services for possibly penalty.

psmithphil: please reject this proposed answer as soon as possible.

Expert Comment

ID: 7110220
You should replace your For Each with something like this...

For x = 1 to ubound(sarr)
    .WriteLine ("put " & sarr(x))

Author Comment

ID: 7110701
Ooops!  I just saw how to reject an answer. Thank you for your answer, srinivasanmr, but it didn't answer my question.  I had previously done what you suggested, I'm just having trouble with the "put" part of the code.

Author Comment

ID: 7110721
Thank you for your comments.  I didn't notice the comment was posted as an answer.

I am going to accept gencross's answer.  I still have some questions regarding this, but they probably should be included in another question post.  Stay tuned!

You've been very helpful and I am going to award you the points.  All the answers were great, but yours was the one I understood the best.  I'm having trouble at one place, the array is getting locked at:
ReDim Preserve sArr(iCnt)

You can see all the code at the web site below. I apologize that it's hard to read, but I couldn't get DreamWeaver 3 to indent properly.

Expert Comment

ID: 7110775
I have looked at the code and I think I found the problem...

Dim sArr(500)

Should be...

ReDim sArr(500)

I apologize (I said there might be a few bugs in it:)).  If you Dim an array you cannot ReDim it as something else, but you can use ReDim only and never Dim the array and you can then us ReDim Preserve.  Does that make sense?

FYI.  You are dimming your array as a Variant because you are not specifying a data type.  This is using more memory.  Since these are all strings, I assume since they are paths, you should declare as string.

ReDim sArr(500) as String

Expert Comment

ID: 7110804
I just realized as well you are not resetting the counter iCnt at the change of each company.  You will want to do this as well or you will have extra elements in the array.

One more thing...The counter is designed to work with a one based array.  When you use ReDim sArr(500) you are creating a zero based array meaning element zero is your first on as opposed to 1.  If you run your code the way it is now it will work fine, however, you will always have an element zero with nothing in it.  The easiest thing to do is to change redim to...

sArr(500) to sArr(1 to 500) each time you declare it in your code.

Author Comment

ID: 7112052
Thank you, gencross for the excellent tweaks to my program!  I will implement them and give them a test.  I am going to try to run this in Windows Scripting Host, so I must keep everything as a variant and can't use the "As String", but will keep that in mind for my VB6 projects.

I may have more questions regarding this program, but if I do, I will post a new question.

You have been an awesome help and I surely appreciate everything!!

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now