Output flat file from VBScript 3

[webwriter]

Hello.
I have written an ASP that collects data that needs to be output to a flat file. This flat file will then be retrieved by an SAP program that will process the data to our database.. My question is this.. How do you output data collected on an ASP to a flat file. (this file can reside on the local drive of machine). Please advise..

Many thanks in advance..

webwriter

 

[Armstrong]

Can the flat file be a text file? If so perhaps you could use the .createTextFile of FSO and perform a .writeline on it.

 

[link9]

You need to use the fileSystemObject --

Here is some good reading on the subject --

http://www.devguru.com/Technologies/vbscript/quickref/filesystemobject.html

If you hit specific roadblocks, post back, and someone here will help hash them out.


Paul Prewett

 

[webwriter]

Thanks guys. I printed off the white paper on the FSO.. That should help me get started.. Yes, the file can be a txt file..

I am going to try this afternoon and see if i can create the file..

May i write you back if i have trouble..

Many thanks..

 

[link9]

Keep in mind, however, that just because you are using the .createTextFile method, you can save it to be whatever file extension you want -- .prn, .dat, whatever -

Set datFile = fso.CreateTextFile("c:\somefile.dat", True)

 

[webwriter]

Thanks Link9. i'll definitely use that too... I may post further questions this afternoon.. Hope I don't get too lost in this.. thanks again for your help..

Much appreciated..

 

[webwriter]

Thanks Link9... I was able to create my flat file very successfully... Your notes were good.. thanks again..

I appreciate it very much..

Webwriter

 

[link9]

 

[webwriter]

Hi Link9..
Could I bother you for a question in VB/ASP. I am trying to create/update an EXCEL spreadsheet from a VB Class module that is called from an ASP... Could you spare a few minutes to help me out??

Many thanks...

webwriter..

 

[link9]

Don't have alot of experience with this specifically, but I do have alot of books and the Internet. ;-)

Whatcha got?

 

[webwriter]

Hi..
BTW.. can i call you by a name rather than link9??

I have an ASP page that allows the user to enter up to 5 lines of data entry that will be validated and entered into an EXCEL spreadsheet. From my ASP, i call a function in my VB class module.. On the first pass thru my line items (on the ASP page), i have no trouble creating the EXCEL spreadsheet and loading the first row. On the next pass thru my line items, i increment my row counter and call the VB function again.. I don't know how to opent the same spreadsheet without EXCEL trying to re-create it again.. In other words, it always tries to create a new spreadsheet rather than open the same one again.. Do you know how I can make my vb code open this workbook rather an create it every time i pass thru my data entry screen? I can send you the code if this question is too complex and cumbersome...

Thanks for all your help and time.. I really do appreciate it.. You were so helpful before..

Thanks again...

webwriter

 

[link9]

Well, I always post my name on every thread **refers to first post**

You are welcome to call me whatever you like (just try to be nice). ;-)

It's tough from what you've posted to answer unequivocally, but here's a thought:

Why don't you count the lines of entry before you call the vb function, so that when you do call it, you only open the spreadsheet one time, write however many lines should be written, and then close it... This may require some modification of the vb module, itself, but I get the idea that you wrote it in the first place, so that shouldn't be a terrible problem.

The idea would be to have three different commands -- a create command, a writeLine command, and a close command -- all three of which would be called, but the writeLine command would be called n times -- depending on how many lines were entered.

I'm assuming here that a new Excel file must be created for each user -- If that's not the case, then you should have a pre-made Excel file on the server that you 'open' each time, and append to, rather than creating a new one each time.

If these ideas weren't terribly helpful (wouldn't surprise me), then try to post some of the more pertinent pieces of code from your module along with the calls that you use to call them, and we'll see if we can get further.


paul

 

[webwriter]

Hi Paul.. (see, that was nice.. ;-) )
You are exactly right that I want to open a pre-existing file in EXCEL and append the rows to it. I am able to increment my row counter so that i don't overlay my data.. That is no problem.. My problem is kinda goofy... but, i don't know the vb command to "open" the workbook and "append" to it rather than the standard " .Workbooks.add" and " .ActiveWorkbook.Close SaveChanges:=True, FileName:="\\testexcel.xls"

I suppose that is where i need the greatest help..

