Consulting

Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 21 to 40 of 57

Thread: need to import merged workbook into access

  1. #21
    sure no problem. Here it is:
    [vba]
    Dim strSheetName As Variant
    Dim strFilePath As String
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim i As Integer
    Dim connToUpdate As ADODB.Connection
    Dim strSQL As String

    filepath = "C:\Documents and Settings\mpearce\My Documents\merged recon files.xls"
    'change strFilePath to filepath to your spreadsheet
    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=" & strFilePath & ";" _
    & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

    'array of all of your worksheet names
    strSheetName = Array(DecoOpen, DecoClosed, NotInDeco, EligibilityNotInHospital, BillingNotInHospital, _
    DecoOpen(2), DecoClosed(2), NotInDeco(2), EligibilityNotInHospital(2), _
    BillingNotInHospital(2))
    'creates a connection to your database
    Set connToUpdate = CurrentProject.Connection

    'this loop will go through each worksheet name in the array
    For i = LBound(strSheetName) To UBound(strSheetName)
    Set rst = New ADODB.Recordset
    'create a recordset for the currently selected worksheet
    rst.Open "SELECT * FROM [" & strSheetName(i) & "];", conn, adOpenStatic, adLockOptimistic
    rst.MoveFirst '<<<explicitly move to the first record in the recordset

    'this loop will update your table one record at a time
    'the execute method is one of the fastest ways to update your table
    Do Until rst.EOF '<<<Loop runs until end of recordset is reached
    'this is the SQL Update statement you need to adjust
    strSQL = "INSERT INTO " & strSheetName(i) & "(**Fields to update**) " _
    & "'VALUES (rst![FieldName].Value & " ')"

    'run the SQL statement
    connToUpdate.Execute strSQL
    rst.MoveNext '<<<Move to next record
    Loop

    rst.Close
    Next

    connToUpdate.Close
    rst.Close
    conn.Close
    Set connToUpdate = Nothing
    Set rst = Nothing
    Set conn = Nothing
    [/vba]

  2. #22
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by CreganTur
    Hmmm...

    Is ADO kicking them out, even when the names w/ parentheses are wrapped in brackets?
    I get that error when I attempt to conn.Execute a SQL statement which references a sheet whose name includes parentheses. Within the SQL statement, the name is surrounded by square brackets.

    Hans

  3. #23
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by mpearce
    sure no problem. Here it is:
    [vba]
    Dim strSheetName As Variant
    Dim strFilePath As String
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim i As Integer
    Dim connToUpdate As ADODB.Connection
    Dim strSQL As String

    filepath = "C:\Documents and Settings\mpearce\My Documents\merged recon files.xls"
    'change strFilePath to filepath to your spreadsheet
    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=" & strFilePath & ";" _
    & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    [/vba]
    You never assigned a value to your strFilePath variable ... so your connection string includes nothing for Data Source.

    However you did assign a file path to another variable, "filepath". But you didn't declare it (Dim) first. If you add Option Explicit to the declarations section of your module, it will save you from this kind of grief.

    Hans

  4. #24
    ah gotcha on that one. I modified filepath to strfilepath and that error is fixed. But now when it comes to:

    [VBA]strSheetName = Array(DecoOpen, DecoClosed, NotInDeco, EligibilityNotInHospital, BillingNotInHospital, _
    DecoOpen(2), DecoClosed(2), NotInDeco(2), EligibilityNotInHospital(2), _
    BillingNotInHospital(2))[/VBA]

    i get a 'type mismatch' run time error and all the elements in the array are empty at that point.

  5. #25
    side note that error occurs with the option explicit line
    when i add it i get variable not defined and i am pointed to the first element in the array. This doesnt make sense.

  6. #26
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    side note that error occurs with the option explicit line
    when i add it i get variable not defined and i am pointed to the first element in the array. This doesnt make sense.
    You need to wrap the worksheet names with double-quotes to make them into string values. VBA think that they are undefined variables otherwise.

    Always, ALWAYS, use Option Explicit. You can make this happen automatically by clicking Tools->Options->Require Variable Declaration. This enforces good coding practice by forcing you to declare all variables that you use.
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  7. #27
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    I get that error when I attempt to conn.Execute a SQL statement which references a sheet whose name includes parentheses. Within the SQL statement, the name is surrounded by square brackets.

    Hans
    If you create a regular query and run it within Access, will it work with object names that have parentheses? If it does, then it shows that ADO has a restriction that's causing problems. If this is the case, then one of two things needs to happen:

    1) parentheses need to be removed from all table/field names, and removed from the worksheets in the workbook.

    -or-

    2) if mpearce says that the parentheses cannot be removed because of some sort of process restriction, then we'll have to use the TransferSpreadsheet method as an alternative.
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  8. #28
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by hansup
    I get that error when I attempt to conn.Execute a SQL statement which references a sheet whose name includes parentheses. Within the SQL statement, the name is surrounded by square brackets.

    Hans
    Sorry, Randy. I stated that wrong. Let me show you a stripped down code example instead:

    [vba]Public Sub ParenthesesProblem()
    Const strWorkbook As String = "Import2Access.xls"
    Dim strFilePath As String
    Dim varSheetNames As Variant
    Dim varSheet As Variant
    Dim cnExcel As ADODB.Connection
    Dim strSQL As String
    Dim strCnString As String
    Dim rst As ADODB.Recordset

    strFilePath = CurrentProject.Path & "\" & strWorkbook
    Debug.Print "Workbook path name: " & strFilePath

    'set up connection to Excel workbook
    Set cnExcel = New ADODB.Connection
    strCnString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source='" & strFilePath & "';" _
    & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
    Debug.Print "Connection string: " & strCnString
    cnExcel.Open strCnString

    'array of all of your worksheet names
    'varSheetNames = Array("DecoOpen", "DecoClosed", "EligibilityNotInHospital", _
    '& "DecoOpen(2)", "DecoClosed(2)")
    varSheetNames = Array("DecoOpen", "DecoClosed", "EligibilityNotInHospital")

    Set rst = New ADODB.Recordset
    'this loop will go through each worksheet name in the array
    For Each varSheet In varSheetNames
    'create a recordset for the currently selected worksheet
    rst.Open "SELECT * FROM [" & varSheet & "$];", cnExcel, adOpenStatic, adLockOptimistic

    rst.Close
    Next varSheet

    Set rst = Nothing
    cnExcel.Close
    Set cnExcel = Nothing
    ExitHere:
    Exit Sub
    End Sub
    [/vba] As written, it runs without error. But when I attempt to use the commented-out version of the varSheetNames array, it fails on the rst.Open step with error messages like this:

    'DecoOpen[2]$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

    Without the dollar sign, I couldn't get it to accept a specific worksheet name in a workbook which contains more than one sheet. I've tried placing the dollar sign in different positions with no joy.

    I'm attaching my workbook in case you willing to look at this.

    Hans

  9. #29
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by CreganTur
    If you create a regular query and run it within Access, will it work with object names that have parentheses?
    I didn't get that far --- my code attempts failed before I got to any step which referenced an Access table name which includes parentheses. But I'll give it a try.
    Quote Originally Posted by CreganTur
    If it does, then it shows that ADO has a restriction that's causing problems. If this is the case, then one of two things needs to happen:

    1) parentheses need to be removed from all table/field names, and removed from the worksheets in the workbook.

    -or-

    2) if mpearce says that the parentheses cannot be removed because of some sort of process restriction, then we'll have to use the TransferSpreadsheet method as an alternative.
    I vote for #1!
    I can't get TransferSpreadsheet to work with sheet names containing parantheses.

  10. #30
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by CreganTur
    If you create a regular query and run it within Access, will it work with object names that have parentheses?
    Yes, this one works:

    SELECT *
    FROM [DecoClosed (2)];

  11. #31
    The sheets could really be called anything. i just need a way to know what sheets came from what workbook. now it looks like there is an issue with the line of code in red. I get another run time error:

    The microsoft jet database engine could not find the object 'DecoOpen'. Make sure the object exists and that you spell its name and path name correctly.

    Here is the full code that i am working with.

    [vba]
    Option Compare Database
    Option Explicit

    Private Sub Command0_Click()
    Dim strSheetName As Variant
    Dim strFilePath As String
    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim i As Integer
    Dim connToUpdate As ADODB.Connection
    Dim strSQL As String

    strFilePath = "C:\Documents and Settings\mpearce\My Documents\merged recon files.xls"
    'change strFilePath to filepath to your spreadsheet
    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=" & strFilePath & ";" _
    & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

    'array of all of your worksheet names
    strSheetName = Array("DecoOpen", "DecoClosed", "NotInDeco", "EligibilityNotInHospital", "BillingNotInHospital", _
    "DecoOpen(2)", "DecoClosed(2)", "NotInDeco(2)", "EligibilityNotInHospital(2)", _
    "BillingNotInHospital(2)")
    'creates a connection to your database
    Set connToUpdate = CurrentProject.Connection

    'this loop will go through each worksheet name in the array
    For i = LBound(strSheetName) To UBound(strSheetName)
    Set rst = New ADODB.Recordset
    'create a recordset for the currently selected worksheet
    rst.Open "SELECT * FROM [" & strSheetName(i) & "];", conn, adOpenStatic, adLockOptimistic
    rst.MoveFirst '<<<explicitly move to the first record in the recordset

    'this loop will update your table one record at a time
    'the execute method is one of the fastest ways to update your table
    Do Until rst.EOF '<<<Loop runs until end of recordset is reached
    'this is the SQL Update statement you need to adjust
    strSQL = "INSERT INTO " & strSheetName(i) & "(**Fields to update**) " _
    & "'VALUES (rst![FieldName].Value & " ')"

    'run the SQL statement
    connToUpdate.Execute strSQL
    rst.MoveNext '<<<Move to next record
    Loop

    rst.Close
    Next

    connToUpdate.Close
    rst.Close
    conn.Close
    Set connToUpdate = Nothing
    Set rst = Nothing
    Set conn = Nothing
    End Sub[/vba]

    It seems that sheet name is taking a value.

  12. #32
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by mpearce
    The sheets could really be called anything. i just need a way to know what sheets came from what workbook.
    Good. I'd like to request your worksheet names start with a letter and include only letters, numbers, and underscore characters.

    Meanwhile, I'm curious about a couple points.

    Why do you merge 2 workbooks into 1 before starting this process? Your worksheets all have unique names. So do the tables. We could import from WorkbookA.xls and import again from WorkbookB.xls

    What will you do with the data after importing to Access? Are the DecoOpen and DecoOpen2 tables forever segregated?

    Quote Originally Posted by mpearce
    now it looks like there is an issue with the line of code in red. I get another run time error:

    The microsoft jet database engine could not find the object 'DecoOpen'. Make sure the object exists and that you spell its name and path name correctly.
    [vba]rst.Open "SELECT * FROM [" & strSheetName(i) & "];", conn, adOpenStatic, adLockOptimistic[/vba]
    I ran into a similar issue. Adding a dollar sign before the second square bracket cured that error (but I don't know if that's really Kosher):
    rst.Open "SELECT * FROM [" & strSheetName(i) & "$];", conn, adOpenStatic, adLockOptimistic

    You still have a lot of work left. You need to customize "**Fields to update**" and the VALUES list in the SQL INSERT statement. If your tables each contain dozens of fields, that's a big job. And if your tables have different fields, you need to construct a separate INSERT statement for each unique set of fields --- and match each table/sheet pair to the appropriate INSERT statement. Are you up for that?

    Wouldn't it be easier to just say "grab the fields from this sheet and put them in that table" without the need to explicitly list each and every field by name?

    See if this code works for you:
    [vba]Public Sub mpearce2()
    Dim strFilePath As String
    Dim varSheetNames As Variant
    Dim varSheet As Variant
    Dim cnExcel As ADODB.Connection
    Dim strSQL As String
    Dim strCnString As String

    'strFilePath = CurrentProject.Path & "\" & "Import2Access.xls"
    'this is where your Excel file lives --- change as needed
    strFilePath = "C:\Documents and Settings\mpearce\My Documents\merged recon files.xls"
    Debug.Print "Workbook path name: " & strFilePath

    'set up connection to Excel workbook
    Set cnExcel = New ADODB.Connection
    strCnString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source='" & strFilePath & "';" _
    & "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
    Debug.Print "Connection string: " & strCnString
    cnExcel.Open strCnString

    'array of all of your worksheet names
    varSheetNames = Array("DecoOpen", "DecoClosed", "EligibilityNotInHospital")

    'this loop will go through each worksheet name in the array
    For Each varSheet In varSheetNames

    strSQL = "INSERT INTO [" & varSheet & "] IN '" & CurrentProject.FullName _
    & "' " & "SELECT * FROM [" & varSheet & "$];"
    Debug.Print strSQL
    cnExcel.Execute strSQL

    Next varSheet

    cnExcel.Close
    Set cnExcel = Nothing
    End Sub[/vba]

  13. #33
    the excel workbooks i am working with are generated by another utility. That utility also names the sheets in the workbooks to DecoOpen, etc.

    My company works with hospitals so there is patient information that goes back and forth with account updates. the goal is to compare 2 of these workbooks to see what accounts havent been updated from month to month.

    So my thought was to import the excel information into access and then establish relationships between tables and run join queries to show what accounts havent been updated.

    so i thought by combining the two workbooks together and only having to import 1 workbook would be the best way to do this. I will not be the end user on this utility I the easiest possible way to make this happen. The end users that will be using this are not very tech saavy.

    to summarize i have two workbooks with 5 sheets each. I need to compare DecoOpen in book1 to DecoOpen in book2 to see what patient accounts exist in both workbooks.

    So what ever way is easiest to do that, that is the solution i would like to implement.

  14. #34
    VBAX Master CreganTur's Avatar
    Joined
    Jan 2008
    Location
    Greensboro, NC
    Posts
    1,676
    Location
    I forgot about the $ sign- it is required at the end of the sheet name.

    This code will grab the worksheet name of the active worksheet. It includes any special symbols, like the $, that the name requires. If there's a worksheet name that you can't get to work- save the workbook with that worksheet selected and run this code- it should work.

    [VBA]Dim conn As ADODB.Connection
    Dim rsData As ADODB.Recordset
    Dim strFilePath as String
    Dim strSheetName As String

    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=" & strFilepath & ";" _
    & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
    Set rsData = conn.OpenSchema(adSchemaTables)
    strSheetName = rsData.Fields("TABLE_NAME").Value
    debug.print strSheetName

    rsData.close
    conn.close
    Set rsData = Nothing
    Set conn = Nothing[/VBA]
    -Randy Shea
    I'm a programmer, but I'm also pro-grammar!
    If your issue is resolved, please use Thread Tools to mark your thread as Solved!

    PODA (Professional Office Developers Association) | Certifiable | MOS: Access 2003


  15. #35
    the two workbook's sheet names and column headings are identical. maybe access isnt the best way of doing this. I just need a simple way to look at the account numbers in each sheet in the workbook from the previous month, and compare those account numbers in each sheet of the current month's workbook and have a report created showing those "duplicates".

    let me start off by saying that my company helps people get medical benefits to help pay hospital bills. so lets take the sheet DecoClosed as an example. Here we have a list of patients whose accounts are showing closed in my company's system (meaning that the medical bill has been paid) but open (meaning there is a balance on the account) in the hospital system . If that account appears in book1 and in book2 I need a report showing that. I need a report showing that for each sheet of the two workbooks.

    Thanks so much for all the help. This was over my head to do from scratch. It is nice to know that there are people out there like you all to help with something like this.

  16. #36
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by CreganTur
    I forgot about the $ sign- it is required at the end of the sheet name.

    This code will grab the worksheet name of the active worksheet. It includes any special symbols, like the $, that the name requires. If there's a worksheet name that you can't get to work- save the workbook with that worksheet selected and run this code- it should work.
    Slick! Thanks.

    Your code allowed me to finally see the problem. I created my sample tables and sheets from the first list posted. That list included a space before the opening parenthesis like so: "DecoOpen (2)". Later on, the space disappeared from posted VBA code which loads the array of sheet names. I was using that code, and naturally failing to match with the actual sheets in the workbook. Gack!

    Putting the space back in allowed ADO to find and use the sheets regardless of parantheses. It also didn't care about parentheses in the Access tables names. As long a I had the dollar sign tacked on the end of the name, ADO was happy.

    Incidentally, DoCmd.TransferSpreadsheet was equally happy with the sheet names ADO accepted.

    I'm really sorry about the wild goose chase.

    Hans

  17. #37
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by mpearce
    the two workbook's sheet names and column headings are identical. maybe access isnt the best way of doing this. I just need a simple way to look at the account numbers in each sheet in the workbook from the previous month, and compare those account numbers in each sheet of the current month's workbook and have a report created showing those "duplicates".

    let me start off by saying that my company helps people get medical benefits to help pay hospital bills. so lets take the sheet DecoClosed as an example. Here we have a list of patients whose accounts are showing closed in my company's system (meaning that the medical bill has been paid) but open (meaning there is a balance on the account) in the hospital system . If that account appears in book1 and in book2 I need a report showing that. I need a report showing that for each sheet of the two workbooks.

    Thanks so much for all the help. This was over my head to do from scratch. It is nice to know that there are people out there like you all to help with something like this.
    This is interesting. We were struggling to import Excel data into Access. I suspect that problem may now be resolved, as long a we put space in sheet names where they're needed. However, if you're using Access only to query/report (not modify) the data, you may not need to import it into Access. You could link the Excel sheets to Access and treat them as virtual tables for querying and reporting. When you have a new batch of data to examine, just replace the old workbook with the new one --- it could work fine as long as the names of the workbook, sheets, and fields remain the same. You could skip the workbook merge step as well --- link DecoClosed_yours to DecoClosed in one workbook, and DecoClosed_theirs to DecoClosed in the other workbook.

    What approach will work best for your non-technical users?

    Hans

  18. #38
    hans,

    I am not really familiar with either method, so i really cant tell give an answer on that.

    I mean some of these people have difficulty copying and pasting, or copying an excel sheet to a new workbook.

    Just to give you an idea of intelligence level.

  19. #39
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    447
    Location
    Quote Originally Posted by mpearce
    I am not really familiar with either method, so i really cant tell give an answer on that.

    I mean some of these people have difficulty copying and pasting, or copying an excel sheet to a new workbook.
    OK. Thanks for giving me a clearer look at the big picture.

    How do you want to proceed from here? Have you had a chance to try the mpearce2 subroutine I posted earlier? Or do you prefer the version we were working with before that?

    Hans

  20. #40
    Quote Originally Posted by hansup
    OK. Thanks for giving me a clearer look at the big picture.

    How do you want to proceed from here? Have you had a chance to try the mpearce2 subroutine I posted earlier? Or do you prefer the version we were working with before that?

    Hans
    with regard to the mpearce() routine i get an error saying:

    The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

    I just need to get something up and running for management to use. So I guees I want to proceed with something that can be coded quickly and easily.

    I would like to get either the mpearce() routine working or if not that one than the one before it.

    Thanks again guys.

Posting Permissions

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