Get data from Excel

kpassaur · Post by **kpassaur** » Fri Jul 31, 2009 2:35 pm

Yes, when I am in the spreadsheet I can use find with no issues. I can search for what I am looking for and other data as well. I can read all of the cells.

I have deleted columns and tried it with no sucess.

It has to be something with the sheet, but what? I tried exporting it to a text file and saw a non ASCII Character in the text file so I deleted that column, saved the file and tried again. Still with no luck.

gdyvig · Post by **gdyvig** » Fri Jul 31, 2009 2:45 pm

Try deleting all rows except the row containing the cell you are looking for and any rows it references.

Then further reduce your spreadsheet by deleting all unrelated columns.

If you are still seeing the problem there should at least be fewer variables to contend with.

Gale

Post by **Marcus Tettmar** » Fri Jul 31, 2009 3:33 pm

Received your file. The code you posted works fine against your file without any errors. I tried it with various data and it always returned the correct cell. I'm using Vista and Excel 2007.

gdyvig · Post by **gdyvig** » Fri Jul 31, 2009 3:43 pm

Since it is not the script or the file it must be site related.

Read this:

http://answers.yahoo.com/question/index ... 808AA57h9j

Gale

kpassaur · Post by **kpassaur** » Fri Jul 31, 2009 7:40 pm

Well I am offically at a loss.

It works for Marcus, so the script is fine and the file must be fine.

It works for me on other files, which tells me that it is the file not the script.

It does not work on any machine I tested it on. Vista and XP both with Office 2007.

Thanks for all of your help.

gdyvig · Post by **gdyvig** » Fri Jul 31, 2009 10:35 pm

All of your machines appear to have something in common that is different than the machine Marcus used because the script and the file was the same everywhere.

The link I provided says certain Excel dll's must exist and be properly registered depending on which Excel objects are needed. The dll for at least one of those objects is not properly registered on any of your machines but is on Marcus's. But which object and therefore which dll?

Gale

Bob Hansen · Post by **Bob Hansen** » Fri Jul 31, 2009 10:58 pm

I think the most common machine differences from those tested outside would be because of Security/Firewalls/Permissions/Excel Reference Libraries/Paths ...

kpassaur · Post by **kpassaur** » Sat Aug 01, 2009 1:03 am

It makes perfect sense, however, I am an administrator, and I sent the test file to Marcus, whatever security should be the same or I would think. He was able to use the command I was not.

Please keep in mind that I can run the script on another file so it is not the machine and the security of the file makes perfect sense. But then again I can access and read any cell (I have not tried to write a cell) It gets more puzzeling

gdyvig · Post by **gdyvig** » Sat Aug 01, 2009 1:32 am

Perhaps it is the machine if the particular data type of data contained in the cell range. What is different about the data in the files where the script works.

Here is a good link about the Find method:

http://www.microsoft.com/technet/script ... y0118.mspx

Gale

JRL · Post by **JRL** » Sat Aug 01, 2009 5:02 am

Kpassaur,

You mentioned that you did a saveas:

Kpassaur wrote: I thought maybe it was corrupt so I opened it, saved it with a different name etc with the same results.

Did you or can you do a copy of the data and then paste it into a completely different excel workbook and save that. This of course does not copy formulas, cell types, macros, etc. But if your file is just a data file it might be worth trying.

OR... have you tried collecting your data using the dbquery> function? It can quickly retrieve all the data from a record (row) in an Excel workbook. If you need to know the row number, you could create a "Row Number" column that contains sequential numbers that match the row numbers. The "Row Number" column information could then be part of the retrieved record.

Try something like this. Step through it in the editor and check the variables that are created.

Code: Select all

//You would set these variables as required
  Let>FileName=ExcelFile.xlsx
  Let>DateColumnName=Date
  Let>DateToFind=01/31/2009

//This will provide the entire record for every %DateToFind% in the %DateColumnName% column.
  Let>connStr1=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%Filename%;Extended Properties=Excel 8.0;
  DBConnect>connStr1,dbH1
  Let>SQL1=select * from [Sheet1$] where [%DateColumnName%] = #%DateToFind%#
  DBQuery>dbH1,SQL1,rsSheet1,nR,nF

You could also use DBQuery> to poll a cell in a similar fashion to the excellent original answer posted in this thread by ainterne. Again you need to create a "Row Number" column in your excel worksheet that contains sequential numbers that match the row numbers.

Code: Select all

//You would set these variables as required
  Let>FileName=C:\Book1.xls
  Let>TempFileName=%temp_Dir%TempExcelFile.xls

//Leave These varaibles alone
Let>CF_OverWrite=1
Let>OldCellData=
Let>NewCellData=

Label>Start
  //Retrieve the info from a copy of the file to prevent access violations
  CopyFile>%FileName%,%TempFileName%
  Let>connStr1=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%TempFileName%;Extended Properties=Excel 8.0;
  DBConnect>connStr1,dbH1
  //This will collect all columns from sheet 1, row number 5
  Let>SQL1=select * from [Sheet1$] where [Row Number] = 5
  DBQuery>dbH1,SQL1,rsSheet1,nR,nF
  DBClose>dbH1

//From row five lets poll column number 3
//Let the value of variable NewCellData hold the contents
//of the cell at row 5 column 3
Let>NewCellData=%rsSheet1_1_3%
//First pass Let OldCellData = NewCellData
If>OldCellData=
  Let>OldCellData=%NewCellData%
  Wait>5
  Goto>Start
ELSE
  //IF>%OldCellData%=%NewCellData% nothing has changed,  go back to Start
  //and check the cell again.
  IF>%OldCellData%=%NewCellData%
    Wait>5
    Goto>Start
  Else
  //IF>%OldCellData%<>%NewCellData% then the cell has changed.  Write the new
  //contents to a file,  reset OldCellData to the new contents,
  //and go back and check the cell again.
    WriteLn>%temp_dir%CellDataRecord.txt,wres,%NewCellData%
    Let>OldCellData=%NewCellData%
    Wait>5
    Goto>Start
  EndIF
EndIF

kpassaur · Post by **kpassaur** » Sat Aug 01, 2009 7:46 am

I did a save as, a fix corrupt file, exported data into a csv file and opened and saved the csv file all with no sucess.

I have tried the code on three different spreadsheets and it works on all of them just not the data in this sheet.

To top it off it only not works for me, it does for Marcus.

Post by **Marcus Tettmar** » Mon Aug 03, 2009 8:18 am

Keith,

Have you tried recording a Find operation in this spreadsheet? I.e. open the spreadsheet, go to macros, record macro, then manually do the Find, then stop recording and then go and look at the VBA produced by the macro recorder. Try and run it.

ainterne · Post by **ainterne** » Mon Aug 03, 2009 8:59 pm

I don't know if this helps any, but I ran the VB code for the find feature that Marcus shared. The first couple of time I also got an error.....Then I realized that while trying to step through the scrip I had had not completely finished stepping through the whole script a couple of times and had just stopped....

What this had done is leave the Excel spreadsheet open a few times and I could see this via taskmgr... Without shutting the old open versions the script didn't really work properly. So I closed them all and everything started working.

Just to make sure that I didn't have any open Excel sessions running I added an little Vb script that Marcus shared with me sometime ago.

VBSTART
Function ftps (pgm)
dim wmi, sQuery, processes, process
set wmi = getobject("winmgmts:")
sQuery = "select * from win32_process where name='" & pgm & "'"
set processes = wmi.execquery(sQuery)
for each process in processes
process.terminate
next
End Function
VBEND

VBRun>ftps,Excel.exe

This ensures that all my Excel sheets are closed down before I run the script......

Be careful though because as I have it above it closes ALL Excel spreadsheets regardless..

For me the find function works really well and is a useful addition.

I was just thinking that you might have some open copies and not be aware......if not, then please ignore...

Thanks...Phil.

kpassaur · Post by **kpassaur** » Fri Nov 20, 2009 4:07 pm

Okay,

I know what the difficulty is but don't know how to correct it.

When I use this

VBEval>FindCell("%xlsheetname%","%company%"),cell

And there is a match it returns the row and column and works flawlessly

otherwise it returns:
Microsoft VBScript runtime error :424
Object required: 'theCell'
Line 37, Column 0

Line 37 in my script is
FindCell = CStr(theCell.Row) & ":" & CStr(theCell.Column)

Does anyone know what needs to be changed so that it does not return this but something that will allow the script to continue to run and the return value set to something that can be read as not found such as 0:0

Post by **Marcus Tettmar** » Fri Nov 20, 2009 4:22 pm

Here's a modification to the function to avoid this:

Code: Select all

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

Get data from Excel

Find Worked

Make the problem as small as possible.

Excel install missing dll?

Excel Error

Combination of file and machine

Excel Security

Maybe the dll needed depends on the data

Save As

FindCell using VBScript and MS with Excel