Solved

import data from a closed workbbok

Posted on 2014-10-06
7
67 Views
Last Modified: 2014-11-03
i am using the adapted code below to import data from a closed workbook.I am using 2013 with the xlsb extension. using the xlsb extension cases it to fail.I am importing about 20 workbooks at a time.can we modify the code to make it work ? thanks
    If Header = False Then
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=No"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=No"";"
        End If
    Else
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=Yes"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"
        End If
    End If
0
Comment
Question by:Svgmassive
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 12

Expert Comment

by:James Elliott
ID: 40363805
What string have you assigned to your 'sourcefile' variable?

Please post all of your code.

Thanks
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40363808
And also, if you are importing just xlsb files then they will only import into application.version >= 12.0

This might also be your problem.
0
 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40363852
I am interested to see the solution for this thread. As I have similar issue with Ado which will post the question in a new thread
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:Svgmassive
ID: 40363898
the sourceflle is the path and work book, what ever ou want it to be ,the last line is   cnn.Open szConnect
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40363910
Just to be clear, you say that you are having problems with a particular file extension, but yet you don't want to post details of how this is being handled in your sourcefile variable??
0
 

Author Comment

by:Svgmassive
ID: 40364183
Dim szConnect As String: Dim cnn As New ADODB.Connection:

   szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"


  szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\TOYS\Desktop\ALL_TEST\2014\Jul_2014\7_1_2014\Book1.xlsb;Extended Properties=Excel 8.0;

cnn.Open szConnect,,,,,,It fails right here

this should be more than enough  there is nothing more
0
 
LVL 12

Accepted Solution

by:
James Elliott earned 500 total points
ID: 40364536
Try changing extended properties 12.0 instead of 8.0
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

752 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