Get data from Excel

[kpassaur]

It is now generating a different error when there is no match

Microsoft VBScript runtime error :1004
Unkown runtime error
Line 24, Column 0

But it continues to run correctly if a match is found.

Marcus this would also explain why when a while back you helped me and it would work on your machine and not on mine as the date format would have been different.

[Marcus Tettmar]

Not sure what this has to do with date formats. If you're getting an error after adding the code I posted then it must be coming from some other section of code.

You could just put "On Error Resume Next" at the top of your VBScript block to suppress any errors, but if it were me I'd want to know where the error occurs and why and fix the code so that it isn't thrown. If you want to post your full code I may be able to see the problem.

[kpassaur]

I mentioned the date function as it would always not exist and give me that error.

However, I stripped out the bulk of the code and left this portion. If I enter an existing value contained in the spreadsheet it finds it and works very well. However if it does not exist it fails with a VBScript Error:

Unkown runtime error Line 24 Column 0

Code: Select all

VBSTART
Dim xlApp
Dim xlBook
Dim Sheet
'Opens the Excel file in Excel
Sub OpenExcelFile(filename)
Set xlApp = CreateObject("Excel.Application")
xlApp.visible = false
Set xlBook = xlApp.Workbooks.open(filename)
end sub
'Use this to close Excel later
Sub CloseExcel
xlApp.quit
Set xlApp = Nothing
End Sub
'Retrieves a cell value from the specified
'worksheet
Function GetCell(Sheet,Row,Column)
Dim xlSheet
Set xlSheet = xlBook.Worksheets(Sheet)
GetCell = xlSheet.Cells(Row, Column).Value
End Function

Function FindCell(Sheet,Data)
  Dim theCell
  Dim xlValues
  xlValues = -4163

  Dim xlSheet
  Set xlSheet = xlBook.Worksheets(Sheet)
  xlSheet.Range("A1").Select
  Set theCell = xlSheet.Cells.Find(Data, xlApp.ActiveCell, xlValues)
  if Not theCell is Nothing then
    FindCell = CStr(theCell.Row) & ":" & CStr(theCell.Column)
  else
    FindCell = ""
  End if
End Function
VBEND


Input>company,Type Company Name

Let>database=Excel
Let>datapath=C:\temp
Let>datafile=test.xls
Let>xlsheetname=test
//Let>company=Digital Medics
Let>companyemailfieldnumb=2


If>%database%=Excel
VBRun>OpenExcelFile,%datapath%\%datafile%
Endif


IF>%database%=Excel

//VBRun>OpenExcelFile,%datapath%\%datafile%
VBEval>FindCell("%xlsheetname%","%company%"),cell
Separate>%cell%,:,cell_coords
Let>r=cell_coords_1
VBEval>GetCell("%xlsheetname%",%r%,%companyemailfieldnumb%),emailadd
//VBRun>CloseExcel
MDL>%emailadd%
DateStamp>c:\temp\output\logfile.txt,     wholedoc%c%.pdf is being sent to %emailadd% at %company%
Endif

[Marcus Tettmar]

Er, well. I can see why. So I modified FindCell so that it didn't error if there was nothing found and instead returned an empty string. So that's sorted. But in your code you then continue to attempt to do GetCell regardless of whether FindCell returned anything or not. If it found nothing it returns an empty string. So your GetCell is going to fail because you're not passing a valid column and row to it. You should modify your code to:

Code: Select all

VBEval>FindCell("%xlsheetname%","%company%"),cell
If>cell<>{""}
  Separate>%cell%,:,cell_coords
  Let>r=cell_coords_1
  VBEval>GetCell("%xlsheetname%",%r%,%companyemailfieldnumb%),emailadd
  //VBRun>CloseExcel
  MDL>%emailadd%
  DateStamp>c:\temp\output\logfile.txt,     wholedoc%c%.pdf is being sent to %emailadd% at %company%
Endif

If you look at my mods to FindCell you'll see that I made it return an empty string if no cell was found. This avoided the error. If you later attempt to pass a non-existent row/column or invalid value into GetCell you'll get another error. You only want to do the GetCell if FindCell found something.