Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Problems on Importing MS Excel Sperasheets to MS Access 2010

    The attached zipped files contain a MS access 2010 db. with two MS Excel spreadsheets imported into it. They are in the db. and should be visible when it is unzipped. The two zipped spreadsheet files are also attached to the this post.

    One spreadsheet file is an example from a big box store on items which includes thousands of records. Using the VBA code in the db. and the command button it imports quite easily. When it is in the db. one can see meaningful category names have transferred over from the Excel spreadsheet to the MS Access table. This is what I want.



    Now when I tried to out in the production tracker spreadsheet it went over well enough, but there was a problem. The category names are certainly not meaningful. The names are serialized like F1, F2 ,F3 and so on. Also. it calls everything text. I do not think so.

    This make no sense. I code check the box or change the VBA code to remind the software that the first row of the spreadsheet, Production Tracker, was only category names not data. In fact I used the same VBA that I used when I imported the first spreadsheet (and that one of course gave much better results).

    How do I change things so I get the meaningful name of the categories in the Production Tracker import into MS Excel, like it did in the first spreadsheet.

    How to modify VBA code of Production Tracker Spreadsheet?

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Remember that Access interprets the first row of the spreadhseet as containing the field names (if you select that option)?

    Now take a look at your spreadsheet "Production Tracker". Does the first row contain the field names? No, I don't think it does - it has Phase 1, Phase 2... The field names you want are (I think) in Row 5. Make a copy of that Excel file, and then make these changes:

    Delete rows 1 - 4 (not just the data - delete the rows completely. Row 5 is now Row 1.
    Change the Title of Column A to ID. Access might complain about the "#"
    Columns AC and AM have the same title (Complete), which causes an error. Change them to unique titles.
    Add a title for Column AN - it is currently blank. (I made it "Original")
    Delete row 4 - it is blank. (did you notice row 3 is there but not really visible, and it is not blank)
    Access thinks there is a blank column after all the data (Column BB). You can delete this column. If you don't, Access will generate an 'error' message about an illegal field name.

    If you need to, you can adjust the column names here in Excel, in in Access - it doesn't matter.

    I tried it with these changes, and the import worked fine (using the form). If you get it to import, you'll probably have the change the data types of some of the fields, when Access had to guess what the dagtas types were.

  3. #3
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Thanks so very much for your help. My only drawback in deleting the rows is that they must be there for a reason. I guess deleting them is one way to get this system to work. Shouldn't they be be put back in at one point? It just seems that what you say will work, but what the rows that are deleted.

    What about them?

    There is a way to tell the software that the first row is for header titles, but is there away to tell the software that may the second, and third (or more) rows are also for header titles?

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You can't have it both ways. Yes, you can keep those rows in there, but if you want Access to generate the field names automatically, the row that contains the values you want for your field names must be the first row, so you will have to move it there. The other effect that will have is to create all or most of the fields in the imported Access table as Text, because the import process looks at existing data in the Excel spreadsheet to determine what data types to use, and since the existing data (in the second and following "headers") is text, that's what Access uses. Changing the types manually later will probably give you an error because Access finds text in what you want to be a numeric field.

    You have to remember that Access is not the same as Excel, and doesn't work the same way. Access doesn't even have the concept of "header rows". Tables contain data, and only data.

    Don't expect to be able to import an Excel file and then have Access emulate what Excel does, because for the most part, you can't.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Excel is spreadsheet system - in spreadsheet, in every cell you can have whatever you want. Multiple headers, date in one cell and text in cell below it, etc.

    Acess is a database. Database keeps data in tables. A table is a container for data, where data of same type are kept in Fields. You can't store different type of data in same field. Every field has a header - a field name unique for this field in this table. A Field can not have more names than one.

    In case you want to display some additional information about table fields, you can create an additional table, and define this additional information there (like tblTableAliases: ID, TableName, FieldName, AliasTyp, AliasText) - and to use it later p.e. in reports or in forms. You can't use them in queries of-course.

  6. #6
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, I understand .Thanks for your input. Greatly appreciated. Now I would like to automate the steps listed in Post #2. The end user of this is not going to be me. It is going to
    be someone else. They will just want to get things done quickly. and easily.

    I think the easiest way is by VBA. I am afraid of using macros. I program in VBA in Access and I guess it is similar to program in VBA in Excel.

    Is this the best way and I assume all the operations must be done in Excel before importing to Access?

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Now I would like to automate the steps listed in Post #2.
    Is this going to be a recurring requirement, or a one-time thing? If it is a one-time thing, then IMO it is not worth the effort to write VBA code (in Access or Excel) to do it. Yes, it is possible to write VBA code to do it, but it is not trivial, and requires a good knowledge of how Excel references and does things. Far better (IMO) is to give the users those instructions (which don't require programming) and tell them that is what they must do before the import.

    I assume all the operations must be done in Excel before importing to Access?
    In this case, yes, because the current structure of the Excel file does not allow a proper import (e.g. the field names)

    Just as an aside -

    Obviously I don't know how the data will be used in Access, but as is characteristic of spreadsheets, the data is not properly normalized. This might cause problems along the way, so just be aware of it.

  8. #8
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I think t will be justified to write the code in VBA. It will be a recurring requirement.

    Now to take the steps :

    Delete rows 1-4.

    That would be done by using the VBA code

    Sub DeleteRows()
    Rows(1:4).Delete
    End Sub

    Change the title of Column A toID

    Sub ChangeColTitle()
    TitleColumnNames(A) = ID
    End Sub

    Change the Column names for for AC and AM.

    Sub ChangeColTitle2()
    TitleColumnNames(AC) = Complete1
    TitleColumnNames(AM) = Complete2
    End Sub


    Change the column name for Column AN, it is currently blank. Change it to "Original"

    Sub ChangeColTitle3()
    TitleColumnNames(AN) = Original
    End Sub

    Delete row 4 - it is blank. I assume that the is a new row 4. In the initial step I deleted rows 1 to 4.

    Again I will jut use the code:

    Sub DeleteRows2()
    Rows(4:4).Delete
    End Sub


    Now I do not know how to do the last part where i delete column BB. You say that Access thinks that there is a blank column. I am just not sure
    how to delete column BB.

    A guess the VBA code code look like:

    Sub DeleteColumn()
    Column(BB).Delete
    End Sub

    it is just guess.

    I know that I could combine some of these sub to make things less complicated. i was just think
    how to do this and elegance was not my goal. Getting it done was.

    Also, I am unsure how to trigger these Subs.

    It seems they could be activated in a way similar to the one used in MS Access.

    Finally, it says to press ALT-F11 to get to the software area to put in the VBA code. I forgot the name.

    If I do that on my Lenovo all it does is change the screen brightness. What is the alternative?

    Any help appreciated. Thanks in advance.


    Respectfully,


    Lou Reed

  9. #9
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I assume you are going to put this in the Excel spreadsheet as a macro or series of macros (it could easily all be in be in one).

    First, an .xlsx Excel file cannot contain macros - it has to be an .xlsm file. Open the .xlsx file, then do a File - Save As , and change the type to .xlsm.

    To create/edit/run macros in Excel, select View - Macros - View Macros. To create a new macro, enter a new macro name, then click Create. You will then see the familiar VBA editing window, where you can paste in your code.

    Read this article: https://msdn.microsoft.com/en-us/lib...ffice.12).aspx
    for how to refer to Cells, Rows and Columns using the A1 notation. None of your code above will compile due to syntax errors.

    Lastly, don't be confused by the term "Macro" in Excel. What we call a Sub in Access is called a Macro in Excel.

    Let us know how it goes.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I ran across this a while ago, but I haven't used it/experimented with it.
    Ken Snell's site:
    Read Data from EXCEL File via Query (SQL Statement)
    http://www.accessmvp.com/kdsnell/EXC...ort.htm#ImpSQL


    Create a new query, switch to SQL view and paste in the following:
    Code:
    SELECT T1.*, 1 AS SheetSource FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\Forum\LouReed\Sept21\Production Tracker.xlsx].[Expanded Tracker$A1:BA8] as T1;
    Change the RED to the full path to the workbook.
    Change the BLUE to the ending cell - I used BA8, since there were only 8 rows in the sample workbook.
    Save the query.
    Open the query. (Bonus:The "field names" of the query match the table field names.)

    I would use VBA to open the query in a record set, then loop through the rows in the query and loop through the columns, updating the table.

    You could validate the data, forcing the data to be the proper type.


    Just a thought........

  11. #11
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hey Steve -

    I'd never seen that - gave it a try and it works fine. For me, it still gives query field names as F1, F2,.... because of the blanks in the first row, but that's not unexpected, I think.

    Thanks for the pointer.

    John

  12. #12
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    You lost me. I know how to use VBA to run a query in SQL, but I do not know how to open a query in a record set.

    Please explain.

    Thanks in advance.

    Respectfully,

    Lou Reed

  13. #13
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    This SQL query, that you wrote:

    Code:
    SELECT T1.*, 1 AS SheetSource FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\Forum\LouReed\Sept21\Production Tracker.xlsx].[Expanded Tracker$A1:BA8] as T1;
    Opens up the spreadsheet source and gives it some parameters.

    Now to loop through the rows and loop through the columns, do I do that in VBA or SQL?

    I am not sure what to do next. As I said above, I am unfamiliar working with recordsets.

    When it comes times to eliminate row 1-4 on down ... delete row 4 should that be done in SQL.

    All of the modifications of the Excel spreadsheet must be done before importing it to Access. If I do not do that then it will not import into Access correctly.

    That implies that I must use SQL. I cannot use VBA (at least VBA for MS Access) until after I import the Excel spreadsheet.

    So I must use SQL on the Excel spreadsheet before (or while) it is imported. Is this correct?

    Sorry, if the questions sound dumb. I am quite to importing Excel into Access.

    Any help appreciated. thanks in advance.


    Respectfully,


    Lou Reed

  14. #14
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    All of the modifications of the Excel spreadsheet must be done before importing it to Access. If I do not do that then it will not import into Access correctly.
    That implies that I must use SQL.
    No, not necessarily. You would use VBA in Excel (VBA stands for Visual Basic for Applications). You have the right idea in your post #8 (but incorrect syntax), look at the link I gave you in Post #9 to get an idea of what the code should look like, my notes on how to get the code into an Excel macro.

    Hint: all the required mods to the spreadsheet can be done in one Excel macro, with 7 or 8 lines of code.

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Comments inline:

    This SQL query, that you wrote:
    Code:
    SELECT T1.*, 1 AS SheetSource FROM [Excel 8.0;HDR=YES;IMEX=1;Database=C:\Forum\LouReed\Sept21\Production Tracker.xlsx].[Expanded Tracker$A1:BA8] as T1;
    Opens up the spreadsheet source and gives it some parameters.
    Nope, doesn't give anything parameters. Are you referring to the connection string?

    Now to loop through the rows and loop through the columns, do I do that in VBA or SQL?
    VBA.

    I am not sure what to do next. As I said above, I am unfamiliar working with recordsets.

    When it comes times to eliminate row 1-4 on down ... delete row 4 should that be done in SQL.
    No.

    All of the modifications of the Excel spreadsheet must be done before importing it to Access. If I do not do that then it will not import into Access correctly.
    Not necessarily. Using the query (SELECT T1.*, 1 AS SheetSource FROM ........) you could use VBA to loop through the record set and append/update records.

    That implies that I must use SQL. I cannot use VBA (at least VBA for MS Access) until after I import the Excel spreadsheet.
    Wrong again. You could:
    1) execute the above query in Access to get a record set, then use VBA to manipulate records (append/update).
    2) Or you could use automation (VBA) to edit the spreadsheet before importing - you could delete the first 4 rows, insert column names, etc, save the spreadsheet, THEN do the import.

    So I must use SQL on the Excel spreadsheet before (or while) it is imported. Is this correct?
    In a sense. Or you could link to the spreadsheet (the spreadsheet looks like a table), then use SQL to open a query based on the linked spreadsheet and use VBA to be able to manipulate the records.



    A lot of what you are trying to do depends on the stability of the spreadsheet design. Will the number of columns change or always a constant layout?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 12-29-2015, 10:41 PM
  2. Replies: 9
    Last Post: 11-20-2013, 03:16 PM
  3. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  4. Replies: 3
    Last Post: 10-04-2012, 11:38 AM
  5. Importing problems from Excel
    By ChrisNWV in forum Import/Export Data
    Replies: 3
    Last Post: 07-24-2012, 11:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums