excel sheet as varible

[moremail]

I have a program that theoretically should select a chosen excel sheet from a workbook to import into a table.

I have no problem creating a table from which to choose the tab. The data looks exactly like this including the quotes;

'ABC Homes$'

After selected it is passed to a varible with this;

store customer to mcust

When I try to select the sheet with this;

.Application.Worksheets(mcust).Activate && Select sheet

OEL Error code 0x8002000b: Invalid index

Any ideas how to make this work? I found if I store a number value in the variable I receive no error however I have found no way to coorelate the sheet numbers with sheet names. I appreciate it. Thanks.

 

[Olaf Doschke]

Where and howdo you set customer?

What works for me is this:

o = CreateObject("Excel.Application")
lcSheetname = o.Worksheets(1).Name
? lcSheetname
o2 = o.Worksheets(lcSheetname)
? o2.Name

This code adresses the first sheets by the numeric index 1 and then adresses the same sheet via it's name.

As you get an invalid index error this points to the name being wrong. If the excel sheet really shows single quotes in the sheet caption, you must include these in the value of mcust, as in:

mcust = ['ABC Homes$']

Leading or trailing spaces may also make the real sheet name different from what you see displayed.

Bye, Olaf.

 

[moremail]

Since the workbook can change frequently what I did was import all the sheet names into a table. A browse window pops up showing the sheets available,

'this company$'
'that company$'
'any company$'

After user selects the sheet I tried to throw the that exact data into a variable which should to that sheet and import data from the sheet. As I said, if I were to store a sheet number as numeric to the variable the selection works with
.Application.Worksheets[tabno].Activate && Select sheet

but I havent been able to relate the names to the sheet because the company table is alpha order thus no tab numbers are available. I tried
.Application.Worksheets[mcust].Activate
and received the same error. Thanks again.

 

[Olaf Doschke]

OK, let me ask straight and simple question:
1. Do you see the quotes in Excel, too?
2. what CODE do you use to set the variable customer. You show, that you set mcust to customer (store customer to mcust), but how do you set the variable named customer? Or is customer a field name? Is it perhaps a char field? Then you perhaps need alltrim or rtrim.

Bye, Olaf.

 

[moremail]

No, these are the sheet name.

lcXLS = GetFile('XLS')
lcConn = "Driver={Microsoft Excel Driver (*.xls)};"+;
"DriverId=790;Dbq="+m.lcXLS+;
";DefaultDir="+JustPath(m.lcXLS)+";"
lnHandle = SQLStringConnect(m.lcConn)
SQLTables(m.lnHandle,"", "SheetNames")

Scan
lcTableName = Trim(SheetNames.Table_Name)
lcOutput = Chrtran(m.lcTableName,' $','_')
SQLExec(m.lnHandle,'select * from ('+m.lcTableName+')',m.lcOutput )
endscan
SQLDisconnect(m.lnHandle)copy fields table_name to seedname

From Seedname comes the sheet name from there to variable.

Make sense?

 

[Olaf Doschke]

? Len(customer), Len(Trim(customer))

Bye, Olaf.

 

[JRB-Bldr]

I use code similar to the following all the time to get to the specific worksheet within a workbook

  cTargetWkSht = "ThisSheet"
  oExcel.Sheets(cTargetWkSht).ACTIVATE

Note - the best way to find what your VFP Automation code needs to be and what it needs to do is to do the operation totally without VFP, manually in Excel while recording the operations as a Macro. Then when done, examine the Macro VBA code.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

create cursor curnames (name C(10))
insert into curnames values ('Olaf')
? 'Olaf'
? len('Olaf')
? name
? len(name)
? occurs(space(1),name)

See?

Your "copy fields table_name to seedname"

creates a field named "table_name", the type should be char, iut has some length. All values inserted into char fields are padded with spaces. So you got trailing spaces, and Excel doesn't find a sheet with a name containing trailing spaces, unless the excel sheet name really has trailing spaces.

Ok?

It's the same problem as with the simple sample cursor given, I put 'Olaf' to it, but stored and read back is 'Olaf ', as the field is c(10).

Bye, Olaf.

 

[Mike Lewis]

MoreMail,

Is the dollar sign actually part of the sheet name (as seen if you open the workbook in Excel)?

The reason I ask is that certain software might append a dollar sign to a sheet name when it shouldn't really be there. (I'm thinking of the Excel ODBC driver, although I'm not completely sure about that.)

You might try removing the dollar sign to see if that makes any difference.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[moremail]

Yep, it appears the $ AND the trim both are the problem. Mike, the dollar sign appears nowhere except in the table seedname. I think you are right about the driver. All I have to do is remove that and trim it and I think it will work. Thanks Olaf and Mike.

 

[Olaf Doschke]

Ok, and this also explains why there is are two lines:
lcTableName = Trim(SheetNames.Table_Name)
lcOutput = Chrtran(m.lcTableName,' $','_')

The trim removes trailing spaces and the chrtran makes it a valid name for a foxpro cursor, removing $ and turning the remaining spaces inside the name to underscores.

But these lines do not change the values of the field table_name. So you need to repeat the removal of $ and the trimming. The turning of spaces to underscores of course not, as sheets are able to contain spaces in their names.

But in the end, you already had that code ammending the sheet names at hand.

Bye, Olaf.

 

[Olaf Doschke]

I just did try one more thing:

Open excel, renamed a sheet to "Table 1 " including the last trailing space.

Your code then reports the sheet name as 'Tabelle 1 $', including the single quotes, which really don't belong to the name.

For the non touched sheet the SheetName cursor does not contain the sinle quotes. So it's really a bit more complicated to get the real names from that data.

And one further experiment: Adding single quotes in the sheet name (T'able 1'), your code or the SheetName table doesn't reflect these single quotes. So it's quite a bit more complicated. Worksheet(1).Name 1:1 return what you see. Adressing Worksheet("T'able 1'") does return the error invalid index again. So you better don't have names really including single quotes in the excel sheet.

This is just a bit problematic. You might rather loop from 1 to the Worksheet.Count and see if the Name you get meets the chosen name, if you remove spaces, $ and single quotes from both names, that might be more stable.

Bye, Olaf.

 

[moremail]

Good points. What looked simple on the outset was not as much. Thanks again.

 

[moremail]

I almost forgot! One last thing in my code"

oExcel.Workbooks.CLOSE
oExcel.Quit()
release oExcel
release all like o*

I've tried all three but I can never seem to release this workbook from processes. Any futher attempt to access reports file in use. Is there a 5th command I don't know about?

 

[Olaf Doschke]

Actually I wouldn't release all like o*, you may release much more than intended.

Windows always would need some time to do a garbage collection and get rid of all handles on the file, if you talk about the XLS file. Perhaps the ODBC driver still has it's hands on.

Try to DOEVENTS FORCE after quitting excel, that at least gives windows a chance to do garbage collection.

Bye, Olaf.

 

[Mike Lewis]

In other Office applications, you only need to call the Quit method. For example, if you were automating Word:

oWord.Quit

But Excel seems to be different. It's seems you have to release the variable in order to close Excel:

RELEASE oExcel

At least,that's what I've always found.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Mike,

moremail does Release oExcel, so that can't be the problem.

I think it's simply garbage collection being slow and lazy.

@moiremail: Did you ever start the the Firefox Browser, right after you closed it? The new Firefox instance then reports, Firefox is laready running. It's about the same problem as with file handles of Excel documents, I think.

If the problem persists long after your autoamtion ended, and users are not able to open the XLS doc, it would be worth saving as a new file name, perhaps.

Bye, Olaf.

 

[JRB-Bldr]

I notice that you are doing

oExcel.Quit()

I have never seen the Quit followed by the parenthesis.

While there may be some over-kill and/or redundancy in it, I always do the following:

   * --- Quit Excel ---
   oExcel.QUIT
   oExcel = .NULL.
   RELEASE oExcel

Good Luck,
JRB-Bldr

 

[moremail]

Finally the only combination I found that worked is

oExcel.Quit
RELEASE oExcel

After browsing around here it appears what works for one, won't work for another. Go figure?

 

[Olaf Doschke]

oExcel.Quit and Release oExcel are not alternatively doing te same thing, it's natural to do both. The first exits excel, but still keeps the reference, releasing the variable let's windows also release the excel object from memory.

It's correct and also the order is correct, of course.

@JRB-Bldr: What is so unusal about adding () at the end of a method call. It doesn't add overkill or redundancy. It's a good way to denoting Quit is a method and not a property.

Bye, Olaf.