Solved

Build a text file in VB at run-time

Posted on 2002-06-25
17
199 Views
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!
http://www.geocities.com/psmithphil/scripting/makingftptextfile.html
0
Comment
Question by:psmithphil
  • 6
  • 5
  • 2
  • +4
17 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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
0
 

Author Comment

by:psmithphil
Comment Utility
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.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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 needed...you do NOT need to KNOW the size of the array, first.

that should point you in the right direction.

Arthur
0
 
LVL 5

Expert Comment

by:rpai
Comment Utility
psmithphil,
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.
Loop

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
0
 
LVL 38

Expert Comment

by:PaulHews
Comment Utility
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.

Loop
0
 
LVL 4

Accepted Solution

by:
gencross earned 500 total points
Comment Utility
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
'SELECT * FROM myTable WHERE FTP_ID IS NOT NULL AND FTP_ID <> '' ORDER BY CompanyName

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
else
    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.
Loop

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.
0
 
LVL 4

Expert Comment

by:gencross
Comment Utility
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).
0
 

Author Comment

by:psmithphil
Comment Utility
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!
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:psmithphil
Comment Utility
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.)
Next

.WriteLine ("")
End With
0
 

Expert Comment

by:srinivasanmr
Comment Utility
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
0
 
LVL 43

Expert Comment

by:TimCottee
Comment Utility
srinivasanmr:

EXPERTS-EXCHANGE PROTOCOL FOR COMMENT VS. ANSWER

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
name.
If you're having a problem with a Member, you can refer it to community support http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt.
 

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.
0
 
LVL 4

Expert Comment

by:gencross
Comment Utility
You should replace your For Each with something like this...

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

Author Comment

by:psmithphil
Comment Utility
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.
0
 

Author Comment

by:psmithphil
Comment Utility
TimCoffee:
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!

gencross:
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:
Else
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.
http://www.geocities.com/psmithphil/scripting/makingftptextfile.html
0
 
LVL 4

Expert Comment

by:gencross
Comment Utility
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
0
 
LVL 4

Expert Comment

by:gencross
Comment Utility
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.
0
 

Author Comment

by:psmithphil
Comment Utility
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!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 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

13 Experts available now in Live!

Get 1:1 Help Now