Solved Open Excel From Access & Update Range

[Jason Lee Hayes]

I have created an Excel 2016 spreadsheet to represent some statistics. (My Excel skills are VERY limited)
It contains 4 rows which represent numbers and when i adjust these numbers the graphs to the right adjust accordingly..
The numbers i wish to populate from an Access Table

My idea is that i have my MS Access Dbase open & when i click a button on my access form it opens the Excel sheet in the background, populates the columns using .range command and rows automatically then refreshed in Excel sheet.

Some things:-

I am using Office 2016 environment & If i open the Excel sheet directly; it opens and i can change the figures which then change the graphs.

I have a reference set to MS ExceI 16 Object Library set & did have this part working in that if i opened the excel sheet and made the changes then try viewing it would crash I got round this by opening the excel sheet, making the changes, closing the excel file then opening for viewing

Here is my code which i have pieced together which did sort of work:-

' open excel and update cell values using .range method

Dim appExcel As Excel.Application
Dim wbook As Excel.Workbook
Dim wsheet As Excel.Worksheet

17030 Set appExcel = New Excel.Application
17040 appExcel.Visible = False
17050 Set wbook = appExcel.Workbooks.Open("C:\Paradox CCTV Support DataBase\InViewDashboard.xlsm")
17060 Set wsheet = wbook.Worksheets("Sheet1")

wbook.Application.DisplayAlerts = True
wbook.Application.IgnoreRemoteRequests = True

With wsheet

' example data used to populate row value

.Range("B9").value = "SC 0001" ' Site Card ID
.Range("C9").value = "10" ' Faults Reported
.Range("D9").value = "5" ' Faults Resolved
.Range("E9").value = "5" ' Faults Outstanding
.Range("F9").value = "5" ' Days Took

.Range("B10").value = "SC 0002" ' Site Card ID
.Range("C10").value = "12" ' Faults Reported
.Range("D10").value = "3" ' Faults Resolved
.Range("E10").value = "9" ' Faults Outstanding
.Range("F10").value = "7" ' Days Took

End With

17070 wbook.Save

17080 Set wsheet = Nothing

17090 wbook.Close True

17100 Set wbook = Nothing
17110 Set appExcel = Nothing

'open file again in visible mode

17120 Set appExcel = New Excel.Application
17130 appExcel.Visible = True
17140 Set wbook = appExcel.Workbooks.Open("C:\Paradox CCTV Support DataBase\InViewDashboard.xlsx")
17150 Set wsheet = wbook.Worksheets("Sheet1")

End Sub


I don't expect the work to be done for me as i like to learn and understand myself but i would appreciate if someone who knows Access VBA & Excel to quickly look over the code and suggest and changes/fixes...

Thanks in advance

Regards,

Jason

Sorry if I've posted in wrong place, not sure if Access or Excel related (Or both)

 

[theDBguy]

Hi. Just a thought, could you maybe link the data in Excel to your Access table?

 

[Gasman]

I don't think you want your numbers as strings?

 

[Jason Lee Hayes]

Hi,

I believe what you are suggesting is probably the right way to go but my access skills are limited as well.

I have a table in Access which retains some basic stats about the site, fault description, reported by etc but more importantly has the following field :-

Site Name
Date Reported
Date Complete

I then generate a report (See Attached Example from a query where the condition "Date Complete" is not null or blank
Within the report i then calculate the days took to complete from the "Date Reported" & "Date Completed" Value which gives me the "Days Taken To Complete" using the following:-

=DateDiff("d",[Date Reported],[Date Completed])

On my report i Group by Site Name which then enables me to add up how many faults are completed per matching "Site Name" before representing on the report.

I am not sure how to create or modify the existing table to calculate the 6 statistics needed to populate the Excel Cells outside of a report..

which is site name, faults per site, faults fixed, not fixed and days taken..

Yes... I have alot to learn lol

 

[Jason Lee Hayes]

I don't think you want your numbers as strings?

I have looked at the cell property in the Excel Spreadsheet and where i have numbers that i wish to change the cell property is set to General. Is this what you mean?

 

[Gasman]

I meant that you appear to be inserting strings, "5" instead of just 5 ?
Perhaps Excel is getting clever? if it does not cause you a problem, but a bad habit to get into, I would think?

 

[Jason Lee Hayes]

I meant that you appear to be inserting strings, "5" instead of just 5 ?
Perhaps Excel is getting clever? if it does not cause you a problem, but a bad habit to get into, I would think?

Thanks for you reply, i see - so in order to insert the numeric the line would be something like:-

.Range("C10").value = 12 as oppose to .Range("C10").value = "12"

Regards,

 

[Gasman]

That is what I would use, yes

 

[Jason Lee Hayes]

Just an update, I've been stepping backwards to prove some things..
Didn't realise the excel file was changed to excel with macro so the extension had changed from xlsx to xlsm hence why not opening..
So; the following code works all the time in that when i click a button the excel sheet now opens yey:-

17120 Set appExcel = New Excel.Application
17130 appExcel.Visible = True
17140 Set wbook = appExcel.Workbooks.Open("C:\Paradox CCTV Support DataBase\InViewDashboard.xlsx")
17150 Set wsheet = wbook.Worksheets("Sheet1")

Great; the issue i may need to fix here is i need to check the excel file is open and if so then do nothing rather than open another instance in read only... I should be able to Google some info on this...

So; what is confusing to me is how you refer to workbook(s) and worksheet(s) from VBA.. I've seen references to sheet indexes as well.
My Excel Sheet will have only a single worksheet and i assume that the name of the worksheet is as labelled in the properties of the sheet (See Picture_

In this instance would i be correct in saying the Worksheet is called "Sheet1" There is also an entry called "Thisworkbook"? Is this the name of the workbook?

Thanks,

Jason

 

[Jason Lee Hayes]

Hi,

Just a further update.... I have worked it out...

The code now opens my Excel File and changes the Cell Values:-

Private Sub btnShowDash_Click() ' Show/Update Dashboard

' Microsoft Excel 16.0 Object Library Reference Needed

Dim appExcel As Excel.Application
Set appExcel = New Excel.Application
appExcel.Visible = True
Set wbook = appExcel.Workbooks.Open("C:\Paradox CCTV Support DataBase\InViewDashboard.xlsx")
Set wsheet = wbook.Worksheets("Sheet1")

With wsheet
'
.Range("B5").value = "SC9999" ' Site Card ID
.Range("C5").value = "TEST NAME" ' Site Name
.Range("D5").value = "99" ' Faults Reported
End With

wbook.Save
End Sub

Used the following function to detect if already open or not which works well:-

Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long

On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0

Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function

 

[Gasman]

Thanks for that function.
I was not sure it would work correctly as I thought next statement might clear the err code?, but it appears not to.

 

[Jason Lee Hayes]

Thanks for that function.
I was not sure it would work correctly as I thought next statement might clear the err code?, but it appears not to.

I call the function like this:-

Dim Ret

Ret = IsWorkBookOpen("C:\Paradox CCTV Support DataBase\InViewDashboard.xlsx")

If Ret = True Then
MsgBox "Dashboard is already open!"
Exit Sub
Else
MsgBox "Dashboard Now Opening"
End If

 

[Gasman]

Yes, I know how you would use it, I was just not sure the Close would reset the error number to 0.?