 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / October 2006Linking to a spreadsheet
I have a Kitchen Installation management application which currently needs figures manually entered into it. The figures come from an Excel spreadsheet which in itself comes from a template. Each spreadsheet is named after the client but with no specific naming convention other than they always contain the name of the client. I'd like to be able to automated the insertion of the necessary financials from the spreadsheet simply by selecting the spreadsheet from a file list. Is there an easy way of doing this? I may want the database to scan the spreadsheet each time there is a change but more likely will just reselect the spreadsheet manually if the figures change. Cheers Mike If the template is always exactly the same, you can automate Excel and Access. See: http://www.mvps.org/access/modules/mdl0006.htm Use the FileOpen/Save api to open the Excel file, calling it from the code above: http://www.mvps.org/access/api/api0001.htm  SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com >I have a Kitchen Installation management application which currently needs >figures manually entered into it. The figures come from an Excel [quoted text clipped - 11 lines] > > Mike Hi Arvin, Thanks for the reply. However I'm sorry but I can't see how this helps? I can see that one routine allows me to use the File Open/Save dialog box but the other routine doesn't seem to allow me to specify that specific database fields in the client record get populated with data from the named spreadsheet. I need to transfer the contents of 5-8 cells in a specific individually named excel spreadsheet into the client record - this seems a very basic import that I would have thought a supposedly integrated suite of software would have! I'm not a coder so I'd have difficulty modifying the examples given - is there no ready written code or 3rd party app I could use? Cheers Mike > If the template is always exactly the same, you can automate Excel and > Access. See: [quoted text clipped - 20 lines] >> >> Mike Hi Mike: See this part of the Excel automation code: With objActiveWkb .Worksheets(1).Cells(1, 1) = "Hello World" strWhat = .Worksheets(1).Cells(1, 1).value End With strWhat is a variable in an Access code module. The code above writes "Hello World" to cell A1, then reads it into strWhat. No suppose that strWhat is a field in an Access table and we write to a recordset based on that field, or even a textbox on a form bound to that table and we write to that textbox (txtWhat instead of strWhat) on that form. The code demonstrats taking the data from any specific Excel cell and writing it to Access. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > Hi Arvin, Thanks for the reply. However I'm sorry but I can't see how this > helps? I can see that one routine allows me to use the File Open/Save [quoted text clipped - 36 lines] >>> >>> Mike Hi Arvin, Thanks for the explanation. Does the spreadsheet in question have to be open or will it read it from the disk? What I was hoping is that I wouldn't have to have the spreadsheet open as this interferes with the workflow (we usually have 3 or 4 spreadsheets open at the same time as using the database. Is there a detailed description of the variables used or do I have to muddle along? > Hi Mike: > [quoted text clipped - 53 lines] >>>> >>>> Mike As far as I know, it has to be open, but it doesn't have to be visible. I don't believe the code I pointed you to makes it visible, but I'm not sure since I usually use early binding myself. The variables are Dim'd in the early part of the code. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > Hi Arvin, > [quoted text clipped - 64 lines] >>>>> >>>>> Mike |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.