VBscript to VBA

Posted on 2007-10-16
Last Modified: 2013-12-25
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

If blTExists Then

& " FROM " & DATA_SOURCE & ";"
End If

oDB.Execute strSQL


Question by:mweidner
    1 Comment
    LVL 65

    Accepted Solution

    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>



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now