Clicky

I am trying to accomplish, what I was hoping was a simple task.  I want to have the user select an excel file to open, then import the contents of that file (from a specific sheet called "HOURLY") into an Access db.  I have some experience with this, however my coding was always hard coded (which I know isn't preferred).  This is the first time I'm leaving it up to the user to select the file.  I am still new to these concepts so, if my code is horrible, please be gentle....
 Private Sub open_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles open.Click          Dim strFileName As String         Dim xlObj, xlSheet, xlBook As Object         Dim con As New ADODB.Connection         Dim adRs As New ADODB.Recordset         Dim cnnExcel As New ADODB.Connection         Dim strSQL As String          openFD.InitialDirectory = "R:\GP Payroll Files\SJ Payroll\"         openFD.Title = "Select the Hours File to import."         openFD.ShowDialog()         strFileName = openFD.FileName          xlObj = CreateObject("Excel.Application")         xlObj.Visible = False         xlBook = xlObj.Workbooks.Open(strFileName)         xlSheet = xlBook.Worksheets("HOURLY")          strSQL = "INSERT INTO IMPORT SELECT * FROM [HOURLY$] IN " & strFileName & " ""Excel 8.0; HDR=YES;"""          con.Open( _            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\GP Payroll Files\Hours\hours.mdb;Jet OLEDB:Engine Type=4")          con.Execute(strSQL)         adRs.Close()         xlBook.Close()         xlObj.Quit()         con.Close()         con = Nothing          MsgBox("Hopefully done...", vbInformation, "Done")      End Sub                             
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 

Select allOpen in new window

asked 12/13/2011 01:38

fizzlefry's gravatar image

fizzlefry ♦♦


17 Answers:
Sorry... forgot to post the outcome...  At this level it tells me there is a problem executing my strSQL in the FROM clause.  I'm just too dumb to see where or how or why...
link

answered

fizzlefry's gravatar image

fizzlefry

Change this

strSQL = "INSERT INTO IMPORT SELECT * FROM [HOURLY$] IN " & strFileName & " ""Excel 8.0; HDR=YES;"""


to

strSQL = "INSERT INTO IMPORT SELECT * FROM [$HOURLY]"

assuming that your sheet name is HOURLY.
link

answered 2011-12-13 at 09:39:53

CodeCruiser's gravatar image

CodeCruiser

Hello, my friend...  I made the listed changes and received an error:

The Microsoft Jet database engine cannot find the input table or query '$HOURLY'.  Make sure it exists and that its name is spelled correctly.
link

answered 2011-12-13 at 11:39:26

fizzlefry's gravatar image

fizzlefry

assuming that your sheet name is HOURLY.
link

answered 2011-12-13 at 11:46:25

CodeCruiser's gravatar image

CodeCruiser

there are 2 sheets in this workbook and one is labeled HOURLY (which is the one I want to get the data from)
link

answered 2011-12-13 at 11:52:07

fizzlefry's gravatar image

fizzlefry

Then try

strSQL = "INSERT INTO IMPORT SELECT * FROM [HOURLY]"
link

answered 2011-12-13 at 11:53:09

CodeCruiser's gravatar image

CodeCruiser

The Microsoft Jet database engine cannot find the input table or query 'HOURLY'.  Make sure it exists and that its name is spelled correctly.

It seems to think that the HOURLY pertains to the Access db and not the excel spreadsheet (at least that's how I'm taking it)...
link

answered 2011-12-13 at 11:53:36

fizzlefry's gravatar image

fizzlefry

>It seems to think that the HOURLY pertains to the Access db

Correct. Because you are connecting to the Access DB.
link

answered 2011-12-13 at 11:58:18

CodeCruiser's gravatar image

CodeCruiser

So you are trying to insert the Excel sheet into Access table?

Check

http://www.access-programmers.co.uk/forums/showthread.php?t=92629

link

answered 2011-12-13 at 11:59:31

CodeCruiser's gravatar image

CodeCruiser

Ok then obviously my order and logic is completely off.  What I'm trying to do is select a specific spreadsheet containing a worksheet called "HOURLY" through this:

openFD.InitialDirectory = "R:\GP Payroll Files\SJ Payroll\"
        openFD.Title = "Select the Hours File to import."
        openFD.ShowDialog()
        strFileName = openFD.FileName

        xlObj = CreateObject("Excel.Application")
        xlObj.Visible = False
        xlBook = xlObj.Workbooks.Open(strFileName)
        xlSheet = xlBook.Worksheets("HOURLY")

Then trying to connect to the Access db to dump the contents of said spreadsheet into the table "tblIMPORT" as such:

strSQL = "INSERT INTO tblIMPORT SELECT * FROM [HOURLY]"

        con.Open( _
           "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\GP Payroll Files\Hours\hours.mdb;Jet OLEDB:Engine Type=4")

        con.Execute(strSQL)
        xlBook.Close()
        xlObj.Quit()
        con.Close()
        con = Nothing

Please help me understand where my logic is going wrong.

Also, I read through your article there and that only helps me if it's a hardcoded sheet (where as mine is user selected).  Also it's using the DoCmd.xxx and I didn't think that could be executed from VB6, but only through Access.  Again, I appreciate your patience.
link

answered 2011-12-13 at 12:01:00

fizzlefry's gravatar image

fizzlefry

Your code is going wrong because you have an excel sheet object and a db connection object and you think that these two would somehow socialize and work with each other. Its not friday night yet :-)
link

