Perry Choy


I want to rename worksheet but haven't installed Excel




Re: How to rename worksheet without installed Excel?

Andy Pope


Hi,

You will require some software in order to open and rename the worksheet. I doubt the excel viewer will allow you to alter the file in anyway.

Why the need to change a sheetname on something you can not use






Re: How to rename worksheet without installed Excel?

Perry Choy

Because the worksheet name more complex, i am try to using ADO to connect it encounted errors, i want to rename the sheet name to fit this problems, have you any suggestion for me. Thanks!

For Example

AB CD EFD $0+ (bb d_ddd)






Re: How to rename worksheet without installed Excel?

Andy Pope

The following code worked for me assuming AB CD EFD $0+ (bb d_ddd) was the sheet name.

Public Sub QueryWorksheet()

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String

' Create the connection string.
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\temp\testado.xls;" & _
"Extended Properties=Excel 8.0;"

' Query based on the worksheet name.
szSQL = "SELECT * FROM ['AB CD EFD $0+ (bb d_ddd)$']"

Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not rsData.EOF Then
Sheet1.Range("A1").CopyFromRecordset rsData
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
rsData.Close
Set rsData = Nothing

End Sub






Re: How to rename worksheet without installed Excel?

Perry Choy

Thanks! I want to pass the range to query cannot work.

' Query based on the worksheet name.
szSQL = "SELECT * FROM ['AB CD EFD $0+ (bb d_ddd)$A9:G11']"





Re: How to rename worksheet without installed Excel?

Andy Pope

Sorry, I'm out of ideas.

Unless somebody knows of a solution to constructing the query you are left with getting the sheet name changed at source or reading all data and using code to deal with the extra records.