Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tansfer Excel data (XP)

    I am trying to transfer data from an Excel spreadsheet into a VB app. Haven't gotten too far but here is what I have:

    In a click event I dimmed an object and set it to excel.sheet
    Dim xlSheet as object
    Set xlsheet = CreateObject("Excel.sheet")

    Also, I have made a reference to Excel object library.

    If any one can shove me in the right direction since I am not sure about any of this, is greatly appreciated. Maybe some text books to reference or whatever.

    Thanks

    Kim

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tansfer Excel data (XP)

    Your question is a bit vague. What do you mean by transferring data? Are you trying to copy the information into a datastore? If so, what do you already have set up. VB uses a Jet database but you can also store data in a text file.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tansfer Excel data (XP)

    Thanks for the response. Trying to transfer data from an excel spreadsheet to an access database thru VB. So I guess that is copying the data into a datastore? Using ADODB connections and recordsets.

    Kim

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Tansfer Excel data (XP)

    If the data in your Excel sheet has a straightforward rows and columns layout and nothing else, you can access it using ADO (see <post#=282794>post 282794</post#>). If you need to pick through a "formatted" structure, you're better off using Automation, as described in your first post.

  5. #5
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tansfer Excel data (XP)

    Thanks for the help. I will give it try. Although, I can not imagine an Excel worksheet not being formatted. Which means that I will probably be back very quickly. But first, on to your unformatted recommendation.

    Kim

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Marietta, Georgia, USA
    Posts
    296
    Thanks
    9
    Thanked 4 Times in 4 Posts

    Re: Tansfer Excel data (XP)

    Here is some example code (from an actual program) using automation to pass values both to Excel, and to return values from Excel. Here is what the code does:

    1. Passes information to Excel
    I first pass values to single cells, such as
    loXL.Range("d2").Value = gdtRetire

    Then I tried to pass an entire array at once, which did NOT work
    'loXL.range("c25:c109").Value = lfEarn

    Instead I had to pass the cell values one at a time in a loop, which is slower
    loXL.Range(lsRng).Value = gdFica(liNdex)

    2. Runs various Excel macros
    These are the lines like
    loXL.Run "NoRecalc"
    loXL.Run "ForceCalc"

    3. Finally retrieves a SINGLE cell's value
    gfPIA = CSng(loXL.Range("d22"))

    Now for the entire routine. Note that all variables prefixed with a "g" are global variables, defined in other routines. The Excel object was defined in the calling routines.


    Private Sub GetPIAxl(Index As Integer, _
    loXL As Object)
    '************************************************* *************************
    'Purpose: Load PIA spreadsheet, transfer data
    ' to Excel, calculate PIA, return it to VB program
    'Inputs: N/A
    'Returns: N/A
    'Assumes: Excel invoked by calling routine
    'Effects: Sets value of gfPIA
    '************************************************* *************************
    'Revision History:
    'Bencalc2.bas
    'GetPIAxl
    Dim loXLwb As Object
    Dim lsPath As String
    Dim liNdex As Integer
    Dim lsRng As String
    Dim liStart As Integer
    Dim liStop As Integer
    Dim lfEarn(16 To 100) As Single

    'Loads workbook
    Call FixAppPath(lsPath)
    Set loXLwb = loXL.Workbooks.Open _
    (filename:=lsPath & "pia.xls", Password:="XXX")

    'Now turn off auto recalculation (if any)
    loXL.Run "NoRecalc"

    'References below point to active workbook
    'and worksheet by default
    'Set value for "effective" date
    loXL.Range("d2").Value = gdtRetire

    'Set value for birth date
    loXL.Range("d4").Value = gdtBirth

    'Documentation says you can pass array,
    'but it did not work for me!
    'loXL.range("c25:c109").Value = lfEarn

    'Send earnings array to sheet
    For liNdex = liStart To liStop
    lsRng = "C" & CStr(16 + 9 + liNdex - liYr16)
    loXL.Range(lsRng).Value = gdFica(liNdex)
    Next liNdex

    'Now make sure all values have been updated
    loXL.Run "ForceCalc"

    'Save the PIA value
    gfPIA = CSng(loXL.Range("d22"))

    If Index = 0 Then
    MsgBox "PIA value is " & _
    CStr(loXL.Range("d22")) & vbCrLf & _
    "Press OK to get Factors"
    End If

    'Close current workbook, don't save changes
    loXLwb.Close savechanges:=False
    Set loXLwb = Nothing

    End Sub


    I hope this gives a clear picture of moving data back and forth from a VB program to Excel.
    Rick Groszkiewicz
    Life is too short to drink bad wine (or bad coffee!)

  7. #7
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tansfer Excel data (XP)

    rgrosz -

    Many thanks for the input. Will have to study what you have presented. Will let you know.

    Kim

  8. #8
    New Lounger
    Join Date
    Dec 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tansfer Excel data (XP)

    Want to query an Excel workbook/sheet to check for the existence of certain fields and if
    they do, transfer the data from those designated fields to Access.

    Have used what jscher2000 has suggested and can read one line in the worksheet. Thought I would
    try the unformatted one first.

    The first row lists the names of the fields that are setup in Access. Succeeding rows
    contain data, i.e. A1 Firstname, B1 Lastname; A2 Kim B2 Peterson, etc. There are 14
    variables including workbook name and sheet name. I have included
    only the first two field variabes for brevity.

    What is what has been developed. It does not find the first field (firstname):


    Private Sub mnuTransferExcel_Click()
    Dim sSheet As String
    Dim sfld As Variant

    If cdgTransfer.FileName = "" Then sSourcePath = App.Path & ""

    cn.Open "PROVIDER=MSDASQL;DRIVER={Microsoft Excel Driver (*.xls)};" & _
    "DBQ=" & sSourcePath & txtDatabase.Text
    rs.Open "[" & txtTable.Text & "$]", cn, adOpenDynamic, , adCmdTable

    sfld = ExcelToFind(txtDatabase.Text, txtTable.Text, txtFirstname.Text, txtLastname.Text)

    End Sub

    Public Function ExcelToFind(ByVal sXlName As String, _
    sSheetName As String, ParamArray sfldName()) As Boolean
    Dim vItem As Variant
    Dim bFoundAll As Boolean
    Dim cn As New ADODB.Connection
    On Error GoTo HandleError
    If cn.State = adStateOpen Then
    cn.Close
    End If
    cn.Open ("PROVIDER=MSDASQL;DRIVER={Microsoft Excel Driver (*.xls)};" & _
    "DBQ=") & sXlName
    If rs.State = adStateOpen Then
    rs.Close
    End If

    rs.Open "[" & sSheetName & "$]", cn, adOpenDynamic, , adCmdTable

    For Each vItem In sfldName
    bFoundAll = FieldExist(cn, sSheetName, vItem)
    'If any one field is not found then the whole function fails
    If Not bFoundAll Then Exit For
    Next vItem
    If Not bFoundAll Then
    Exit Function
    End If

    cn.Close
    Set cn = Nothing
    'FieldToFind = bFoundAll
    'Exit Function

    FieldtoFind_Exit:
    Exit Function
    HandleError:
    Err.Raise Err.Number, Err.Description
    End Function

    Public Function FieldExist(conn As ADODB.Connection, ByVal sTableToFind As String, ByVal sFieldToFind As String) As Boolean
    Dim rst As ADODB.Recordset
    Set rst = conn.OpenSchema(adSchemaColumns, Array(Empty, Empty, sTableToFind, sFieldToFind))
    FieldExist = Not (rst.EOF And rst.BOF)
    rst.Close
    Set rst = Nothing
    End Function


    Any help is appreciated.

    Kim

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Tansfer Excel data (XP)

    Place a Stop statement in your code on the line after rs.Open, open the Locals window and check the parameters for the RecordSet:

    1. <LI>What is BOF? If True, you need to rs.MoveFirst to get to the first record. Try doing that in the Immediate window while you're still in Debug mode, then close and re-open the Locals window to refresh it.

      <LI>In the Fields collection, does it recognize the words in the first row as field names? If not, you can push past the first record and use column numbers (1 and 2) to refer to your fields. Not as human-friendly, but... you also could define a Constant to get around that if you like.
    Once you can reliably position on the first record containing actual data, you then need a loop similar to

    While Not rs.EOF
    'do real work, e.g., PushToAccess(rs.Fields("Firstname").Value, rs.Fields("Lastname").Value)
    rs.MoveNext
    Wend

    to get all the data over.

    Now, I must admit, I'm kind of lost on what's happening with most of your code. Do you have time to comment it and post it again (you can edit your post to save vertical inches in the thread).

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Tansfer Excel data (XP)

    Sorry, missed a point in my above post:

    > Want to query an Excel workbook/sheet to check for the existence of certain fields...

    If you actually want to query across the content of the sheet, you might be able to use the Find method of the Recordset object. This example is a bit out of context, but it gives the general idea.

    <pre>lngQueryTerm = 123456
    With rsR
    .Find "mruUnique = " & lngQueryTerm
    If .RecordCount > 0 Then
    .MoveFirst
    MsgBox .Fields("mruLastUsed")
    Else
    MsgBox "Unable to obtain ""last used"" date."
    End If
    End With</pre>

    Hope this helps.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Marietta, Georgia, USA
    Posts
    296
    Thanks
    9
    Thanked 4 Times in 4 Posts

    Re: Tansfer Excel data (XP)

    Kim, I found a few problems in your code. The major one is that you need to pass the path to the Excel file in your call to the ExcelToFind routine:

    sfld = ExcelToFind(sSourcePath & txtDatabase.Text, txtTable.Text, txtFirstname.Text, txtLastname.Text)
    'Bad - needs path!
    'sfld = ExcelToFind(txtDatabase.Text, txtTable.Text, txtFirstname.Text, txtLastname.Text)

    The second problem is that your FieldExist routine does not work. I was unable to figure out the trick to the OpenSchema method, so I used J. Scher's suggestion about using the Fields collection of the Recordset object. I replaced your call to FieldExist with a For/Next loop. This code successfully identified both the Firstname and Lastname fields:

    Dim liNum As Integer
    For Each vItem In sfldName

    bFoundAll = False
    For liNum = 1 To rs.Fields.Count
    If rs.Fields(liNum - 1).Name = vItem Then
    bFoundAll = True
    Exit For
    End If
    Next

    'This did not work
    'bFoundAll = FieldExist(cn, sSheetName, vItem)

    'If any one field is not found then the whole function fails
    If Not bFoundAll Then Exit For
    Next vItem
    Rick Groszkiewicz
    Life is too short to drink bad wine (or bad coffee!)

  12. #12
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Tansfer Excel data (XP)

    Rick,

    Many thanx for your suggestions. Have been out of town for a few days and just returned. The openschema works for transfering Access data and I assumed that it would also work for Excel.

    Will check this out and let you know.
    Thanks

    Kim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •