Hi Tom
Essentially, what I need to do is the following:
- read a comma delimited file uploaded by user
- validate the values of the file
- mark each value of the file as valid or invalid
- save validated file to server and display results
- if user is happy with results, update to database with valid results
What I was hoping to do was to use the opentext method to read the comma delimited file as it appeared to be an simple and elegant way of reading a comma delimited file.
Having said that, I've also written a work around (here's a snippet)
Dim objFSO,objFile,strContents
Set objFSO = CreateObject("Scripting.FileSystemObject")
REM Read all contents of file into a string
Set objFile = objFSO.OpenTextFile(strUploadFile, 1)
strContents = objFile.ReadAll
objFile.Close
REM Replace all occurances of comma in file with tab character
strContents = Replace(strContents, ",", vbTab)
REM Write updated contents back to file
Set objFile = objFSO.OpenTextFile(strUploadFile, 2)
objFile.Write strContents
objFile.Close
REM Now open as excel file and read contents
Set objExcelBook = objExcelApp.Workbooks.open(strUploadFile, false, false)
REM how many columns are used in the current worksheet
Set currentWorkSheet = objExcelApp.ActiveWorkbook.Worksheets(1)
usedColumnsCount = objExcelSheet.UsedRange.Columns.Count
REM For simplicity only allow one used column of data.
If usedColumnsCount <> 1 Then
response.write "<br>Only one column of data allowed. Please correct and retry."
response.end
Else
REM how many rows are used in the current worksheet
usedRowsCount = currentWorkSheet.UsedRange.Rows.Count
REM What is the topmost row in the spreadsheet that has data in it
topMostRow = currentWorkSheet.UsedRange.Row
REM What is the leftmost column in the spreadsheet that has data in it.
leftMostCol = currentWorkSheet.UsedRange.Column
REM Read the cell data from the worksheet
Set cells = currentWorkSheet.Cells
REM Only look at columns that are in the "used" range. nb only one column allowed
curCol = leftMostCol
REM Loop through each row in the worksheet
For row = 0 to (usedRowsCount-1)
REM Only look at rows that are in the "used" range
curRow = topMostRow + row
REM Get the value/word that is in the cell
word = cells(curRow,curCol).Value
REM Validate value
If isValidValue(word, strErrMsg) = False Then
cells(curRow,curCol+1).Value = "FAIL"
Else
cells(curRow,curCol).Value = word
cells(curRow,curCol+1).Value = "PASS"
End if
Next
End if
REM We are done with the current worksheet, release the memory
Set currentWorkSheet = Nothing
REM Save the workbook and quit
objExcelApp.Workbooks(1).Save
objExcelApp.Workbooks(1).Close
objExcelApp.Quit
REM We are done with the Excel object, release it from memory
Set objExcelBook = Nothing
Set objExcelApp = Nothing
thanks Fletcher