Running Excel Addin from VBA in Access....HELP!!

[Tirelle]

Please Help!!! 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

 

[Robert Morley]

You'd probably be better off asking this in an Excel VBA group. Despite the
fact that you're automating it with Access, you're really using the Excel
Object Model, and Excel VBA users would be more familiar with it.


Rob

Please Help!!! 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

<code snipped>

 

[LarryP]

I have various databases that do various parts of this, sort of, so will
offer a couple thoughts in case they might help. Using your numbering scheme:

(1) This I've done; a routine runs a query several times, but changes the
criteria and the output name each time. The first pass therefore creates
(via acTransferSpreadsheet) the desired Excel file with the first worksheet,
and each subsequent pass adds another worksheet.

(2), (3) and (4) When I ran into this situation, what I wound up doing was
first creating an Excel macro that did exactly what I wanted. Then I put the
entire VBA for that macro into a text file. My Access procedure opens the
target Excel file (presumably the one you just created in (1) above), inserts
the text file into that Excel workbook as a macro, then just tells Excel to
run the macro. Crunch, munch, crunch, munch, Excel does its thing, following
the instructions in your macro. Then control goes back to Access, which
closes the Excel file and ends the procedure. I found this way easier than
trying to write Excel VBA within Access, with all the objects and what have
you that are required to make that work.

If either of those is of interest, reply here and I'll get my code to you
somehow.

 

[Tirelle]

Thanks larry. That does interest me. Would love to see code and try it.

 

[LarryP]

Okay. Got an EMail address? (It's a bit long, and probably not of interest
to everybody here...)

 

[Tirelle]

Yep.. Send to (e-mail address removed) Thanks!!!

Okay. Got an EMail address? (It's a bit long, and probably not of interest
to everybody here...)

 

[LarryP]

It's on its way. Good luck.

Yep.. Send to (e-mail address removed) Thanks!!!

 

[Jeanette Cunningham]

Hi Larry,
Could you please shoot a copy of the code over to me, so I can see how you
can use the VBA in excel from the text file.
get my e mail by removing spaces from the line below
jj cc 14 @ gmail .com

Thanks very much
Jeanette Cunningham

 

[LarryP]

I didn't post it here initially as I didn't think it was of broad interest,
but two is a majority, so here it is. Note, though, that the code that
actually runs in Excel is NOT shown below. That code is stored in a text
file. You initially build a macro in Excel to do what you want, then copy
all that VBA and paste it into a text file. Each time you run your Access
procedure, the code below (part 2, specifically) grabs that text file and
imbeds it in an Excel file of your choosing, then runs it as a macro.

''''First part: code that creates an Excel file, first with one worksheet
and then with additional worksheets as the code iterates through a table with
a list of the output names I want:

Public Function ExportTabbedDetail()

Dim objUniqueTeams As Object

'Open tblUniqueTeams and loop through all records...
''''My tblUniqueTeams simply contains a list of names that are to be the
names of the successive tabs in the Excel file. You can call yours
tblExcelTabNames or whatever if you prefer.

Set objUniqueTeams = CurrentDb.OpenRecordset("tblUniqueTeams")
objUniqueTeams.MoveFirst
Do While Not objUniqueTeams.EOF

strSheetName = objUniqueTeams!Team
CurrentDb.QueryDefs("qryExportTabbedDetail").SQL = RebuildSQL(12)
''''In my case, I’m changing the query criteria each time I run it, so the
line above calls another function to modify the SQL for each pass. For your
purposes that may not be necessary; you might be able to just run the same
query over and over if the main purpose is to generate the necessary named
worksheets. Each worksheet would then contain the exact same initial
contents, but you can delete all the contents as part of your Excel macro.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryExportTabbedDetail", "your output filename goes here….xls", True,
strSheetName

objUniqueTeams.MoveNext
Loop
objUniqueTeams.Close
End Function


''''Part 2: code that opens your Excel file, then takes your Excel macro
VBA, which you have stored in a text file, and inserts it into your workbook,
then runs it.

