0

My script fails when trying to connect to a 2010 excel file with an ADODB.Connection.

I have read all about the 32 bit v 64 bit but still can't get it to work.

Is there an easy way to connect to an excel file and grab some data with vb script...? thanks

'## Create, Open, and Close Excel Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Open("\\WWAUDB01\MKTGDA_Raw\US\AU Subscription Payment Report*")

Dim varh
varh = "Hello"

copyData objExcel

sub copyData(book)
    set objDestXL = CreateObject("Excel.Application")
    objDestXL.Visible = True
    set objDestWrkBk = objDestXL.Workbooks.Add
    set objDestWrkSht = objDestWrkBk.Sheets.Add(, objDestWrkBk.Sheets(objDestWrkBk.Sheets.Count))
    Dim myConString
    Dim myConn
    Dim myRS
    'Define Variables
    'Define the connection string
    myConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\WWAUDB01\MKTGDA_Raw\US\AU Subscription Payment Report*;" 

    'Gain access to ADODB Connection and Recordset objects   
    Set myConn = CreateObject("ADODB.Connection")   
    Set myRS = CreateObject("ADODB.Recordset")   

    'Establish the Connection
    myConn.Open myConString
    'Open a recordset in the connection. A sheet or range are valid
    myRS.Open "SELECT * FROM [Sheet1$]", myConn
end sub
objExcel.Quit
Sankar Balasubramanian
  • 83
  • 8
Craigoh1
  • 169
  • 2
  • 3
  • 15
  • Where does it fail? What exactly happens? Du you have any error mesage? I've edited your code block. Now some can read it without finger burning scrolling ;) – Clijsters Apr 28 '16 at 21:55
  • And then my hint: A scripting language has the property to be processed sequentially... – Clijsters Apr 28 '16 at 21:59
  • Hi Clijisters,thanks for cleaning up code. – Craigoh1 Apr 29 '16 at 02:56
  • what do you mean processed sequentially? – Craigoh1 Apr 29 '16 at 02:56
  • It fails at the line where I attempt to open the connection string mConn.Open myConString. Im having lots of trouble getting anything to connect to an excel file... VB script, MSSQL, Linked Server.. it must be something to do with 32 vs 64 bit.. I have tried so many things but can't seem to crack it. Something so simple as to connect to an excel file is giving me great headaches :( – Craigoh1 Apr 29 '16 at 02:56

0 Answers0