answered 2011-12-13 at 12:05:08

CodeCruiser's gravatar image

CodeCruiser

Try

Insert Into tblImport
Select *
from OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)}; DBQ=" & strFileName & ";', 'SELECT * FROM [HOURLY$]')


http://www.sqlservercentral.com/Forums/Topic652343-149-1.aspx
link

answered 2011-12-13 at 12:07:25

CodeCruiser's gravatar image

CodeCruiser

Syntax error in FROM clause.

This was my code:

strSQL = "Insert Into tblImport Select * from OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)}; DBQ=" & strFileName & ";', 'SELECT * FROM [HOURLY$]')"

I wish I knew the hard code method for "Friday Night"!!!
link

answered 2011-12-13 at 12:09:42

fizzlefry's gravatar image

fizzlefry

Try

strSQL = "Insert Into tblImport Select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Driver={Microsoft Excel Driver (*.xls)}; DBQ=" & strFileName & ";', 'SELECT * FROM [HOURLY$]')"
link

answered 2011-12-13 at 12:12:56

CodeCruiser's gravatar image

CodeCruiser

Syntax error in FROM clause...
link

answered 2011-12-13 at 12:17:37

fizzlefry's gravatar image

fizzlefry

While waiting for further response, I got it working, using this:

Dim strFileName As String
        Dim db_path As String
        Dim xlObj, xlSheet, xlBook
        Dim con As New ADODB.Connection

        db_path = "R:\GP Payroll Files\Hours\hours.mdb"

        openFD.InitialDirectory = "R:\GP Payroll Files\SJ Payroll\"
        openFD.Title = "Select the Hours File to import."
        openFD.ShowDialog()
        strFileName = openFD.FileName

        xlObj = CreateObject("Excel.Application")
        xlObj.Visible = False
        xlBook = xlObj.Workbooks.Open(strFileName)
        xlSheet = xlBook.Worksheets("HOURLY")

        con.Open( _
           "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\GP Payroll Files\Hours\hours.mdb;Jet OLEDB:Engine Type=4")

        Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName & _
    ";Extended Properties=Excel 8.0;"
        Using conn As New OleDbConnection(connect)
            Using cmd As New OleDbCommand()
                cmd.Connection = conn
                cmd.CommandText = "INSERT INTO [MS Access;Database=" & db_path & "].[tblImport] SELECT * FROM [HOURLY$]"
                conn.Open()
                cmd.ExecuteNonQuery()
            End Using
        End Using

        con.Close()
        con = Nothing
        xlBook.Close()
        xlObj.Quit()

        MsgBox("Hopefully done...", vbInformation, "Done")
link

answered 2011-12-13 at 12:19:21

fizzlefry's gravatar image

fizzlefry

Sorry, CodeCruiser...  But thanks a lot for all the help.  I'm sure I'll talk to you again soon... : )

link

answered 2011-12-13 at 13:24:12

fizzlefry's gravatar image

fizzlefry

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×60
×89
×33

Asked: 12/13/2011 01:38

Seen: 379 times

Last updated: 12/17/2011 05:20