I'm going to include my function code.. Rip it away..

==========================================================
Public Function BuildExcel(ByVal strCustNum As String, ByVal strSalesRep As String, _
ByVal strVkorg As String, ByVal strVtweg As String, ByVal strSpart As String, _
ByVal dtBegDate As String, ByVal dtEndDate As String, ByVal wrkprice As String, _
ByVal strMatPrGrp As String, ByVal CommPrGrp As String, ByVal wrkByUnit As String, _
ByVal strCurrency As String, ByVal strPer As String, ByVal Pricel As String, _
ByVal wrkRange As String) As Variant

Dim xlApp As Excel.Application


Set xlApp = New Excel.Application


With xlApp
.Workbooks.Add

'enter data into specific cells
.Range("A" + wrkRange).Value = strCustNum
.Range("B" + wrkRange).Value = strSalesRep
.Range("C" + wrkRange).Value = strVkorg
.Range("D" + wrkRange).Value = strVtweg
.Range("E" + wrkRange).Value = strSpart
.Range("F" + wrkRange).Value = dtBegDate
.Range("G" + wrkRange).Value = dtEndDate
.Range("H" + wrkRange).Value = wrkprice
.Range("I" + wrkRange).Value = strMatPrGrp
.Range("J" + wrkRange).Value = CommPrGrp
.Range("K" + wrkRange).Value = wrkByUnit
.Range("L" + wrkRange).Value = strCurrency
.Range("M" + wrkRange).Value = strPer
.Range("N" + wrkRange).Value = Pricel

.ActiveWorkbook.Close SaveChanges:=True, FileName:="testexcel.xls"

.Quit

End With

Set xlApp = Nothing

BuildExcel = True

End Function
===========================================================

anymore thoughts?? I *really* appreciate your help.. Let me know if this gets to be a nuisance..

Lots and lots of thanks...
Celeste..

 

[link9]

sure Celeste -- see if this might shed some light --

Dim xlApp As Excel.Application
dim thePath as string

thePath = "d:\pathName\fileName.xls"

Set xlApp = getObject(thePath, "Excel.Workbook&quot.ActiveSheet

'THIS WILL OPEN UP THE EXCEL WORKBOOK -- AND GET YOU TO THE ACTIVE SHEET -- WHICH WOULD BE THE SHEET THE WORKBOOK WAS ON THE LAST TIME IT WAS CLOSED --

You'll then need to have some sort of system implemented in the workbook so that you will be able to determine what line was written to last --

This could be accomplished a couple of ways -- you could have a special cell that you know the location of that would hold a number that would denote the next blank row, which you would update each time the book was written to --

Or, you could have a certain column that you know will be written to each and every time the book is opened, and so if you find a blank cell on that column, you can assume that the entire row is blank, and it is safe to write to that row --

Conversely, maybe you want a new sheet to be added for each user that makes input to the sheet -- if that's the case, then instead of:

With xlApp
.Workbooks.Add

You would most likely want to say:

With xlApp
.sheets.Add

Or some such so that each time it's opened and written to, a new sheet is added. I'm not sure on the allowable number of sheets in an Excel workbook, though, so you'd have to decide if this would be a viable option for you.

I think that the rest of your methods should work ok -- with just the slightest of mods to account for which option you go with --

Does this help any?

lemme know -
paul

 

[webwriter]

Hi Paul..
That is perfect.. That is exactly what i am looking for.. I'm going to try and implement that code and see if i can get that to work.. I'm sure with the help you've given me, i will not have a problem now..

Once again.. you are my hero of the day.. This is twice you have saved me... Many many thanks..

Do you check this website daily? My questions means this: If i needed to ask you some questions down the road, is there a way to contact you without creating a new thread here??

Thanks again...
Celeste... :-D

 

[link9]

Well, I don't throw my email around here, but as anyone who is a member of the forums I frequent (this being one of them) can tell you, I'm addicted to this site.

The information I have learned from here, and continue to learn every day I visit, keep me coming back for more. Just post here, and I guarantee that I'll see it, and if I can help, I'll answer.

If I can't help, then someone else surely will.

And don't forget the ASP forum. That's my other favorite, and that one gets more traffic, so more people are likely to see your question.

Glad I could help!