Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 Public Function AutomateExcel(ChargeEntry As Boolean, strBookName As String, intNumSheets As Integer) As Workbook 'This function create a workbook for importing digital hydrometer data. A seperate workshheet for each hydrometer 'is created. Data is imported for each hydrometer. Dim intOrigNumSheets As Integer 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 intOrigNumSheets = Excel.Application.SheetsInNewWorkbook If ChargeEntry Then strBookName = "Charge_" & strBookName & "_SpecificGravities" Set xlApp = New Excel.Application xlApp.SheetsInNewWorkbook = intNumSheets xlApp.Visible = True Set xlsHydrometerImport = Workbooks.Add 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. . . . . ." 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 strBookName = xlsHydrometerImport.FullName End With For HydrometerCount = 1 To intNumSheets 'Code to simulate an import ImportFromHydrometers = 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 str As String str = "\AP-SoftPrint.xla" xlApp.Workbooks.Open (xlApp.Application.LibraryPath & str) xlApp.Application.OnTime Now(), ("AP-SoftPrint.xla!startcollection"), Now() + 1 Excel.SendKeys "{~}", True xlApp.Application.OnTime Now(), ("AP-SoftPrint.xla!endcollection"), Now() + 1 'Excel.CommandBars.ActionControl.OnAction ' ' Excel.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 xlsHydrometerImport = Nothing Excel.Application.SheetsInNewWorkbook = intOrigNumSheets Set xlApp = Nothing Set AutomateExcel = Nothing Excel.Application.Quit CreateNew_End: Exit Function CreateNew_Err: Debug.Print Err.Number & " " & Err.Description Set AutomateExcel = Nothing xlsHydrometerImport.Close False Resume CreateNew_End End Function |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
addin running in different Excel instances | Excel Programming | |||
Using an excel addin to call macros in current sheet. | Excel Programming | |||
Access current active workbook from DLL | Excel Programming | |||
Addin macro to delete names in current workbook | Excel Programming | |||
Getting Access Error Messages when running Access through Excel | Excel Programming |