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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.