PDA

View Full Version : Solved: Passing variable from access to excel



icthus123
06-18-2007, 06:11 AM
Hi, I need to pass a variable from access to excel, how would I do this?

matthewspatrick
06-18-2007, 03:28 PM
Please clarify what you are trying to do.

icthus123
06-19-2007, 12:58 AM
Sorry, I'm trying to open an excel workbook from access and put a date which the user has selected in access in a certain column depending on what day of the week it is.

Now I can do this and I'm using this code to do it.


Private Sub cmdExcel_Click()

Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim DayNr As Integer

Set objExcel = New Excel.Application

With objExcel
.Visible = True
Set objWorkbook = .Workbooks.Open("C:\Documents and Settings\SPP Conference\Desktop\Timesheet template.xlt")
End With

objWorkbook.Sheets("HOURS").Select

DayNr = Weekday(cboStartDate, vbSunday)

Select Case DayNr
Case 1
Range("C6").Value = cboStartDate
Case 2
Range("D6").Value = cboStartDate
Case 3
Range("E6").Value = cboStartDate
Case 4
Range("F6").Value = cboStartDate
Case 5
Range("G6").Value = cboStartDate
Case 6
Range("H6").Value = cboStartDate
Case 7
Range("I6").Value = cboStartDate
End Select

End Sub

However, I can't help but feel that it would be better if instead of this I could simply pass the date variable directly into the VBA code in excel and decide from there which cell to populate, etc. This is what I don't know how to do. Any ideas?

matthewspatrick
06-19-2007, 06:11 AM
If your intent is for the process to be initiated in Access, then I think the
approach you take above is pretty much your only option. The only way
for Access to "pass" a value to Excel is by having Access automate Excel.

icthus123
06-19-2007, 07:03 AM
Okay then! Thanks a lot anyway Patrick.