Private Sub DoExcelMacros()
Dim oXL As Object
Dim oBook As Object
Dim oSheet As Object
' Create a new instance of Excel and make it visible
Set oXL = CreateObject("Excel.Application")
' Open newly generated Excel file, then set a reference to the worksheet
where the macro is to run and activate it.
Set oBook = oXL.Workbooks.Open(your filename here)
Set oSheet = oBook.Sheets("qryPVACrosstabsMerge")
oSheet.Activate
' Import the VBA code from your text file into a module in the Excel file
oXL.VBE.ActiveVBProject.VBComponents.Import "name of your text file
containing the Excel macro VBA goes here….bas" ''''I think it needs to have
the .bas extension to keep Excel happy, but not 100% sure, maybe .txt would
be OK too
'Save the Excel file with the newly imported macro
oBook.Save
' Now run the Excel macro you just imported
oXL.Run "name you assigned to your macro goes here…"
' Close the Excel file, saving the work done by the macro
' Then release any outstanding object references.
oBook.Close (True)
oXL.Quit
Set oSheet = Nothing
Set oBook = Nothing
Set oXL = Nothing
End Sub

 

[Jeanette Cunningham]

Thanks Larry

I didn't post it here initially as I didn't think it was of broad interest,
but two is a majority, so here it is. Note, though, that the code that
actually runs in Excel is NOT shown below. That code is stored in a text
file. You initially build a macro in Excel to do what you want, then copy
all that VBA and paste it into a text file. Each time you run your Access
procedure, the code below (part 2, specifically) grabs that text file and
imbeds it in an Excel file of your choosing, then runs it as a macro.

''''First part: code that creates an Excel file, first with one worksheet
and then with additional worksheets as the code iterates through a table
with
a list of the output names I want:

Public Function ExportTabbedDetail()

Dim objUniqueTeams As Object

'Open tblUniqueTeams and loop through all records...
''''My tblUniqueTeams simply contains a list of names that are to be the
names of the successive tabs in the Excel file. You can call yours
tblExcelTabNames or whatever if you prefer.

Set objUniqueTeams = CurrentDb.OpenRecordset("tblUniqueTeams")
objUniqueTeams.MoveFirst
Do While Not objUniqueTeams.EOF

strSheetName = objUniqueTeams!Team
CurrentDb.QueryDefs("qryExportTabbedDetail").SQL = RebuildSQL(12)
''''In my case, I'm changing the query criteria each time I run it, so the
line above calls another function to modify the SQL for each pass. For
your
purposes that may not be necessary; you might be able to just run the same
query over and over if the main purpose is to generate the necessary named
worksheets. Each worksheet would then contain the exact same initial
contents, but you can delete all the contents as part of your Excel macro.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryExportTabbedDetail", "your output filename goes here..xls", True,
strSheetName

objUniqueTeams.MoveNext
Loop
objUniqueTeams.Close
End Function


''''Part 2: code that opens your Excel file, then takes your Excel macro
VBA, which you have stored in a text file, and inserts it into your
workbook,
then runs it.

Private Sub DoExcelMacros()
Dim oXL As Object
Dim oBook As Object
Dim oSheet As Object
' Create a new instance of Excel and make it visible
Set oXL = CreateObject("Excel.Application")
' Open newly generated Excel file, then set a reference to the worksheet
where the macro is to run and activate it.
Set oBook = oXL.Workbooks.Open(your filename here)
Set oSheet = oBook.Sheets("qryPVACrosstabsMerge")
oSheet.Activate
' Import the VBA code from your text file into a module in the Excel file
oXL.VBE.ActiveVBProject.VBComponents.Import "name of your text file
containing the Excel macro VBA goes here..bas" ''''I think it needs to
have
the .bas extension to keep Excel happy, but not 100% sure, maybe .txt
would
be OK too
'Save the Excel file with the newly imported macro
oBook.Save
' Now run the Excel macro you just imported
oXL.Run "name you assigned to your macro goes here."
' Close the Excel file, saving the work done by the macro
' Then release any outstanding object references.
oBook.Close (True)
oXL.Quit
Set oSheet = Nothing
Set oBook = Nothing
Set oXL = Nothing
End Sub