I’m having a problem pushing some cell results to Access

[BThresher]

Good morning


I’m having a problem pushing some cell results to Access.

When I hit F9 to refresh the cells in Excel, several cells change results. I would like to add these entries into an Access DB every time they change. Preferably, every time I hit F9. Right now, in Access I have a macro that can pull named cells into a new table. So I have to hit F9 in Excel, shift to Access, run the macro, and then cut and paste results into the proper table. I have the feeling I might just be doing this the hard way.

Any pointers or suggestions?


Thanks in advance,

Bob

 

[Luke M]

As it's easier to get data into Access when working from Access, I would recommend modifying the Access macro to recalculate the worksheet (what you're doing when you hit F9) and then have it pull in the data. Admittedly, I'm a little weak in Access VB, but the psuedocode would look like


Sub GetData()

Workbooks("My Book.xls").Open

ActiveWorkbook.Calculate

GetData 'Somehow??

End Sub

 

[BThresher]

Not sure how to use the GetData, the one I have now uses ImportExportSpreadsheet

here's a copy.


<Action Name="ImportExportSpreadsheet">

<Argument Name="TableName">'Craft_Run'</Argument>

<Argument Name="FileName">D:DropboxGameBrew_Craft_Try1.xlsx

</Argument><Argument Name="Range">brew_results</Argument>

</Action>

</Statements>

</UserInterfaceMacro></UserInterfaceMacros>

 

[SirJB7]

Hi, BThreser!


As Jack said, let's go by parts.


Part 1: Excel side - dummy workbook


a) Create a workbook named "ExportedFile.xlsx"

b) Rename first sheet to "ExportedData" and delete any others

c) Define a named range called "ExportedRange" referred to cell A1


Part 2: Excel side - source workbook


a) Set calculation mode to manual

b) Define a named range "RangeForExport"

c) Place this code in an added module

-----

[pre]

Sub ExportedFile()
' constants
' declarations
Dim sExportedFile As String
' start
Application.EnableEvents = False
sExportedFile = "ExportedFile.xlsx"
' process
Workbooks.Open sExportedFile, False
With Workbooks(sExportedFile)
.ActiveSheet.Cells.ClearContents
ThisWorkbook.Activate
Range("RangeForExport").Copy
.Worksheets("ExportedData").Range("ExportedRange").PasteSpecial xlPasteValues
.Save
.Close
End With
' end
Application.EnableEvents = True
End Sub
-----

c) Place this code in the VBA section of proper worksheet

-----

Private Sub Worksheet_Calculate()
ExportToExportedFile
ActiveWorkbook.Save
End Sub

[/pre]
-----


So each time your sheet get recalculated, it values (not formulas) will be copied into the dummy workbook defined in the sExportedFile variable (can change it to anything but remember renaming dummy file properly), and source workbook will be saved.


Part 3: Access side - linked table


a) Go to External Data, Excel

b) In the Source Data filename dialog, search for "ExportedFile.xlsx"

c) Select the third option (Link to source data creating a Linked Table)

d) Press Accept and the range "ExportedData" should appear

e) Press Next, then check that FirstRowsHasColumnHeaders is on, Next again

f) Enter "ImportedData" as name for the linked table and press End


This should link your exported data from Excel to a linked table in Access.


Part 4: Both sides - automating the process


I think about something like DDE between Excel & Access, or a timed procedure in Access to check new versions of "ExportedFile.xlsx", for example another exported file (maybe same name .txt) with the date&time of export... Give me a couple of days and let's see what arises.


In the meantime, feel free to ask anything.


Regards!

 

[SirJB7]

Hi, BThresher!


Update to Part 3: Access side - linked table


To avoid sharing issues with the workbook "ExportedFile.xlsx" while linked table "ImportedData" is opened within Access, let's add this to previous procedure:


f) Create a table called "ImportedDataFinal" with the same design as "ImportedData"

g) Create a query called "ImportedDataFinalQueryDelete" with:

DELETE *

FROM ImportedDataFinal;

h) Create a query called "ImportedDataFinalQueryInsertInto" with:

INSERT INTO ImportedDataFinal

SELECT *

FROM ImportedData;


In this way you'll have your actual data in the new created table and you'll get rid of opening issues of file "ExportedFile.xlsx" from within the original source workbook in Excel because of Access constraints while related objects opened.


See you when anything for Part 4 comes into light.


Regards!