controling excel from vba access and using sub procedures

[trevorC via AccessMonster.com]

Hi All,
I'm having trouble getting my code to work using sub procedures in my vba
code, this only happens when i am interacting with excel. I've tried to copy
all the dims and defs from the calling procedure but to no avail at this time.
sample code below.

Dim strPath As String
Dim rst As DAO.Recordset
Dim Excel_Application As Excel.Application
Dim Excel_Workbook As Excel.Workbook
Dim Current_Worksheet As Excel.Worksheet
Dim Data_Range
Dim Worksheet_Name
Dim db As Database
Dim rs As Recordset
D_now = Format(Date, "dd-mm-yy") ' Formated to use as part of file name
gg = "C:\Employee Time Report " & D_now & ".xls"
Is_File= Len(Dir(gg))
If Is_File= 0 Then
Else
T_Msg = MsgBox("File already exists, Delete file and continue ?.",
vbYesNo, "")
If T_Msg = vbYes Then
Kill gg
Else
Exit Sub
End If
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report Output",
gg, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Comments", gg,
True
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent
Excel_Application.WindowState = xlMinimized
Excel_Application.Visible = True
Excel_Workbook.Windows(1).Visible = True
Excel_Workbook.Worksheets(1).Name = "Report Output"
Set Current_Worksheet = Excel_Workbook.Worksheets("Report Output")
Excel_Workbook.Worksheets("Report Output").Select

THE code here works if left here but not in it's own sub procedure.- 'CAll
Add_Comments'

Current_Worksheet.Range("B11").AddComment
Current_Worksheet.Range("B11").Comment.Visible = False
Current_Worksheet.Range("B11").Comment.Text Text:="trevorc:" & Chr(10) &
"test"

I was hoping to then add code to this sub procedure.

 

[Stefan Hoffmann]

hi Trevor,

THE code here works if left here but not in it's own sub procedure.- 'CAll
Add_Comments'

Current_Worksheet.Range("B11").AddComment
Current_Worksheet.Range("B11").Comment.Visible = False
Current_Worksheet.Range("B11").Comment.Text Text:="trevorc:"& Chr(10)&
"test"

I was hoping to then add code to this sub procedure.

It works if you define it correctly:

Public Sub AddComments(AWorkSheet As Excel.Worksheet)

AWorkSheet.Range("B11").AddComment
AWorkSheet.Range("B11").Comment.Visible = False
AWorkSheet.Range("B11").Comment.Text _
Text:="trevorc:" & Chr(10) & "test"

End Sub

Use it as

AddComments Current_Worksheet

or

Public Sub AddComments(ARange As Excel.Range)

ARange.AddComment
ARange.Comment.Visible = False
ARange.Comment.Text _
Text:="trevorc:" & Chr(10) & "test"

End Sub

Use it as

AddComments Current_Worksheet.Range("B11")


I would assume the second method is what you need.


mfG
--> stefan <--

 

[ryguy7272]

It is quite easy to perform operations in Excel, and control the entire
process from Access.

Make sure you set a reference to Excel, and then run this code in an Access
module:

Option Compare Database
Option Explicit ' Use this to make sure your variables are defined
' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub Rep()
Dim strFile As String
strFile = "C:\MyExcelWorkbook.xls"

' Of course, this is just an example; put the actual path to your actual
file here…
' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True

' Opens up a Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)

' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet

' Set xlWS = xlWB("Sheet1")
With xlWS ' You are now working with the Named file and the named worksheet
' Your Excel code begins here…you can record a macro and make the process
super easy!!
End With

' Close and Cleanup
xlWB.SaveAs xlSaveFile
xlWB.Close
xlapp.Quit
Set xlapp = Nothing

End Sub

 

[trevorC via AccessMonster.com]

Hi,
I don't have a problem with interfacing with excel, theres only 2 things I
can't do as yet (turn off print text box(I can create it but not turn it off
when printing), create code and save it to a macro in excel, I can create the
macro but not assign the code to it.),but thats a different day.

What I am trying to do is re-use common code that i need in a loop, I don't
want to have 50 copies of this in my app, so I thought to use a sub procedure
and call it were required. This works fine in access for other subs I have
created but not if I am also interacting with excel at the same time. I have
tried adding the references to excel that are in the original calling
procedure as well.
All help is greatfully accepted.
regards
Trevor.