Excel VB » Access VB

gg

Well-known Member
Joined
Nov 18, 2003
Messages
560
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?

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

Well-known Member
Joined
Nov 18, 2003
Messages
560
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"
 
Last edited:

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251

ADVERTISEMENT

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

Well-known Member
Joined
Nov 18, 2003
Messages
560
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

Well-known Member
Joined
Nov 18, 2003
Messages
560

ADVERTISEMENT

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
Code:
Public Sub SKPIUPDATE()
to
Code:
Public Function SKPIUPDATE()
and it would be better?

Is this a "best practice"?

Thank you
 

gg

Well-known Member
Joined
Nov 18, 2003
Messages
560
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,944
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 18, 2003
Messages
560
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top