Replies

Highlight the block of cells you wish to sum and click on the Autosum icon. this will then add an sum() to the end of your block.

Peter

I think that you need to set the value to NA() rather than “”

Peter

The Word document is a RTF file with a single table in it. It is a propper table not just a formatted list of texrt, with a Header row and aprrox 1500 rows of data.
It is generated as a report from a “Bought in” database that has no other output options.
I currently use the following code but is is unreliable.

Set WordApp = CreateObject(“word.application”)
‘ Use WordApp to access Words objects
With WordApp
.Documents.Open FileName:=strReportPath
.ActiveDocument.Tables(1).Select
.Selection.Copy
End With

Range(“b4”).Select
‘ActiveSheet.Paste
SendKeys “^v”, True
‘ sends ctr-V to paste, as ActiveSheet.Paste pasted differently to pasting by hand
With WordApp
.ActiveDocument.Close
.Quit
End With
Set WordApp = Nothing ‘ clear the reference.

It will work most of the time, but will occasionally paste the document back onitself instead of in Excell!

Not sure about using HTML but I will have a look, I am sure there is no output in that format from the DB but it might be more reliable to automate Word to do it than the copy/paste routine

Thanks

Peter

I can copy and paste OK doing things by hand but I am setting up an automatic system, Click a button in Excel to import the data sort it and report.
It is the copy/paste in code that is causing the problem.
I was hoping that there would be a way of importing/linking to the table directly to avoid the copy/paste bit

Peter

I think this code should help you with your query.
You may need to hack the date format depending where you live ( It works in the UK )

Function funCompleteTime(dateIn As Date) As Date
Const intHourStart As Integer = 7
Const intHourEnd As Integer = 18
Const intTargetTime As Integer = 8

Dim dblRemainingTime As Double
Dim intAddDays As Integer
Dim dateCheck As Date
Dim fDone As Boolean

If Hour(dateIn) > intHourEnd – intTargetTime Then
intAddDays = 1
Do Until fDone = True
dateCheck = dateIn + intAddDays
If WeekDay(dateCheck) = vbSaturday Then
intAddDays = intAddDays + 1
ElseIf WeekDay(dateCheck) = vbSunday Then
intAddDays = intAddDays + 1
ElseIf DLookup(“[dateHoliday]”, “tblHolidayList”, “[dateHoliday] = #” & Format(dateCheck, “dd/mm/yyyy”) & “#”) Then
intAddDays = intAddDays + 1
Else
fDone = True
End If
Loop
dblRemainingTime = intTargetTime – (intHourEnd – ((dateIn – Int(dateIn)) * 24))
funCompleteTime = Int(dateIn) + intAddDays + (intHourStart + dblRemainingTime) / 24
Else
funCompleteTime = dateIn + intTargetTime / 24
End If
End Function

Peter

Just tried it on my (A97)
Works fine if you import the link, not try to link to the link

HTH

and as a P.S. you can always save a macro as a module to get the code

Bat17

Unless you want to store the details for printing out later, it is not so much a case of storing the value as having the query checking the values when it is run.
In the criteria field of the Query grid use the builder to get the value from the combo box on the form, some thing like
[Forms]![frmReportFilter]![cboLineName]

HTH

Peter