Excel file upload or import into Access

**hariharanmca** · Aug 4 '07, 08:04 AM

Originally posted by lucky13

Hello to all, can anyone please help in how to import the excel file into Access through VB Code. I have written a prog. but i m getting error as "Sntax Error in From Clause"

Can you explain little bit more. and what you had tryed till now?

**lucky13** · Aug 6 '07, 09:52 AM

Originally posted by hariharanmca

Can you explain little bit more. and what you had tryed till now?

Thanks for shwoing intrest in my question, below i m mentioning the code which i wrote, which help u to solve my proble ;

Private Function UploadExcel() As Boolean

On Error GoTo Err

Dim rstemp As New ADODB.Recordset
Dim rsupload As New ADODB.Recordset
Dim rssrno As New ADODB.Recordset
Dim objexcel As Object
Dim strsql As String
Dim strfilename As String
Dim intcount As Integer, x As Long
Dim strfname As String, strlname As String, strsrno As String

conn.Open
'cn.Open
rsupload.Cursor Location = adUseClient
rstemp.CursorLo cation = adUseClient
rssrno.CursorLo cation = adUseClient
' UploadExcel = False

'strfilename = frmInputBox.Inp utString("Selec t upload file", "Upload File...", "", , "xls")
'strfilename = InputBox("Selec t excel file for Upload", "Upload File...", "", "xls")
'strfilename = InputBox("Selec t upload file", "Upload File...", "", , "xls") 'PLEASE CHECK THE PARAMETER SEQUENCE

strfilename = InputBox("Selec t Upload File", "Upload Files....", "")

MsgBox "File Uploading Started", vbOKOnly + vbInformation, "Uploading"

If Trim(strfilenam e) <> "" Then
Screen.MousePoi nter = vbHourglass

Set objexcel = GetObject(Trim( strfilename), "Excel.Shee t")

strsql = "select * from School.mdb"
'rsupload.Open strsql, conn, adOpenDynamicm, adLockOptimisti c
rsupload.Open strsql, conn, adOpenStatic, adLockOptimisti c, adCmdTable

intcount = 0
x = 1

Do While Not (objexcel.Activ eSheet.Range("A " & CStr(x)) = "")
If Not objexcel Is Nothing Then
intcount = incount + 1
x = intcount
If ((x = 1) And ((objexcel.Acti veSheet.Range(" A" & CStr(1))) <> "SrNo")) Then
Screen.MousePoi nter = vbDefault
MsgBox "Not Appropriate file", vbCritical + vbOKOnly, "Upload Excel File Error"
rsupload.Close
' changeuploadsta tus ("N")

Set rsupload = Nothing
Set objexcel = Nothing

Exit Function
End If
'---------------------------------------------------------------------------
If objexcel.Active Sheet.Range("A" & CStr(x)) <> "SrNo" Then
strsrno = obsjexcel.Activ eSheet.Range("A " & CStr(x))
strfname = objexcel.Active Sheet.Range("B" & CStr(x))
strlname = objexcel.Active Sheet.Range("C" & CStr(x))

'Insert into Table1 (Srno,firstname ,lastname) values('" & strsrno &'", '"&strfname& '", '"&strlname& '")
strsql = "Insert Into Table1 (SrNo, firstname, lastname)values ('" & strsrno & "', '" & strfname & "','" & strlaname & "')"

conn.Execute strsql

End If
End If
x = x + 1
Loop
Else
UploadExcel = False
Screen.MousePoi nter = vbDefault
End If

If rsupload.State = adStateOpen Then rsupload.Close
If rssrno.State = adStateOpen Then rssrno.Close

Set rssrno = Nothing
Set rsupload = Nothing
Set objexcel = Nothing

Exit Function

Err:
Screen.MousePoi nter = vbDefault
MsgBox Err.Number & ": " & Err.Description , vbOKOnly + vbCritical, "UploadexcelFil e error..."

UploadExcel = False
Resume
rsupload.Close
rssrno.Close

Set rssrno = Nothing
Set rsupload = Nothing
Set objexcel = Nothing

End Function

**lucky13** · Aug 6 '07, 10:56 AM

Originally posted by hariharanmca

Can you explain little bit more. and what you had tryed till now?

Code:

Private Function UploadExcel() As Boolean

    On Error GoTo Err
    
    Dim rstemp As New ADODB.Recordset
    Dim rsupload As New ADODB.Recordset
    Dim rssrno As New ADODB.Recordset
    Dim objexcel As Object
    Dim strsql As String
    Dim strfilename As String
    Dim intcount As Integer, x As Long
    Dim strfname As String, strlname As String, strsrno As String
    
    conn.Open
    'cn.Open
    rsupload.CursorLocation = adUseClient
    rstemp.CursorLocation = adUseClient
    rssrno.CursorLocation = adUseClient
  '  UploadExcel = False
    
    'strfilename = frmInputBox.InputString("Select upload file", "Upload File...", "", , "xls")
    'strfilename = InputBox("Select excel file for Upload", "Upload File...", "", "xls")
    'strfilename = InputBox("Select upload file", "Upload File...", "", , "xls")        'PLEASE CHECK THE PARAMETER SEQUENCE
    
    strfilename = InputBox("Select Upload File", "Upload Files....", "")
    
    
    MsgBox "File Uploading Started", vbOKOnly + vbInformation, "Uploading"
    
    If Trim(strfilename) <> "" Then
    Screen.MousePointer = vbHourglass
    
    Set objexcel = GetObject(Trim(strfilename), "Excel.Sheet")
    
    strsql = "select * from Table1"
    'rsupload.Open strsql, conn, adOpenDynamicm, adLockOptimistic
    rsupload.Open strsql, conn, adOpenStatic, adLockOptimistic, adCmdTable
    
    intcount = 0
    x = 1
    
    Do While Not (objexcel.ActiveSheet.Range("A" & CStr(x)) = "")
        If Not objexcel Is Nothing Then
            intcount = incount + 1
            x = intcount
            If ((x = 1) And ((objexcel.ActiveSheet.Range("A" & CStr(1))) <> "SrNo")) Then
                Screen.MousePointer = vbDefault
                MsgBox "Not Appropriate file", vbCritical + vbOKOnly, "Upload Excel File Error"
                rsupload.Close
'                changeuploadstatus ("N")
                
                Set rsupload = Nothing
                Set objexcel = Nothing
                
                Exit Function
            End If
        '---------------------------------------------------------------------------
            If objexcel.ActiveSheet.Range("A" & CStr(x)) <> "SrNo" Then
                strsrno = obsjexcel.ActiveSheet.Range("A" & CStr(x))
                strfname = objexcel.ActiveSheet.Range("B" & CStr(x))
                strlname = objexcel.ActiveSheet.Range("C" & CStr(x))
                
                'Insert into Table1 (Srno,firstname,lastname) values('" & strsrno &'", '"&strfname&'", '"&strlname&'")
                strsql = "Insert Into Table1 (SrNo, firstname, lastname)values('" & strsrno & "', '" & strfname & "','" & strlaname & "')"
                conn.Execute strsql
                                               
            End If
        End If
        x = x + 1
        Loop
    Else
        UploadExcel = False
        Screen.MousePointer = vbDefault
    End If
    
    If rsupload.State = adStateOpen Then rsupload.Close
    If rssrno.State = adStateOpen Then rssrno.Close
    
    Set rssrno = Nothing
    Set rsupload = Nothing
    Set objexcel = Nothing

Exit Function

Err:
Screen.MousePointer = vbDefault
MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbCritical, "UploadexcelFile error..."

UploadExcel = False
Resume
rsupload.Close
rssrno.Close

Set rssrno = Nothing
Set rsupload = Nothing
Set objexcel = Nothing


End Function

My database name is school.mbd & the excel file name is Text1.xls. & I create one DSN name as school, Access database is attached to this.

**hariharanmca** · Aug 6 '07, 12:23 PM

Originally posted by lucky13

Code:

Private Function UploadExcel() As Boolean

    On Error GoTo Err
    
    Dim rstemp As New ADODB.Recordset
    Dim rsupload As New ADODB.Recordset
    Dim rssrno As New ADODB.Recordset
    Dim objexcel As Object
    Dim strsql As String
    Dim strfilename As String
    Dim intcount As Integer, x As Long
    Dim strfname As String, strlname As String, strsrno As String
    
    conn.Open
    'cn.Open
    rsupload.CursorLocation = adUseClient
    rstemp.CursorLocation = adUseClient
    rssrno.CursorLocation = adUseClient
  '  UploadExcel = False
    
    'strfilename = frmInputBox.InputString("Select upload file", "Upload File...", "", , "xls")
    'strfilename = InputBox("Select excel file for Upload", "Upload File...", "", "xls")
    'strfilename = InputBox("Select upload file", "Upload File...", "", , "xls")        'PLEASE CHECK THE PARAMETER SEQUENCE
    
    strfilename = InputBox("Select Upload File", "Upload Files....", "")
    
    
    MsgBox "File Uploading Started", vbOKOnly + vbInformation, "Uploading"
    
    If Trim(strfilename) <> "" Then
    Screen.MousePointer = vbHourglass
    
    Set objexcel = GetObject(Trim(strfilename), "Excel.Sheet")
    
    strsql = "select * from Table1"
    'rsupload.Open strsql, conn, adOpenDynamicm, adLockOptimistic
    rsupload.Open strsql, conn, adOpenStatic, adLockOptimistic, adCmdTable
    
    intcount = 0
    x = 1
    
    Do While Not (objexcel.ActiveSheet.Range("A" & CStr(x)) = "")
        If Not objexcel Is Nothing Then
            intcount = incount + 1
            x = intcount
            If ((x = 1) And ((objexcel.ActiveSheet.Range("A" & CStr(1))) <> "SrNo")) Then
                Screen.MousePointer = vbDefault
                MsgBox "Not Appropriate file", vbCritical + vbOKOnly, "Upload Excel File Error"
                rsupload.Close
'                changeuploadstatus ("N")
                
                Set rsupload = Nothing
                Set objexcel = Nothing
                
                Exit Function
            End If
        '---------------------------------------------------------------------------
            If objexcel.ActiveSheet.Range("A" & CStr(x)) <> "SrNo" Then
                strsrno = obsjexcel.ActiveSheet.Range("A" & CStr(x))
                strfname = objexcel.ActiveSheet.Range("B" & CStr(x))
                strlname = objexcel.ActiveSheet.Range("C" & CStr(x))
                
                'Insert into Table1 (Srno,firstname,lastname) values('" & strsrno &'", '"&strfname&'", '"&strlname&'")
                strsql = "Insert Into Table1 (SrNo, firstname, lastname)values('" & strsrno & "', '" & strfname & "','" & strlaname & "')"
                conn.Execute strsql
                                               
            End If
        End If
        x = x + 1
        Loop
    Else
        UploadExcel = False
        Screen.MousePointer = vbDefault
    End If
    
    If rsupload.State = adStateOpen Then rsupload.Close
    If rssrno.State = adStateOpen Then rssrno.Close
    
    Set rssrno = Nothing
    Set rsupload = Nothing
    Set objexcel = Nothing

Exit Function

Err:
Screen.MousePointer = vbDefault
MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbCritical, "UploadexcelFile error..."

UploadExcel = False
Resume
rsupload.Close
rssrno.Close

Set rssrno = Nothing
Set rsupload = Nothing
Set objexcel = Nothing


End Function

My database name is school.mbd & the excel file name is Text1.xls. & I create one DSN name as school, Access database is attached to this.

lucky13, just post only that method and where you are getting the problem and what are the solution you need. Then only we can analysis your code.
Do not dump a huge code. Post very few code and explain more.

**lucky13** · Aug 11 '07, 12:59 PM

can anybody please help in how to import Excel data into Access through VB. i have written program. I am getting Error as Syntax error FROM Clause.....

**Scott Price** · Aug 11 '07, 02:01 PM

Originally posted by lucky13

can anybody please help in how to import Excel data into Access through VB. i have written program. I am getting Error as Syntax error FROM Clause.....

You have posted this in the Visual Basic forum, it should properly be posted in the Access forum, as the VBA that Access uses is different from 'regular' VisualBasic...

Perhaps one of the moderators can move it across for you.

Regards,
Scott

**lucky13** · Aug 17 '07, 10:43 AM

thnks , my problem has been solved.

**Krunchy** · Aug 17 '07, 07:33 PM

Please share what the correction what I'm having the same issue.

**lucky13** · Sep 17 '07, 10:30 AM

Originally posted by Krunchy

Please share what the correction what I'm having the same issue.

sorry for replying late., u r problem has solved or not. if not then pls, let me know.

regards
lucky13

**smugcool** · Sep 17 '07, 11:11 AM

Hi Lucky,

Plz give me the solution. How u did it? As i m also having the same problem.

Thanx in advance.

**lucky13** · Sep 17 '07, 11:53 AM

Originally posted by smugcool

Hi Lucky,

Plz give me the solution. How u did it? As i m also having the same problem.

Thanx in advance.

please let me know where exactly u r getting problem....

Excel file upload or import into Access

Excel file upload or import into Access

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment