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

 

[Fazza]

Hi, gg.

Suggest you give it a go. It appears to me that it should work identically in Access or one of the other Office programs - so Word, Outlook, etc. I don't notice anything specific to Excel.

HTH. Regards, Fazza

 

[gg]

I have never done VBA in access. I assumed it was the same as excel. But when I tried to run it nothing happened?

What do I need to know about Access to get it to run?

I tried to run it from the debugger and got a
compile error
"method or data member not found"

 

[Fazza]

gg,

I can't help you, I think. The code seems incomplete. Certainly not all variables are dimmed - though maybe you don't require that; I always use 'option explicit'. Maybe this is only part of the code from Excel? I wonder if it works OK if you paste the code in a new Excel workbook. Regards, Fazza

 

[SydneyGeek]

For interacting with IE, you'll need a specific library. Check the references in the working spreadsheet. If you can find a selected ref for IE, go to the Access IDE and reference the same library.

You could also change it from a Public Sub to a Public Function. That often affects the calling in Access: you can call Functions from forms and buttons, but not Subs.

Denis

 

[gg]

gg,

I can't help you, I think. The code seems incomplete. Certainly not all variables are dimmed - though maybe you don't require that; I always use 'option explicit'. Maybe this is only part of the code from Excel? I wonder if it works OK if you paste the code in a new Excel workbook. Regards, Fazza

This code has worked good for me for over a year. However, I am really struggling with getting it to work on another PC.

What is the "Option Explicit" I am not a VB guru. I just started using VB on this file and got it to work and never really had to learn much more.

 

[gg]

For interacting with IE, you'll need a specific library. Check the references in the working spreadsheet. If you can find a selected ref for IE, go to the Access IDE and reference the same library.

You could also change it from a Public Sub to a Public Function. That often affects the calling in Access: you can call Functions from forms and buttons, but not Subs.

Denis

So you are saying I could go from

Public Sub SKPIUPDATE()

to

Public Function SKPIUPDATE()

and it would be better?

Is this a "best practice"?

Thank you

 

[gg]

Ok guy's I am having a problem here. I have a blank db and wanting to just run the VB. I will build off this once I get this to work.

I have made the changes that were recomended above . However, I cant figure out how to call the VB? In Excel it is easy. Tools»Macro»Run.

 

[Joe4]

I have made the changes that were recomended above . However, I cant figure out how to call the VB? In Excel it is easy. Tools»Macro»Run.

Most VBA in Access is event driven (so it runs upon some event happening, like the opening of a Form, changing of a value, clicking of a button, etc). Quite often, it is connected to Command, so clicking the button runs the code.

One way to get it to run in a more "standalone" manner (like in Excel), is to go in the Macros section, and create a new Macro and use the RunCode Action to call the existing VBA function.

 

[gg]

I also found I could run it in the debugger by selecting the name? Don't know if this makes sense but I got it run...

Just to get a compile error