Controlling Excel from within Access

[maGemme]

Hi again everyone,

Here is what I have and where I am stuck.

I have an Access DB and an Excel spreadsheet. The Excel spreadsheet is built to gather information from a website that contains statistics we use in the office. There is a "front" worksheet that has basic controls such as dates, clear button and search button.

Basically you enter the dates between which you want to get the data (i.e. Let's say I want the report for may I would enter 05/01/2010 in one field and 05/31/2010 in another. Once you hit the seach button Excel launches internet explorer, logs in to the website and collects all the info in different worksheets.

I use each of these worksheets as a linked table in my Access DB. Right now I have to enter the spreadsheet manually to run the web query.

What I want to know is if it's possible to have a form setup in Access that would allow me to enter the dates I want (through a calendar control for instance), feed that to Excel in the specific fields and then activate the search button.

I hope this isn't to basic, I searched around but couldn't find what I was looking for. I know it would be best to bypass Excel altogheter and have Access gather the info from the website itself but I fear my VBA skills are to limited and the Excel spreadsheet works perfectly!

I hope I was clear enough, if not I'll try to clarify.

Thank you in advance.

 

[DCrake]

It is possible to pass info from Access to Excel, however you may also be able to move the code from Excel to Access to populate tables in Access as opposed to populating worksheets. Can you show the code being to to quantify this?

 

[maGemme]

Well I don't feel super comfortable giving out the code used since I'm not the author, this is not the whole code but the main part that gathers info. I have obviously removed the URL as it is confidential.

Sub RPCpull(struser As String, strpass As String, strdtstart As String, strdtend As String)
 
strurl = xxxxxxxxxxxxxxxxxx
 
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
 
ie.Visible = True
ie.Navigate strurl
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Do While ie.Busy
Loop
 
DoEvents
Call SendKeys(struser) 'This is the Login Name Box.
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
DoEvents
Call SendKeys("{tab}") 'I am tabbing to the password box.
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
DoEvents
Call SendKeys(strpass) 'Password Box
DoEvents
Call SendKeys("{tab}") ' Tab to the "login" button.
DoEvents
Call SendKeys("{enter}") 'Hit enter on the "login" button
 
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 15
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
 
Do While ie.Busy
Loop
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
' waits for report to load, then copies to clipboard
ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
' pastes into excel pastesheet
ThisWorkbook.Activate
Sheets("PasteSheet").Select
Sheets("PasteSheet").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Shapes.SelectAll
Selection.Delete
ActiveCell.Activate
 
Sheets("PasteSheet").Activate
Sheets("PasteSheet").Range("A1:P2000").Select
Selection.MergeCells = False
ActiveSheet.Range("B1:I2000").Copy
Sheets("AHT OBRPC Data").Activate
Sheets("AHT OBRPC Data").Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
 
Worksheets("PasteSheet").Select
ActiveSheet.Range("A1:AZ2000").Select
Selection.ClearContents
Selection.MergeCells = False
ActiveSheet.Shapes.SelectAll
Selection.Delete
ie.Quit
End Sub

Honestly this is as much as I feel comfortable sharing... Hope it helps, otherwise I'll keep looking for my original answer which is to send commands to Excel from Access

 

[maGemme]

Bump up, I'd still like to find a way to send commands to Excel from within Access without having to integrate the code in Access.

 

[boblarson]

Bump up, I'd still like to find a way to send commands to Excel from within Access without having to integrate the code in Access.

What do you mean "without having to integrate the code in Access." If you are going to be working with Excel from Access you will need to code it in Access and the code will need to reside in Access.

 

[maGemme]

Sorry I was referring to the previous post where DCrake was trying to go the route of integrating the code used in Excel inside my Access DB.

I'd like to keep my Excel spreadsheet as is but be able to send commands to it from a form in Access. Hope that's clearer.

 

[boblarson]

For that you can do things like:

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object

Set objXL = CreateObject("Excel.Application")

objXL.Visible = True

Set xlWB = objXL.WorkBooks("PathAndNameToWorkBookHere").Open

Set xlWS = xlWB.Worksheets("NameOfWorkSheetHere")

And then you can go about doing things with it.

 

[maGemme]

thank you again Bob, you're a lifesaver (timesaver I guess)

Now there's only 2 things i need to do in Excel, modify 2 specific cells with data gathered from text boxes in Access and the other thing is to push a command button that's on the Excel spreadsheet. Can that be done?

 

[boblarson]

thank you again Bob, you're a lifesaver (timesaver I guess)

Now there's only 2 things i need to do in Excel, modify 2 specific cells with data gathered from text boxes in Access and the other thing is to push a command button that's on the Excel spreadsheet. Can that be done?

Automation is a wonderful thing. Yes, it can.

So, you can modify the specific cells (within the context of what I had by using):

xlWS.Range("G4").Value = Forms!YourFormName.txtTextBoxName

 

[boblarson]

I'm not sure about how to run the code for the button, but I'm sure we'll either find it or someone else will chime in with it.

 

[maGemme]

Automation is a wonderful thing. Yes, it can.

So, you can modify the specific cells (within the context of what I had by using):

xlWS.Range("G4").Value = Forms!YourFormName.txtTextBoxName

The problem I have is that I can't modify the spreadsheet itself. I need a way to tell access to open up the spreadsheet and enter the value from Forms!YourFormName.txtTextBoxName into cell "XX".

Thank you for the button response as well, hopefully wE'll find a way. I mean worse case I just go in Excel and do it manually... I'm just trying to save myself a few minutes every month when I have to run report.

 

[boblarson]

The problem I have is that I can't modify the spreadsheet itself. I need a way to tell access to open up the spreadsheet and enter the value from Forms!YourFormName.txtTextBoxName into cell "XX".

That IS the way you enter the value (Entering the value is modifying the cell, by the way).

 

[maGemme]

Ok, I'll try it out and get back to you!