Excel File manipulation using Visual Basic

I currently have an Excel spreadsheet with about 6 different database table connections and a vba program that searches those different sheets for certain information and puts them in their correct cells on a separate worksheet. All of these are in the same workbook. It works nicely, but SLOW. I know this is not the most efficient way to do this. I had a couple of ideas but I do not know enough to implement nor how to find information how to implement them

1) Use connection strings within VBA to connect to the oracle database and let the DBMS do all the queries (like it should). Does Excel VBA have this function?

2) Use visual Basic to interface with the Oracle database then create an excel spreadsheet with the information. My question here is can Visual Basic write an exel file or do you have to create a delimited file, then use excel to import that file (which is NOT what i want to do because the user I want to use this needs it to be as simple as possible)?
Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
If you have Excel installed on the machine running the VB program, then yes, VB can automate Excel and create the workbook. You can also use ADO in VBA to run procedures on the Oracle db directly using code like:
   Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset
   Application.ScreenUpdating = False
   Set cn = New ADODB.Connection
   With cn
      .Provider = "MSDAORA.Oracle"
      .ConnectionString = "Data Source=servername;User ID=whatever;Password=something"
   End With
   strQuery = "SELECT * FROM statplac WHERE PLACINGKEY < 100"
   Set rst = New ADODB.Recordset
   rst.Open strQUery, cn, adOpenStatic, adLockReadOnly, adCmdText

and you can then use the Range object's CopyFromRecordset method to get the data wherever you want, or put individual fields in individual cells.

rolltide0Author Commented:
Thats exactly what I need. thanks
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.