• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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