none
Data from excel into Access RRS feed

  • Question

  • Hey guys, what I've got going is this... I've got a macro to open excel, run a macro within excel to calculate the data required, and then save/close the file. Then it imports the data off of a specific sheet into a table in access. What I'm trying to figure out is how I can get a value to kick back out of excel containing a "CountA" to tell me how many columns exist for import. The spreadsheet is of a dynamic design, and varies in columns depending on how many products are calculated (pulls data from a raw data excel sheet out of another program.). I've got to figure out how to get a count value back into access from excel without doing multiple imports (time restraints). All of this has been VBA coded. Any ideas would be appreciated. My version of office is 2007.
    Friday, August 14, 2015 8:32 PM

Answers

  • Why not just import the data into Access and calculate and then return the count of records filtered by your criteria. The only thing you really need Excel for is to provide the data...

    Really since you are outputting from another file anyway it can send as a .csv file which you can import directly into a new blank Table and modify and calculate and count. Why put Excel in the middle at all.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    Saturday, August 15, 2015 4:17 AM
  • This setup sounds kind of weird to me.  Nevertheless, you can certainly import data from a specific cell, into Access, from Excel.  Please see this link for an idea of how to proceed.

    http://www.access-programmers.co.uk/forums/showthread.php?t=212031


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, August 17, 2015 12:33 AM

All replies

  • Why not just import the data into Access and calculate and then return the count of records filtered by your criteria. The only thing you really need Excel for is to provide the data...

    Really since you are outputting from another file anyway it can send as a .csv file which you can import directly into a new blank Table and modify and calculate and count. Why put Excel in the middle at all.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012


    Saturday, August 15, 2015 4:17 AM
  • This setup sounds kind of weird to me.  Nevertheless, you can certainly import data from a specific cell, into Access, from Excel.  Please see this link for an idea of how to proceed.

    http://www.access-programmers.co.uk/forums/showthread.php?t=212031


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, August 17, 2015 12:33 AM
  • Darkviper84,  Have you tried linking the Excel sheet to the Access database.  When the Excel sheet is modified (new column added) those changes are reflected in the Access linked table automatically.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Monday, August 17, 2015 4:04 PM