Linking to a spreadsheet

[Mike Langensiepen]

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

 

[Arvin Meyer [MVP]]

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

 

[Mike Lang]

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

 

[Arvin Meyer [MVP]]

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Mike Langensiepen]

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?

 

[Arvin Meyer [MVP]]

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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com