Excel VB » Access VB

[gg]

Hey guy's!!
I am using the below VB code in excel to download and save data onto my hard drive.
Basically the website I use gives me the option to save the file as an Excel file.

This code has been running in Excel for a couple years and does very well. However, I would like to create an Access system that does the same thing.

Is it possible to move the same VB to Access and will it function the same way?

Public Sub SKPIUPDATE()
Dim QPR
Dim lnk
Dim frm
Dim start
Dim fin
Dim drp1
Dim drp2
Dim src1
Dim NAMC As Integer
' This macro will automatically open and download the TMMK-VEH daily scrap
'and store the file in the same directory

Set QPR = CreateObject("InternetExplorer.application")

    QPR.Visible = True
    
    QPR.navigate "https://www.portal.toyotasupplier.com/wps/myportal/"
         
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop
    
    With QPR.document.forms("Login")
        .User.Value = "******"
        .Password.Value = "******"
        .submit
    End With

    Application.Wait Now + TimeSerial(0, 0, 11)

    QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/")
    
    Application.Wait Now + TimeSerial(0, 1, 60)
    
    If myNAMC = "TMMK-VEH" Then
        NAMC = 4
    ElseIf myNAMC = "TMMK-PWT" Then
        NAMC = 3
    ElseIf myNAMC = "TMMC" Then
        NAMC = 5
    ElseIf myNAMC = "TMMTX" Then
        NAMC = 6
    ElseIf myNAMC = "TABC" Then
        NAMC = 7
    ElseIf myNAMC = "NUMMI" Then
        NAMC = 8
    ElseIf myNAMC = "TMMI" Then
        NAMC = 9
    ElseIf myNAMC = "TMMTX" Then
        NAMC = 6
    ElseIf myNAMC = "TMMBC" Then
        NAMC = 10
    ElseIf myNAMC = "TMMAL" Then
        NAMC = 11
    ElseIf myNAMC = "TMMNK" Then
        NAMC = 12
    End If
        
    Set lnk = QPR.document.Links(3) ' 3=TMMK-VEH,4=TMMK-PWT,5=TMMC,6=TMMTX,7=TABC,8=NUMMI,9=TMMI,10=TMMBC,11=TMMAL,12=TMMNK
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop
    
    lnk.Click
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop

    QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/SkpiGatewayServlet?jadeAction=NCPARTS_SEARCH")
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop
  
    Set frm = QPR.document.forms("form1")
    Set dwn = QPR.document.forms("page")
        
    Set start = frm.all("SKPI_SEARCH_START_DATE_KEY")
    start.Value = "01/01/" & Year(Now)
    
    Set finish = frm.all("SKPI_SEARCH_END_DATE_KEY")
    finish.Value = Format(Now - 1, "mm/dd/yyyy")
    
    Set drp2 = frm.all("SKPI_SEARCH_NC_TYPE_KEY")
    drp2.Item(1).Selected = True
    
    Set src1 = frm.all("Submit")
    
    src1.Click
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop
    
    QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/DownloadNCPartListServlet")
    
    Application.Wait Now + TimeSerial(0, 1, 0)
    
    Windows("DownloadNCPartListServlet").Activate

End Sub

 

[SydneyGeek]

In Access, you can run from the VBE (fine if you're testing, nasty if you're a user) or attach the code to a button. So...

1. Place a button on a form, with Wizards off.
2. Create a Click event for the button, and use code like this:
YourModuleName.TheNameOfTheFunction

Denis

 

[gg]

Sydney,

Thanks for the help here.
I tried your direction and got a compile error "Method or Data Member not found"

In the code builder I used this..

Private Sub Command0_Click()
Form_Form1.TEMP_SKPIUPDATE_ALL_NAMC
End Sub

My module name = module1

Public Function TEMP_SKPIUPDATE_ALL_NAMC()
Dim QPR
Dim lnk
Dim frm
Dim dwn
Dim Start
Dim fin
Dim drp1
Dim drp2
Dim drp3
Dim src1
Dim NAMC As Integer

' This macro will automatically open and download the TMMK-VEH daily scrap
'and store the file in the same directory

Set QPR = CreateObject("InternetExplorer.application")

    QPR.Visible = True
    
    QPR.navigate "https://www.portal.toyotasupplier.com/wps/myportal/"
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop
    
    With QPR.Document.Forms("Login")
        .User.Value = "%%%%%"
        .Password.Value = "%%%%%"
        .submit
    End With
    
    Application.Wait Now + TimeSerial(0, 0, 11)

    QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/")
       
End Function

 

[SydneyGeek]

Put the function into a standard module and try again. That's where public functions should live.

Denis

 

[gg]

I created a class module with the same code in it.

I think should be pretty simple. I checked my references and there is nothing missing.

Now I am getting this error..

Visual Basic for Applications (VBA) encountered a problem while attempting to access a property or method. The problem may be one of the following:
A reference is missing.
For help restoring missing references, see the Microsoft Knowledge Base article 283806.
An Expression is misspelled.
Check all expressions used in event properties for correct spelling.
A user-defined function is declared as a sub or as a private function in a module.
Expressions can resolve a user-defined function only if the function is declared as one of the following:
A public function in a module
A public or private function in a code module of the current form or report
Security in Access is set to Medium or High and the Microsoft Jet 4.0 SP8 update is not installed.
A more recent verion of Jet 4.0 must be installed for Access to function properly when security is set to Medium or High. To obtain the latest version of Microsoft Jet, go to Windows Update.

Form Code:

Private Sub Command0_Click()
Form_Form1.SKPIUPDATE_ALL_NAMC
End Sub

Public Function SKPIUPDATE_ALL_NAMC()
Dim QPR
Dim lnk
Dim frm
Dim dwn
Dim Start
Dim fin
Dim drp1
Dim drp2
Dim drp3
Dim src1
Dim NAMC As Integer

' This macro will automatically open and download the TMMK-VEH daily scrap
'and store the file in the same directory

Set QPR = CreateObject("InternetExplorer.application")

    QPR.Visible = True
    
    QPR.navigate "https://www.portal.toyotasupplier.com/wps/myportal/"
    
    Do While QPR.Busy: DoEvents: Loop
    Do While QPR.readyState <> 4: DoEvents: Loop
    
    With QPR.Document.Forms("Login")
        .User.Value = "ggodwin"
        .Password.Value = "060469-9"
        .submit
    End With
    
    Application.Wait Now + TimeSerial(0, 0, 11)

    QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/")
       
End Function

 

[SydneyGeek]

Not a class module, just a standard module. Sorry, I should have been clearer. For future reference...

Standard modules hold all of your general code. (Insert > Module in the IDE). This is where you create functions that you call from other parts of the database
Class modules are for defining objects and classes.
Form and report modules are specialised class modules. They also work with objects, usually existing database objects and their controls. (eg, forms, reports, recordsets). If you place a function in a form module you can call it from that form, but generally not from anywhere else because the form needs to be open when you call the function.

Hope that helps
Denis

 

[gg]

I see.
That actually is how I started off.