I have a VB script that searches through several worksheets for info to compile into one worksheet. Is there any way to get that script to run when that one specific worksheet is opened?
from my experience....not if you want real time data collection as excel won't automatically update across different spreadsheets if they are already open by another user. Excel will grab the data at the point the spreadsheet was opened but that's it. Access is another story altogether.
Interesting! Wonder if that holds true when auto save is on? In any case, I found where to insert the code to auto call the macro. Wish I had more Access experience. Coworkers seem to want to stay away from Access for some reason.
sample vbscript to pull data in a spreadsheet. Just manipulate for multiple sheets. Set objXL = CreateObject("Excel.Application") Set objWB = objXL.WorkBooks.Open("C:\Scripts\test1.xls") Set objWS = objXL.ActiveWorkBook.WorkSheets("test1") Dim CellArray() ACount = 0 For i = 300 To 350 If objXL.Cells(i, 9).Value = "" Then CellValue = "Empty" Else CellValue = objXL.Cells(i, 9).Value End If ReDim preserve CellArray(ACount) CellArray(ACount) = CellValue ACount = ACount + 1 Next objWB.Close objXL.Quit
Access is super easy and was my favorite tool for collecting data from spreadsheets - I was pretty much self-taught. There are lots of books and tutorials out there.
Ditto on Access. After taking an Access course at JCC, I wouldn't consider using Excel for anything other than the simplest temporary spreadsheet. The setup may require more thought and a minimal time hit on the front end, but the benefits are enormous. To be honest, I think the only reason Excel even exists is because users are comfortable and don't want to change. Open office also has a version of Access, but I've never used it.
