• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

VBscript to VBA

Can this vbscript be modified to run in Excel VBA?  

Option Explicit

Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim blTExists 'As Boolean
Dim strSQL 'As String

Const DB_NAME = "C:\rubyfiles\tanks\gasdata.mdb"
Const TBL_NAME = "Reg_VR_data"
Const DATA_SOURCE = "[Text;HDR=NO;FMT=Delimited;Database=C:\rubyfiles\tanks\;].regular_data.csv"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

For Each oTDef In oDB.TableDefs
If oTDef.Name = TBL_NAME Then
blTExists = True
Exit For
End If
Next

If blTExists Then
strSQL = "INSERT INTO " & TBL_NAME _
& " SELECT * FROM " & DATA_SOURCE & ";"

Else
strSQL = "SELECT * INTO " & TBL_NAME _
& " FROM " & DATA_SOURCE & ";"
End If

oDB.Execute strSQL

oDB.Close

0
mweidner
Asked:
mweidner
1 Solution
 
RobSampsonCommented:
Hi, this code should work pretty closely just as is......but you will need to edit a macro in the VBA editor, click Tools --> References, and tick the component
Microsoft DAO 3.6 Object Library

Then, add
Sub GetData()
as the first line, and
End Sub
as the last line, and remove the apostrophes at the top before the type declarations that are
As <datatype>

Regards,

Rob.
0
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now