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:
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?
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()
Can anyone offer some advice on how best to perform these tasks along the lines I'm heading, but without the 'disappearing' spreadsheet?