ADO deleting/creating Tables in Excel

[bartekR]

I have macro that runs a little check and extracts data which is then transferred to another excel file (Price check.xls).

Before the transfer happens I wanted it to clear all the records from the previous run.
Initially I tried "DELETE... WHERE.. Price_date<> date" syntax but it seems like it doesn't work in excel as it does in Access and you can't delete the whole rows.

Instead i decided to delete and recreate the table where my extracted records would be appended.

The problem is that when i delete the old table with say 50 records, the new table will appear starting from the row 51 which is a bit confusing for the end user.

Can anyone think of a way of making the new data set to appear starting from the first cell?


thank you

here's my code (should work on any pc with excel 2003 after replacing a path in sDataDropFile = )

'----------------------------------------------------------
Public conExcel As New ADODB.Connection
Public sSql As String
Public sDataDropFile As String
Sub MainMacro()

Call ConnectToExcelFIle
Call DeleteOldTable
Call CreateNewTable
Call InsertData

End Sub
'----------------------------------------------------------
Private Sub ConnectToExcelFIle()

' Connect to the spreadsheet

sDataDropFile = "R:\lifeaccts\INVESTME\New Hierarchy Structure\Income\Valuations\2% CHECKS\Price check.xls"

conExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sSourceFile & _
";Extended Properties=Excel 8.0;"

End Sub
'-----------------------------------------------------------
Private Sub DeleteOldTable()
On Error Resume Next

sSql = "DROP TABLE [Sheet1$]"
conExcel.Execute sSql

On Error GoTo 0
End Sub
'-----------------------------------------------------------
Private Sub CreateNewTable()

sSql = ""
sSql = "CREATE TABLE [Sheet1$](" & _
"Sedol string(7)," & _
"Security string," & _
"Offer_price LONG," & _
"Price_Mvmnt LONG," & _
"Price_date DATETIME)"

conExcel.Execute sSql

End Sub
'-----------------------------------------------------------
Private Sub InsertData()
'Insert New Data

For i = 1 To 5

' dummy data :

sSedol = "b" & i
sName = "Vodafone"
dPrice = 10.24
dtedate = Format(Date, "mm/dd/yy")

sSql = ""
sSql = "Insert into [[Sheet1$]] (Sedol, Security,Offer_price,Price_Mvmnt,Price_date)" & _
" values ('" & sSedol & "','" & sName & "'," & dPrice & ",4, #" & dtedate & "#)"

conExcel.Execute sSql

Next

conExcel.Close
Set conExcel = Nothing

End Sub

 

[JerryKlmns]

>...and extracts data...
From where? A database ? Or is this happenning inside Excel? If the last is true why are you using ADODB and not just the object model of excel?

 

[bartekR]

Thanks for your response Jerry.

It's all between excel files.

As for why I am using ADODB to do that ...the honest answer would be that as a novice in VBA programming I am not familiar with any alternative apart from creating an array and transfering it to another file.

The main thing behind going for ADO was the fact that it allows to write to another excel file without opening it.

Could you point me to any resources where i could read a bit about the method you are suggesting?

thanks

 

[Andrzejek]

I tried "DELETE... WHERE.. Price_date<> date" syntax but it seems like it doesn't work in excel as it does in Access and you can't delete the whole rows.

Seams to me that you are confusing Access (which is a data base) with Excel (which is a spreadsheet, not a data base).

In Access you have records, tables, and you can use SQL’s like “Delete from ….”
In Excel you have rows, worksheets, and files.

If you grasp the differences, you will be able to handle your problems a lot better.


Have fun.

---- Andy

 

[bartekR]

Thanks Andy.

I understand that i am probably aproaching the problem from the wrong side, but it's a good occasion to practise a bit with ADO which I haven't used before.

Taking into account what you said I have changed my strategy and instead of creating/deleting tables/records I have tried now to place the extracted data into
the new WORKSHEET which i wanted to be automatically created by sql statement.

To do that i first created a template worksheet (with headers as in previous table) which I wanted to be copied over and named as per below :

sNewSheetName = Format(Date, "ddmmyy")

sSql = ""
sSql = "SELECT * INTO ['" & sSourceFile & "'].['" & sNewSheetName & "'] FROM [templatesheet$]"

conExcel.Execute sSql

When i run this code i get an error msg saying :

"CANNOT UPDATE, DATABASE OR OBJECT READONLY"

I think the devil is in "adOpenDynamic, adLockOptimistic" parameters which i have no idea how to nest into my code.

Can you or anyone else help with this?

 

[xlbo]

Rather than using SELECT * INTO

Use the copyfromrecordset method

sSQL ="SELECT * FROM [templatesheet$]"

set RecSet = conExcel.Execute sSql

Sheets("Sheetname").Range("A1").copyfromrecordset RecSet


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question