Access VBA to open, change & save excel file-VBForums
Results 1 to 20 of 20

Thread: Access VBA to open, change & save excel file

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Posts
    332

    Access VBA to open, change & save excel file

    I have an Access database and with in it I need to open, change and then save an Excel file.

    File Name & Path: C:\Users\Owner\Documents\_DENSO QRE\SKPI PARTS RETURN\SKPI_update.xls

    Steps.
    1. Open the file
    2. Select the first row of data
    3. Replace all "." with nothing (Basically just remove the "."
    4. Replace all "/" with nothing
    5. Replace all " " (Blanks with nothing) essensically just removing the blanks
    6. Save and Close the file


    I can do this in excel but it seems that Access is a whole different deal.
    Last edited by ggodwin; Jan 15th, 2009 at 10:26 AM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,238

    Re: Access VBA to open, change & save excel file

    See the Excel Tutorial link in my signature (or the FAQs at the top of this forum) to see how to work with Excel files from VB/VBA.

    That should be enough for you, but feel free to ask for more specific help if needed.

  3. #3
    Hyperactive Member Davadvice's Avatar
    Join Date
    Apr 2007
    Location
    Glasgow (Scotland)
    Posts
    440

    Re: Access VBA to open, change & save excel file

    The below code will allow you to open a spread sheet. I copy this from 1 project to another instead of trying to remember it all.

    When I was starting with xl I found it to be frustrating trying to utilize the correct functions and differentiating from workbook and work sheet.

    I would advise that you take a look at the object model for xl while you are working with it, this will allow you to see what functions an properties each part has.
    if you need more as Si says just land it here and we will try and help.

    one tip i will give is when xl crashes out it will still be in the process task list (ctrl+Alt+Del Task List) if you don't close the work book each time it will cause issues.

    Good luck
    David


    vb Code:
    1. Public Sub openXL()
    2. Dim xl As Excel.Application
    3. Dim wbk As Excel.Workbook
    4. Dim wsht As Excel.Worksheet
    5. Dim strFileName As String
    6. Dim lastRow As Integer
    7.  
    8.        
    9. Set xl = New Excel.Application
    10. xl.DisplayAlerts = False
    11. Set wbk = xl.Workbooks.Add("c:\Temps\Temp.xls")
    12.  
    13.  
    14. wbk.SaveAs "c:\Temps\Temp.xls"
    15. wbk.Close
    16. Set xl = Nothing
    17.  
    18. End Sub
    This is Blank

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Posts
    332

    Re: Access VBA to open, change & save excel file

    David,
    Thanks for the Advise. Do you have a resource that you recomend I use to study the XL Object model?

    Another problem that I am having is asking Access to take a current Excel file that is open and saving it.

    I use VBA to navigate a website and download the file into Excel. Then I want it to be saved so that I can import it in the next step. However, 30% of the time It does not save the excel file. Instead it saves the Websites login page and then gives me an error when I try to open it. Other times it works very good.

    This is the entire code I am using to download the file. The last 5 lines are the lines that save the file. If you have any ideas on how to get that to work. I could greatly use them.

    Code:
    Option Compare Database
    Option Explicit
    Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
    "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
    ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
    
    Public Function SkpiUpdate()
    
    Dim QPR As Object
    Dim lnk As Object
    Dim TimeOut As String
    Dim frm As Object
    Dim Start As Object
    Dim Finish As Object
    'Dim drp2 As Object
    Dim drp1 As Object
    Dim src1 As Object
    Dim p1 As Variant
    Dim objWB As Object
    Dim objExc As Object
    
     
    Set QPR = CreateObject("InternetExplorer.application")
     
        QPR.Visible = True
        
        QPR.navigate "https://www.portal.toyotasupplier.com/wps/myportal/"
        
      TimeOut = Now + TimeValue("00:00:20")  '-- wait maximum of 20 seconds
       Do While QPR.Busy Or QPR.readyState <> 4
          DoEvents
          If Now > TimeOut Then
            MsgBox "Time Out before Login"
            Exit Function
          End If
       Loop
     
       With QPR.Document.Forms("Login")
          .User.Value = "xxxxxxx"
          .Password.Value = "xxxxxxxx"
          .submit
       End With
     
       TimeOut = Now + TimeValue("00:00:40")  '-- wait maximum of 10 seconds
       Do While QPR.Busy Or QPR.readyState <> 4
          DoEvents
          If Now > TimeOut Then
             MsgBox "Time Out after Login"
             Exit Function
          End If
       Loop
        QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/")
        
          TimeOut = Now + TimeValue("00:00:05")  '-- wait 1 second for above navigation to take effect
          Do While Now < TimeOut
             DoEvents
          Loop
        
        TimeOut = Now + TimeValue("00:00:50")  '-- wait maximum of 10 seconds
       Do While QPR.Busy Or QPR.readyState <> 4
          DoEvents
          If Now > TimeOut Then
             MsgBox "Did not navigate to SKPI application"
             Exit Function
          End If
       Loop
         
       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
       lnk.Click
       
       TimeOut = Now + TimeValue("00:00:20")  '-- wait 1 second for above navigation to take effect
          Do While Now < TimeOut
             DoEvents
       Loop
       
       QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/SkpiGatewayServlet?jadeAction=NCPARTS_SEARCH")
       
             TimeOut = Now + TimeValue("00:00:05")  '-- wait 1 second for above navigation to take effect
          Do While Now < TimeOut
             DoEvents
          Loop
       
        Set frm = QPR.Document.Forms("searchForm") ' was form1 before the change in December 2008
            
        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 drp1 = frm.all("SKPI_SEARCH_NAMC_KEY")
        drp1.Item(p1).Selected = True
        
        Set src1 = frm.all("Search") 'was submit before December 2008 update
        
        src1.Click
        
        TimeOut = Now + TimeValue("00:00:05")  '-- wait 1 second for above navigation to take effect
          Do While Now < TimeOut
             DoEvents
        Loop
            
        QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/DownloadNCPartListServlet")
        
        TimeOut = Now + TimeValue("00:01:00")  '-- wait 1 minute for above navigation to take effect
        Do While Now < TimeOut
             DoEvents
        Loop
      
       'QPR.navigate ("https://www.portal.toyotasupplier.com/public/pr_logout.htm")
       
       Const strUrl As String = "https://www.portal.toyotasupplier.com/skpi/DownloadNCPartListServlet"
        Dim strSavePath As String
        strSavePath = "C:\Users\Owner\Documents\_DENSO QRE\SKPI PARTS RETURN\SKPI_UPDATE.xls"
        Dim returnValue As Long
        returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)
    
    End Function

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,238

    Re: Access VBA to open, change & save excel file

    Quote Originally Posted by ggodwin
    David,
    Thanks for the Advise. Do you have a resource that you recomend I use to study the XL Object model?
    My tutorial covers the most important and frequently used parts, you can get further information from the help in VB (when you have a reference to Excel, simply click on any Excel based item in your code and press F1).


    Another problem that I am having is asking Access to take a current Excel file that is open and saving it.
    ...
    One possible problem is that you are navigating the pages using IE, but are then using URLDownloadToFile to get the file. I don't know how well they link up, so it is possible that the page is not downloaded 'appropriately' by IE. I don't interact with IE much, but would expect there to be part of the object model which allows you to save the file.

    A more likely problem is that the page is not fully downloaded, as you aren't checking that - you just wait a fixed amount of time and hope it is good enough. It would be much better to check if the page has fully loaded instead.

    I'm not sure what property you need to check, but the code for your "TimeOut" loops would be replaced by something like this:
    Code:
                          '-- wait for above navigation to take effect
        Do While QPR.StillExecuting   'this is likely to be the wrong name!
             DoEvents
        Loop
    edit: now I've posted it I can see that you actually do that kind of check earlier in the routine - so why not later too?

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Posts
    332

    Re: Access VBA to open, change & save excel file

    SI - Would it be a good practice for me to use the "StillExecuting" code in all of my "Time out" loops?

  7. #7
    Hyperactive Member Davadvice's Avatar
    Join Date
    Apr 2007
    Location
    Glasgow (Scotland)
    Posts
    440

    Re: Access VBA to open, change & save excel file

    Quote Originally Posted by ggodwin
    David,
    Thanks for the Advise. Do you have a resource that you recomend I use to study the XL Object model?
    Hi, I used the object browser within Access and also the help files within Excel to help me get to grips with programing excel.

    David
    This is Blank

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,238

    Re: Access VBA to open, change & save excel file

    It should actually be .Busy/.readyState (as you have earlier) rather than .StillExecuting, but yes I think it should be always used - so that you wait until the page is loaded, instead of just waiting a fixed amount of time that may be too much/too little.

    That does assume however that they would work the same way in that part of your routine, and there is a chance they don't.. you'll need to test it out.


    Note that as you have repeated code for the loops, I would recommend creating Subs/Functions to do the work. For example, by creating one function all of the loops like this one:
    Code:
       TimeOut = Now + TimeValue("00:00:20")  '-- wait maximum of 20 seconds
       Do While QPR.Busy Or QPR.readyState <> 4
          DoEvents
          If Now > TimeOut Then
            MsgBox "Time Out before Login"
            Exit Function
          End If
       Loop
    ..can be changed to this:
    Code:
       If PauseTimeOut("00:00:20") Then
          MsgBox "Time Out before Login"
          Exit Function
       End If
    The function for it would be like this:
    Code:
    Private Function PauseTimeOut(p_strPauseTime As String) As Boolean
    'Waits until QPR is ready, or the timeout expires
    'Returns True if the timeout expires
    
    Dim TimeOut As Date
       TimeOut = Now + TimeValue(p_strPauseTime)
       Do While QPR.Busy Or QPR.readyState <> 4
          DoEvents
          If Now > TimeOut Then
             PauseTimeOut = True
             Exit Function
          End If
       Loop
    
    End Function
    Looking at the size of it here there doesn't appear to be much of an improvement, but as that kind of loop is used a few times it will shorten the code, and of course make it easier to read/maintain, as all of the loops are removed from the main routine (as is one of the variables).

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Posts
    332

    Re: Access VBA to open, change & save excel file

    That sounds like good advice. I will update the system and see where I stand. As of this morning I am only getting the wrong page downloaded on first attempt of the day.

    I am not sure what is causing it then unless a 2nd, 3rd and 4th pass is much faster with less wait time.

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,238

    Re: Access VBA to open, change & save excel file

    That is certainly a possibility, as there is likely to be some kind of caching in one or more parts of the operation.

    If the check for .Busy works properly for it, I suspect that the issue will be gone completely.

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Posts
    332

    Re: Access VBA to open, change & save excel file

    What exactly does the "(00:00:20") do in your function you added?
    I was under the impression that there was not a time associated delay?

    Is this a pause after completion of the QPR Busy step?

    Code:
       If PauseTimeOut("00:00:20") Then
          MsgBox "Time Out before Login"
          Exit Function
       End If

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Posts
    332

    Re: Access VBA to open, change & save excel file

    I am getting a run-time 91' error

    With help pointing me to this line?
    Code:
       Do While QPR.Busy Or QPR.readyState <> 4
    I am sure that I need to declare a variable some where??
    I added and still have the error.

    Code:
    Private Function PauseTimeOut(p_strPauseTime As String) As Boolean
    'Waits until QPR is ready, or the timeout expires
    'Returns True if the timeout expires
    
    Dim QPR As Object
    
    Dim TimeOut As Date
       TimeOut = Now + TimeValue(p_strPauseTime)
       Do While QPR.Busy Or QPR.readyState <> 4
          DoEvents
          If Now > TimeOut Then
             PauseTimeOut = True
             Exit Function
          End If
       Loop
    
    End Function
    The code fires simultaneous with the code logging into he site.
    Any ideas?

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Posts
    332

    Re: Access VBA to open, change & save excel file

    Maybe I should have stated that before I deaclared the QPR variable I had the Compile error on that variable.
    Also, I am wondering. I had timeout declared as a string in my original. Should I change this because you have it as a "date" in the function code?

  14. #14
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,238

    Re: Access VBA to open, change & save excel file

    Quote Originally Posted by ggodwin
    What exactly does the "(00:00:20") do in your function you added?
    I was under the impression that there was not a time associated delay?
    It is passed to the function as the p_strPauseTime parameter, so in that particular example the line of code in the function is equivalent to this:
    Code:
       TimeOut = Now + TimeValue("00:00:20")
    ..which is what was in the original code.

    Using the function means that all of the code to perform the delay and timeout is inside the function - the code to call it simply specifies the time for timeout, and checks if it did reach the timeout (if it does the function returns True, hence the If statement), and what to do if it happens.

    I am getting a run-time 91' error

    With help pointing me to this line?
    Code:

    Do While QPR.Busy Or QPR.readyState <> 4


    I am sure that I need to declare a variable some where??
    Oops... I assumed it was a control, I hadn't noticed that it was a variable inside your original routine.

    In that case you need to change the function slightly, so that QPR is passed as a parameter too, ie:
    Code:
    Private Function PauseTimeOut(QPR as Object, p_strPauseTime As String) As Boolean
    ..and you also need to pass it when you call the function:
    Code:
       If PauseTimeOut(QPR, "00:00:20") Then
    Also, I am wondering. I had timeout declared as a string in my original. Should I change this because you have it as a "date" in the function code?
    Yes you should - storing a date or time in a String is not very safe, it is much better to use the proper data type. If you want more information about why, see the FAQ article Why are my dates not working properly?

    However, if you call the function in place of all of your loops (assuming it is valid for the last few loops, it might not be), you wont need that variable at all.

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Posts
    332

    Re: Access VBA to open, change & save excel file

    OK, I have updated the file and tested it.

    Now I am getting an error on a line I had never had one before. For some reason it is erroring on this line almost as soon as the IE object opens. It is now acting as if nothing has time to load? (Run-Time Error 91) Can you think of anything that would cause me to error here? (Never had an error here before)

    Code:
       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
       lnk.Click
    Code:
    Option Compare Database
    Option Explicit
    Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
    "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
    ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
    Public Function NEWUPDATE()
    
    Dim QPR As Object
    Dim lnk As Object
    Dim TimeOut As Date
    Dim frm As Object
    Dim Start As Object
    Dim Finish As Object
    'Dim drp2 As Object
    Dim drp1 As Object
    Dim src1 As Object
    Dim p1 As Variant
    Dim objWB As Object
    Dim objExc As Object
    
     
    Set QPR = CreateObject("InternetExplorer.application")
     
        QPR.Visible = True
        
        QPR.navigate "https://www.portal.toyotasupplier.com/wps/myportal/"
        
     If PauseTimeOut(QPR, "00:00:20") Then
          MsgBox "Time Out before Login"
          Exit Function
       End If
     
     On Error GoTo SkipLogIn
     
    For Each drp1 In QPR.Document.Forms
     'MsgBox drp1.Name
        If drp1.Name = "Login" Then
             With QPR.Document.Forms("Login")
            .User.Value = "xxxxxxxxx"
            .Password.Value = "xxxxxxxxx"
            .submit
            End With
        End If
      Next drp1
       
    SkipLogIn:
        QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/")
        
      If PauseTimeOut(QPR, "00:00:20") Then
          MsgBox "Time Out After Login"
          Exit Function
       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
       lnk.Click
       
      If PauseTimeOut(QPR, "00:00:20") Then
          MsgBox "Time Out before NAMC selection"
          Exit Function
       End If
       
       QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/SkpiGatewayServlet?jadeAction=NCPARTS_SEARCH")
       
       If PauseTimeOut(QPR, "00:00:20") Then
          MsgBox "Time Out before Search Selection form loads"
          Exit Function
       End If
           
        Set frm = QPR.Document.Forms("searchForm") ' was form1 before the change in December 2008
            
        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 drp1 = frm.all("SKPI_SEARCH_NAMC_KEY")
        drp1.Item(p1).Selected = True
        
        Set src1 = frm.all("Search") 'was submit before December 2008 update
        
        src1.Click
        
       If PauseTimeOut(QPR, "00:00:20") Then
          MsgBox "Time Out before search criteria entered"
          Exit Function
       End If
            
        QPR.navigate ("https://www.portal.toyotasupplier.com/skpi/DownloadNCPartListServlet")
            
       If PauseTimeOut(QPR, "00:00:20") Then
          MsgBox "Time Out before file downloads"
          Exit Function
       End If
      
       'QPR.navigate ("https://www.portal.toyotasupplier.com/public/pr_logout.htm")
       
       Const strUrl As String = "https://www.portal.toyotasupplier.com/skpi/DownloadNCPartListServlet"
        Dim strSavePath As String
        strSavePath = "C:\Users\Owner\Documents\_DENSO QRE\SKPI PARTS RETURN\SKPI_UPDATE.xls"
        Dim returnValue As Long
        returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)
    
    End Function
    Private Function PauseTimeOut(QPR As Object, p_strPauseTime As String) As Boolean
    'Waits until QPR is ready, or the timeout expires
    'Returns True if the timeout expires
    
    Dim TimeOut As Date
       TimeOut = Now + TimeValue(p_strPauseTime)
       Do While QPR.Busy Or QPR.readyState <> 4
          DoEvents
          If Now > TimeOut Then
             PauseTimeOut = True
             Exit Function
          End If
       Loop
    
    End Function
    Last edited by Hack; Jan 21st, 2009 at 07:16 AM. Reason: Removed UserName And Password

  16. #16
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Access VBA to open, change & save excel file

    @ggodwin:

    I edited your post above and removed the username and password as they looked real. If so, posting an actual username and password in a thread on a public forum is something that should be avoided.

    Thanks.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  17. #17
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,238

    Re: Access VBA to open, change & save excel file

    Quote Originally Posted by ggodwin
    Now I am getting an error on a line I had never had one before. For some reason it is erroring on this line almost as soon as the IE object opens. It is now acting as if nothing has time to load? (Run-Time Error 91) Can you think of anything that would cause me to error here? (Never had an error here before)
    Based on that behaviour, my first assumption was that the function is not working correctly - but can see no reason why, as the code is technically almost identical to what you had before.

    The only real difference is that TimeOut is declared as Date instead of a String... from a test in VB it seems that change would be perfectly fine, but there may be an issue in Access VBA. So, try changing the declaration in the function to String. If that doesn't solve it, change it back to Date.


    As you have changed the code a bit, the next step is to right-click on the code window, and select "Toggle"->"Break on all errors". I suspect an error is occurring in your loop, which (due to the way you deal with errors there) would mean that you would not be logged into the site - and that probably means that you wouldn't have the right page loaded (but some kind of page telling you to log in).

  18. #18

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Posts
    332

    Re: Access VBA to open, change & save excel file

    Quote Originally Posted by Hack
    @ggodwin:

    I edited your post above and removed the username and password as they looked real. If so, posting an actual username and password in a thread on a public forum is something that should be avoided.

    Thanks.
    Hack - Thank you!! You are a very observant moderator!! Thanks again.

    si_the_geek,
    Just for a test, I commented out all the "PauseTimeOut" Functions and ran the code. I got the same exact error. I think something in my QPR.Busy language is not working.

    I am going to try your troubleshooting idea.

  19. #19

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Posts
    332

    Re: Access VBA to open, change & save excel file

    Access VBA only gives me
    Toggle Break point (or Bookmark)

    So I set the break point here
    Code:
       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
    Which is the line just above where I am getting my error. It takes about 45 seconds to get to this point. (Which is much faster than doing manually)

    From that point on when I proceed everything seems to work. The pages load and move much faster than before. (But this concerns me)

    Back to the orginal problem, it seems the first attempt after the PC has been setting is the only time the Excel file saves the wrong thing. After once it gets it correct until a good delay. (Cleared Cache of something??). Therefore, I am still not sure if the changes made to pausetimeout function are resolved. It appears that I am not loading all pages before the next step begins.

    Is there a way I can test the PauseTimeout Function?

  20. #20
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    37,238

    Re: Access VBA to open, change & save excel file

    Quote Originally Posted by ggodwin
    si_the_geek,
    Just for a test, I commented out all the "PauseTimeOut" Functions and ran the code. I got the same exact error.
    That is to be expected - as you are removing the "wait until loaded" delay, and the error is almost certainly caused by the page either not being loaded fully, or not being the right page.

    Which is the line just above where I am getting my error. It takes about 45 seconds to get to this point. (Which is much faster than doing manually)
    That sounds like a reasonable amount of time, and unless IE takes almost that long to load (which I doubt), then it implies that the function is working - because without any pauses the rest of the code should take well under a second.


    However, in checking what code was running up to that point I spotted something that I missed earlier - you are not calling the function after the log in code, but you should be (as you did have a loop there before).

    Back to the orginal problem, it seems the first attempt after the PC has been setting is the only time the Excel file saves the wrong thing. After once it gets it correct until a good delay. (Cleared Cache of something??). Therefore, I am still not sure if the changes made to pausetimeout function are resolved. It appears that I am not loading all pages before the next step begins.

    Is there a way I can test the PauseTimeout Function?
    As mentioned earlier in this post, it seems that the function is working - so I would assume the problem is that IE doesn't give the same notification (via QPR.Busy and QPR.readyState) when loading the Excel file.

    I think you should be able to check that by looking at the progressbar on the IE window - I suspect it will be full/gone before the file is loaded.

    If that is the case, what I would recommend is making a guess as to how long it takes to load from the point the progress is complete (including the first time you run), and after calling PauseTimeOut also have the kind of loop you had originally ("'-- wait 1 minute...") for slightly longer than that amount of time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.