General discussion

Locked

Execute code for an Excel spreadsheet

By hurdk ·
I have an Excel spreadsheet.

I need to execute instructions such as:

Dim dbs As database
Dim strSQL As String
Dim fld As Field
Dim rs1 As Recordset
On Error Resume Next
Set dbs = CurrentDb()
fld.Attributes = dbAutoIncrField
With tdf
.Fields.Append.CreateField("PersonID,dbLong")
End With

strSQL = "SELECT PersonID FROM .. ORDER ... "
DoCmd.RunSQL (strSQL)
The coding cannot be performed with a macro related to Excel.

Thank you.

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Execute code for an Excel spreadsheet

by Bristar In reply to Execute code for an Excel ...

Why are you trying to pull a SQL statement on a spreadsheet? As capable as it is, it is still not a database. Maybe you should import it into Access and then try..

Collapse -

Execute code for an Excel spreadsheet

by hurdk In reply to Execute code for an Excel ...

Poster rated this answer

Collapse -

Execute code for an Excel spreadsheet

by jmiller1 In reply to Execute code for an Excel ...

--The coding cannot be performed with a macro related to Excel.

Why can't it? Is that an error message you are getting? When you are in the Visual Basic editor, go to Tools->References and add a reference to Microsoft DAO, and you should be able to run your data access code.

jm

Collapse -

Execute code for an Excel spreadsheet

by hurdk In reply to Execute code for an Excel ...

Poster rated this answer

Collapse -

Execute code for an Excel spreadsheet

by p2k In reply to Execute code for an Excel ...

You know you don't have a "CurrentDB" in Excel.. you have to connect to the database using either a DSN or by specifying the connection string.
You need to add a reference to either DAO, RDO or ADO.
Since you have previously been using DAO you might find it easier to keep using it. Personally, I think ADO is way simpler to use.

You can't use the DoCmd object outside Access. With DAO, you need to create a reference to your database by using the Workspace object's OpenDatabase method. You can then run SQL queries with the Database object's OpenRecordset method.

If you only need to retrieve data from a database to an Excel sheet, try Data | External Data | Create new query. This will open the MS Query window, allow you to connect to your db, write your SQL statement and return the data to your spreadsheet (provided you have MS Query installed).
You can refresh the data at any time with a click of a button, and it's fairly easy to manipulate the SQL sentence dynamically with VBA. Just make sure you use full UNC paths in case your source db is in the network and you want to make sure all users can access it, regardless of their network drive letter mappings.

And yes, you can even query an Excel sheet using SQL, just use the appropriate driver in your connection string. But there ARE easier ways of displaying only the data matching your criteria..

Collapse -

Execute code for an Excel spreadsheet

by hurdk In reply to Execute code for an Excel ...

Excellent professional answer.

I learned a lot from reviewing and analyzing this answer.

Collapse -

Execute code for an Excel spreadsheet

by hurdk In reply to Execute code for an Excel ...

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums