473,699 Members | 2,135 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

import from excel using vb 6.0

3 New Member
Hi....

Hi everybody, i have a code that i make in VBA and know I want to use this code in to VB6. But i don't know how to use that code in to VB 6.0
Please correct this code so i can use it in VB 6.0.

Code:
--- use in mainform -------
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdImport_Click()
  5. On Error Resume Next
  6.    Dim strMsg As String
  7.    Dim strFile As String
  8.    strFile = Nz(Me.txtFile, "")
  9.    If Dir(strFile) <> "sales.xls" Then
  10.       strMsg = "You must enter a path for the file named Sales.xls"
  11.       MsgBox strMsg, vbExclamation, "Error"
  12.       Me.txtFile.SetFocus
  13.    Else
  14.       Me.lblMsg.Caption = "Import store records."
  15.       strMsg = "Do you want to import Sales records from file: " & vbCrLf & strFile
  16.       If MsgBox(strMsg, vbQuestion + vbYesNo, "Start Import") = vbYes Then
  17.          CurrentDb.Execute "DELETE FROM [sales_import]"
  18.          strMsg = ProcessFileImport(Me.txtFile, "sales_import")
  19.          MsgBox strMsg, vbInformation, "Finished"
  20.          Me.lblMsg.Caption = strMsg
  21.          DoCmd.OpenTable "sales_import"
  22.          DoCmd.MoveSize 100, 100, 9500, 6500
  23.       Else
  24.          Me.lblMsg.Caption = "Import Cancelled."
  25.       End If
  26.    End If
  27. End Sub
  28.  
  29. Private Sub Form_Load()
  30. On Error Resume Next
  31.    Me.txtFile = CurrentProject.Path & "\sales.xls"
  32. End Sub
  33.  
  34. --- use in a module -----
  35.  
  36. Option Compare Database
  37. Option Explicit
  38. Public Function ProcessFileImport(ByVal sFile As String, ByVal sTable As String) As String
  39.    On Error GoTo ProcessFileImport_Error
  40.    ' Excel object variables
  41.    Dim appExcel As Excel.Application
  42.    Dim wbk As Excel.Workbook
  43.    Dim wks As Excel.Worksheet
  44.    ' Access object variables
  45.    Dim dbs As DAO.Database
  46.    Dim rstRead As DAO.Recordset
  47.    Dim rstWrite As DAO.Recordset
  48.    Dim fld As DAO.Field
  49.    ' Declared variables
  50.    Dim bytWks As Byte
  51.    Dim bytMaxPages As Byte
  52.    Dim intStartRow As Integer
  53.    Dim strData As String
  54.    Dim intMaxRow As Integer
  55.    Dim strSQL As String
  56.    Dim strMsg As String
  57.    Dim intLastCol As Integer
  58.    Dim intRow As Integer
  59.    Dim intRec As Integer
  60.    Dim strCurrFld As String
  61.    Dim intCol As Integer
  62.    Dim intLen As Integer
  63.    Dim varValue As Variant
  64.    Dim lngErrs As Long
  65.  
  66.    Const cPassword As String = "xxx999"
  67.    DoCmd.Hourglass True
  68.  
  69.    ' Create the Excel Applicaiton, Workbook and Worksheet and Database object
  70.    Set appExcel = Excel.Application
  71.    Set wbk = appExcel.Workbooks.Open(sFile)
  72.    Set dbs = CurrentDb
  73.    ' Optionally, you can protect / unprotect with a password
  74.    'wkb.Unprotect (cPassword)
  75.  
  76.    ' You could loop through sheets, but for this example, we'll just do one.
  77.    bytMaxPages = 1
  78.    ' Sometimes there is header info, so the "Start Row" isn't the first one.
  79.    ' Set this variable to the first row that contains actual data.
  80.    intStartRow = 2
  81.    PostMessage "Opening file: " & sFile
  82.  
  83.    For bytWks = 1 To bytMaxPages
  84.       ' Initialize variables on each pass
  85.       Set wks = Nothing
  86.       Set rstRead = Nothing
  87.       intRow = intStartRow
  88.       ' Load current worksheet.  Find used range to determine row count.
  89.       Set wks = appExcel.Worksheets(bytWks)
  90.  
  91.       ' Optionally, you can protect / unprotect with a password
  92.       'wks.Unprotect (cPassword)
  93.  
  94.       ' You need to figure out how many rows this sheet contains, so to know
  95.       ' how far down to read.  That value is saved in intMaxRow
  96.       strData = wks.UsedRange.Address
  97.       intMaxRow = CInt(Mid(strData, InStrRev(strData, "$")))
  98.       'intMaxRow = CInt(Mid(strData, LastInStr(strData, "$")))
  99.  
  100.       strData = ""
  101.      ' Go get the list of fields for this worksheet from the Field Map table
  102.       strSQL = "SELECT [AccessField], [OrdinalPosition] FROM ImportColumnSpecs " & _
  103.                "WHERE [ImportName]='" & sTable & "' ORDER BY [OrdinalPosition] ASC;"
  104.       Set rstRead = dbs.OpenRecordset(strSQL, dbOpenDynaset)  
  105.       ' If there is a mistake and no specification exists, then exit with message
  106.       If rstRead.BOF And rstRead.EOF Then
  107.          strMsg = "The import spec was not found.  Cannot continue."
  108.          MsgBox strMsg, vbExclamation, "Error"
  109.       Else
  110.          rstRead.MoveLast
  111.          rstRead.MoveFirst
  112.          intLastCol = rstRead.RecordCount
  113.          ' The name of the import and destination table should be the same for this
  114.          ' code to function correctly.
  115.          Set rstWrite = dbs.OpenRecordset(sTable, dbOpenDynaset)
  116.          Do Until intRow > intMaxRow
  117.             ' Check row to be sure it is not blank.  If so, skip the row
  118.             For intCol = 1 To intLastCol
  119.                strData = strData & Trim(Nz(wks.Cells(intRow, intCol), ""))
  120.             Next      
  121.             If strData = "" Then
  122.                intRow = intRow + 1
  123.             Else
  124.                intRec = intRec + 1
  125.                PostMessage "Processing record " & intRec & ".  {StoreID=" & wks.Cells(intRow, 1) & "}"
  126.                rstWrite.AddNew
  127.                Do Until rstRead.EOF
  128.                   ' Loop through the list of fields, processing them one at a time.
  129.                   ' Grab the field name to simplify code and improve performance.
  130.                   strCurrFld = Nz(rstRead!AccessField, "")
  131.                   intCol = rstRead!OrdinalPosition
  132.  
  133.                   If dbs.TableDefs(sTable).Fields(strCurrFld).Type = dbText Then
  134.                      intLen = dbs.TableDefs(sTable).Fields(strCurrFld).Size
  135.                      varValue = Left(Nz(wks.Cells(intRow, intCol), ""), intLen)
  136.                   Else
  137.                      varValue = wks.Cells(intRow, intCol)
  138.                   End If
  139.  
  140.                   ' The database schema requires that empty fields contain NULL, not
  141.                   ' the empty string.
  142.                   If varValue = "" Then varValue = Null
  143.  
  144.                   ' Handle date columns.  Sometimes Excel doesn't format them as dates
  145.                   If InStr(1, strCurrFld, "Date") > 0 Then
  146.                      If Not IsDate(varValue) Then
  147.                         If IsNumeric(varValue) Then
  148.                            On Error Resume Next
  149.                            varValue = CDate(varValue)
  150.                            If Err.Number <> 0 Then
  151.                               ' Can't figure out the date.  Set to null
  152.                               varValue = Null
  153.                               Err.Clear
  154.                            End If
  155.                            On Error GoTo ProcessFileImport_Error
  156.                         Else
  157.                            lngErrs = lngErrs + 1
  158.                            varValue = Null
  159.                         End If
  160.                      End If
  161.                      rstWrite.Fields(strCurrFld) = varValue
  162.                   Else
  163.                      ' If not a date field, then just write the value to the rst
  164.                      rstWrite.Fields(strCurrFld) = varValue
  165.                   End If
  166.  
  167.                   rstRead.MoveNext
  168.                Loop
  169.                If Not rstRead.BOF Then rstRead.MoveFirst
  170.  
  171.                rstWrite.Update
  172.  
  173.                ' Reset the variables for processing of the next record.
  174.                strData = ""
  175.                intRow = intRow + 1
  176.                'Debug.Print intRow
  177.             End If
  178.          Loop
  179.          Set wks = Nothing
  180.       End If
  181.    Next
  182.  
  183. Exit_Here:
  184.    ' Report results
  185.    strMsg = "Total of " & intRow & " records imported."
  186.    PostMessage strMsg
  187.    ProcessFileImport = strMsg
  188.    ' Cleanup all objects  (resume next on errors)
  189.  
  190.    On Error Resume Next
  191.    ' Optionally, you can protect / unprotect with a password
  192.    'wkb.Protect (cPassword)
  193.    'wks.Protect (cPassword)
  194.    Set wks = Nothing
  195.    wbk.Close True
  196.    Set wbk = Nothing
  197.    appExcel.Quit
  198.    Set appExcel = Nothing
  199.    Set rstRead = Nothing
  200.    Set rstWrite = Nothing
  201.    Set dbs = Nothing
  202.    DoCmd.Hourglass False
  203.    Exit Function
  204.  
  205. ProcessFileImport_Error:
  206.    MsgBox Err.Description, vbExclamation, "Error"
  207.    Resume Exit_Here
  208.  
  209. End Function
  210.  
  211. Private Sub PostMessage(ByVal sMsg As String)
  212. On Error Resume Next
  213.    If IsLoaded("frmMain") Then
  214.       Forms!frmMain!lblMsg.Caption = sMsg
  215.       Forms!frmMain.Repaint
  216.    End If
  217. End Sub
  218.  
  219. Public Function IsLoaded(ByVal sForm As String) As Boolean
  220. On Error Resume Next
  221.  
  222. '/////////////////////////////////////////////
  223. '
  224. ' Returns True if the specified form is open
  225. ' in Form view or Datasheet view.
  226. '
  227. '/////////////////////////////////////////////
  228.  
  229. Const conObjStateClosed = 0
  230. Const conDesignView = 0
  231.  
  232.     If SysCmd(acSysCmdGetObjectState, acForm, sForm) <> conObjStateClosed Then
  233.         If Forms(sForm).CurrentView <> conDesignView Then
  234.             IsLoaded = True
  235.         End If
  236.     End If
  237.  
  238. End Function
  239.  
  240. Public Function LastInStr(sText As String, sFind As String) As Integer
  241. On Error Resume Next
  242.  
  243. '//////////////////////////////////////////////////////////////////////////
  244. '
  245. ' This function finds the last instance of a character within
  246. ' a string of characters and returns an integer representing
  247. ' the final position of the desired character.
  248. '
  249. ' Typically, this function us used to find the final "\" in
  250. ' a file path string
  251. '
  252. '//////////////////////////////////////////////////////////////////////////
  253.  
  254. Dim intCurrVal As Integer
  255. Dim intLastPosition As Integer
  256.  
  257.     intCurrVal = InStr(sText, sFind)
  258.     Do Until intCurrVal = 0
  259.         intLastPosition = intCurrVal
  260.         intCurrVal = InStr(intLastPosition + 1, sText, sFind)
  261.     Loop
  262.     LastInStr = intLastPosition
  263.  
  264. End Function
thanx for reply me.
Feb 1 '07 #1
1 8706
willakawill
1,646 Top Contributor
Hi. Create a form in vb. Make sure the project has excel as a reference. Paste this code. Create the controls on the form with the same name as your code and run it. Let us know what errors come up
Feb 3 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
15506
by: Fred | last post by:
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access wizard..... If I have to write vb code, where is a sample??? Or do I import the excel in to a new spreadsheet and then write some kind of querey to move the data from that table
3
3712
by: deko | last post by:
I've been trying to use the Access Import Wizard to expedite importing data into my mdb. The nice thing about the wizard is that I can import from different file formats - txt, xls, even Outlook - and dump everything into a table. The problem is once I have the data imported into a new table, I can't do much with it. If I try to run an Append query and insert data from the new table into an existing table, the query fails - "Error...
8
6550
by: Nick M | last post by:
Hello All, Excellent info here Thanks! I am very new to using access in general and I am on a learning curve. I'm trying to import an excel workbook (with worksheets) into an access db via a macro. (I'll get to using VB later on). What I would like to do is import a single workbook w/three seperate worksheets into three seperate access tables AND truncate the time stamp that is used in the excell sheet via a macro.
0
1430
by: autoEx | last post by:
I am using #import directive to import the excel library to make excel files but the excel object is not recognized by my application. Here is what I do: I have simple dialog based MFC application in VC 6.00 In the dialog .cpp file, I add the following #import "D:\\Program Files\\Microsoft Office\\Office\\MSO9.DLL" no_namespac #import "C:\\Program Files\\Common Files\\Microsoft Shared\\VBA\\VBA6\\VBE6EXT.OLB" no_namespac #import...
10
11822
by: Niklas | last post by:
Hi Before I start coding I need to be sure that nobody else has not done it yet and that I can use it. I need an import utility which import data from Excel to a database or some object in .NET. The data in Excel is not in structured columns but can exist everywhere in the workbook. For example if I am supposed to import a person and all his/her cars which exist in a workbook I want to be able to create an import protocol and specify...
8
8599
by: FireGeek | last post by:
I have a database that is split appropriately. Annually, we need to add data from .xls files. I have added code so with a click of a button, it will import this data as a new table into the database. The problem is that the code is on the front end and thus it imports the data/new table into the front ent. How can I accomplish an import (with the click of a button) into the back end and link it to the front end? THANKS
1
5790
by: Eric | last post by:
In MS Excel, the ability exists to run a "web query." This function is accessed via the data menu's import external data option. The web query wizard accepts a URL address, and then is able to import the data from that address into an excel worksheet. What I would like to do is use SSIS to import data from the same web site. In other words, I now use Excel's web query functionality to import data from a website with a url of xyz.asp. ...
3
1998
by: Roy | last post by:
Hi All, I have an A2003 application .Recently we had a request to import data from IDL files and create some reports. Since I have no idea to do that using Access,I used Excel to import data and then imported these excel sheets into Access tables. Is there a way to directly import IDL files into Access using any method or VBA? Thanks,
8
9649
by: shenkel55 | last post by:
I'm using Access and Excel 2003. Using either the import wizard or code, I have the same problem. This problem only happens with Excel files automatically generated by Corp IT. If I try to do an import and the Excel file isn't open I get the following error: "The wizard is unable to access information in the file "...path info... "Please check that the file exists and is in the correct format." If the files are opened directly in Excel, it...
0
9184
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8931
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8892
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7767
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6539
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5878
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4386
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2359
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2014
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.