Tirelle,
I tried to clean it up and it may even run (and it may not).
There is lots more that could be done, but I lost interest...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

'--
Public Function AutomateExcel(ChargeEntry As Boolean, strBookName As String, _
intNumSheets As Integer) As Boolean
'This function create a workbook for importing digital hydrometer data.
'A separate workshheet for each hydrometeris created. Data is imported for each hydrometer.
Dim SheetCtr As Integer
Dim HydrometerCount As Integer
Dim strImportingFrom As String
Dim xlsHydrometerImport As Excel.Workbook
Dim xlsHydrometerSheet As Excel.Worksheet
Dim xlApp As Excel.Application
Dim ImportFromHydrometers As VbMsgBoxResult
'Dim rng1 As Range
'Dim rng2 As Range
'Dim rng3 As Range

Const TimePerHydrometerImport As Integer = 2000
Const TimePerLogSheet = 2000

On Error GoTo CreateNew_Err
If ChargeEntry Then strBookName = "Charge_" & strBookName & "_SpecificGravities"

Set xlApp = New Excel.Application
xlApp.SheetsInNewWorkbook = intNumSheets
xlApp.Visible = True

Set xlsHydrometerImport = xlApp.Workbooks.Add
xlApp.AddIns("AP-SoftPrint").Installed = True

With xlsHydrometerImport
For Each xlsHydrometerSheet In .Worksheets
xlsHydrometerSheet.Name = "Hydrometer No. " & Right(xlsHydrometerSheet.Name, 1)
' ShowProgress 500, "Creating Hydrometer No. " & Right(xlsHydrometerSheet.Name, 1), _
"Creating Import Sheets. . . . . ." 'ACCESS ?
xlsHydrometerSheet.Range("A2", "I2").Font.Bold = True
xlsHydrometerSheet.Range("A2", "I2").MergeCells = True
xlsHydrometerSheet.Range("A2", "I2").Value = "Digital Hydrometer Imports"

xlsHydrometerSheet.Range("A4", "C4").Font.Bold = True
xlsHydrometerSheet.Range("A4", "C4").MergeCells = True
xlsHydrometerSheet.Range("A4", "C4").Value = "Import Date and Time:"

xlsHydrometerSheet.Range("A6", "B6").Font.Bold = True
xlsHydrometerSheet.Range("A6", "B6").MergeCells = True
xlsHydrometerSheet.Range("A6", "B6").Value = "Imported:"

xlsHydrometerSheet.Range("E6", "F6").Font.Bold = True
xlsHydrometerSheet.Range("E6", "F6").MergeCells = True
xlsHydrometerSheet.Range("E6", "F6").Value = "Formatted:"

xlsHydrometerSheet.Range("D4", "E4").Font.Bold = True
xlsHydrometerSheet.Range("D4", "E4").MergeCells = True

xlsHydrometerSheet.Range("E7").Font.Bold = True
xlsHydrometerSheet.Range("E7").Value = "Cell"
xlsHydrometerSheet.Range("F7").Font.Bold = True
xlsHydrometerSheet.Range("F7").Value = "S.G."
xlsHydrometerSheet.Range("A7").Font.Bold = True
xlsHydrometerSheet.Range("A7").Value = "Sample"
xlsHydrometerSheet.Range("B7").Font.Bold = True
xlsHydrometerSheet.Range("B7").Value = "S.G."
' DoCmd.Close acForm, "frmProgressbar", acSaveNo
Next xlsHydrometerSheet

' .SaveAs DLookup("HydrometerLocation", "qryImportFunctions") & "\" & strBookName 'ACCESS ?
strBookName = xlsHydrometerImport.FullName
End With

For HydrometerCount = 1 To intNumSheets
'Code to simulate an import
ImportFromHydrometers = VBA.MsgBox("1. Connect Digital Hydrometer No. " & _
HydrometerCount & " " & vbCrLf & "2. Ensure Hydrometer Is Turned ON. " _
& vbCrLf & "3. Press OK. ", vbOKCancel, "Import From Hydrometers")

If ImportFromHydrometers = vbOK Then
Dim strSuffix As String 'str is already used by Excel
strSuffix = "\AP-SoftPrint.xla"
' xlApp.Workbooks.Open (xlApp.LibraryPath & strSuffix) '<<< Installing it opens it!
xlApp.OnTime Now(), ("AP-SoftPrint.xla!startcollection"), Now() + 1
xlApp.SendKeys "{~}", True
xlApp.OnTime Now(), ("AP-SoftPrint.xla!endcollection"), Now() + 1
' Excel.CommandBars.ActionControl.OnAction
xlApp.SendKeys "{~}", True

'Set xlsHydrometerSheet = Worksheets.Add
' With xlsHydrometerSheet
' .Name = "measuring data " & HydrometerCount
' strImportingFrom = .Name
'End With
End If
'FormatHydrometerImport strBookName, str(HydrometerCount),strImportingFrom
Next HydrometerCount

xlsHydrometerImport.Close SaveChanges:=True
Set xlsHydrometerSheet = Nothing
Set xlsHydrometerImport = Nothing
xlApp.Quit
Set xlApp = Nothing
CreateNew_End:
Exit Function
CreateNew_Err:
Debug.Print Err.Number & " " & Err.Description
xlsHydrometerImport.Close False
Resume CreateNew_End
End Function
'---------------------


"Tirelle"
wrote in message
First of all...Happy New Year to All !!!!! I posted this in ac Access group
as well and it was suggested that you are much moer knowledgeable on th Excel
Object... So Here we go...

Here are the details of my dilemna...
1.I need Access to create a new Excel Workbook with a specified number of
worksheet with names.
2. I then need to run an Excel Addin from code in Access on the Active
Workbook. The Addin creates and addtional worksheet in active workbook named
"measuring data" and populates it in a realtime import from a piece of test
equipment.
3. I then need to rename the new worksheet to correspond to test equipment ID.
4.I need to run the Addin multiple times based on amount of test
equipment(1-3 times). I can code that functionality.

What I need help with is running th Addin in Active Workbook. I seem to be
able to partially get it to work in a new workbook. All my code is below....
It is a little choppy and I will clean it up when I get it to work. All
suggestion and help is greatly appreciated. Thank You In Advance.
Tirelle
-snip-