Access VBA & Excel spreadsheets

jamsta

Registered User.
Local time
Today, 13:30
Joined
Oct 29, 2009
Messages
26
Hi all,

I am trying to learn how to read from and write to specific cells in an Excel spreadsheet, from a VBA module in Access. This is my first try at this and I am currently playing around with some code and ideas.

All it does so far is run a sub to tell me what's in a particular cell in the spreadsheet, and then run another sub to write to a particular cell and then tell me what's in that too. The second sub references the cell at an intersection of two named ranges (probably not relevant).

Everything works as I expect it should, except that when I then open the actual spreadsheet, none of the worksheets are visible. I only know the file has actually opened because when I go to close down Excel I get the standard "Do you want to save changes to..." message. Other than not being able to see it, the file seems to be in tact.

Here is my code:

Code:
Option Compare Database

Dim xl As Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook

Dim strDBPathAndFile As String

Sub main() 'main sub

    Call connectToSpreadsheet
    Call readSomeCells
    Call writeSomeCells
    Call disconnectFromSpreadsheet
    
End Sub 'end main()

Sub connectToSpreadsheet() 'Connect to the spreadsheet.

    strDBPathAndFile = CurrentProject.Path
    Set xl = CreateObject("Excel.Application")
    Set xlWrkBk = GetObject(strDBPathAndFile & "/TstSpreadsht.xlsm")
       
    Set xlsht = xlWrkBk.Worksheets("Input_Variables")
    
End Sub 'End connectToSpreadsheet()

Sub readSomeCells()
    Dim a As Integer
    a = xlsht.Range("C7")
    MsgBox "It's " & a
End Sub 'end readSomeCells

Sub writeSomeCells()
    Dim nm As String
    nm = "Inp_WE_30_07_2010 ResILT"
    xlsht.Range(nm).Value = 805
    Dim b As Integer
    b = xlsht.Range(nm)
    MsgBox "It's " & b
'    xlWrkBk.Save
End Sub 'end writeSomeCells()

Sub disconnectFromSpreadsheet() 
'    xlWrkBk.Close
    Set xl = Nothing
    Set xlWrkBk = Nothing
    Set xlsht = Nothing
    
End Sub 'enddisconnectFromSpreadsheet()
I suspect my problem may be with the 'disconnectFromSpreadsheet' sub, as this may not be disconnecting at all. I'm having trouble conceptualizing connecting / disconnecting.

Can anyone offer some advice on how best to perform these tasks along the lines I'm heading, but without the 'disappearing' spreadsheet?
 
Thanks for the link. I can't find anything there relating to my specific issue but I've bookmarked it because it's obviously a useful resource.

I've been tinkering some more. The version of the code I posted is misleading because the offending line is commented out. The problem seems to be where I used xlWrkBk.Save. If the spreadsheet isn't actually open when I run my code, it's this saving action that seems to make the spreadsheet disappear. If the spreadsheet is open, the save command works fine. Is there a way of updating the spreadsheet and saving without actually having it open?
 
Try going through that link again and re-read the excel code and you will see what you're doing wrong or is not advisable.

1. The declaration of xl should be Object
2. Instead of using GetObject use xl.Workbooks.Open(filepath)
3. Not sure what you're doing here:
Code:
    nm = [COLOR=Red][B]"Inp_WE_30_07_2010 ResILT"[/B][/COLOR]
    xlsht.Range([COLOR=Red][B]nm[/B][/COLOR]).Value = 805

You're code writing style looks like you've done some Java?
 
OK, thanks for that - I'll have another look and take your tips on board.

Yes Java was the first language I tinkered with - so it seems I speak VBA with a Java accent!
 
Haha! You were using names like "main" and you seemed to have functions split up like class methods. Oh also I noticed you were calling each procedure like a real procedural language :) I used to code in Java.

Let us know how you get on.
 
Thanks again for the advice. Delayed reply... but I've looked at the snippets now and I've got this working how I want it to work.

However.... related point: Is it possible to run some some code in a module in Access, from Excel?

I've got data in a table that's maniupulated by some code in Access and then outputted to a spreadsheet. I'd like it if the user could open the spreadsheet, enter a couple of parameters and run a macro from there that will call the Access code.

Ideally, the user would not see the access DB open (if it even has to open in the conventional sense), and it would close at the end leaving them with the populated spreadsheet.

Any pointers, gratefully received as always.

Jamsta.
 
I believe you can. Just as you would create an instance of Excel from Access you can also create an instance of Access from Excel and call the relevant PUBLIC function. I can't remember what the security implications are but if there is then you could create a macro that runs the function, then run the macro instead.
 
That makes sense, but I'm struggling with exactly how to do it. I have this in my Excel code:

Code:
   Dim oApp As Object
    Dim LPath As String
    
    'Path to Access database
    LPath = "C:\Users\My Documents\My_DB.mdb"
    
    'Open Access
    Set oApp = CreateObject("Access.Application")
    oApp.Visible = True
    
    'Open Access database as defined by LPath variable
    oApp.OpenCurrentDatabase LPath

This opens My_DB.mdb. I'm not sure what syntax I then have to use in the Excel code to run the sub in the module in the DB.
 
I believe it's something like:

oApp.run "Name of Function"

If you have parameters, you can include them also:

oApp.run "Name Of Function", "Param 1", "Param 2", ...etc
 
You've nudged me right into the solution - that works a treat. Thanks again.
 
Haha! I'm probably going to take it up next year including French :)
 

Users who are viewing this thread

Back
Top Bottom