-
Jan 15th, 2009, 10:23 AM
#1
Thread Starter
Hyperactive Member
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.
- Open the file
- Select the first row of data
- Replace all "." with nothing (Basically just remove the "."
- Replace all "/" with nothing
- Replace all " " (Blanks with nothing) essensically just removing the blanks
- 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.
-
Jan 15th, 2009, 10:25 AM
#2
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.
-
Jan 16th, 2009, 06:09 AM
#3
Hyperactive Member
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:
Public Sub openXL()
Dim xl As Excel.Application
Dim wbk As Excel.Workbook
Dim wsht As Excel.Worksheet
Dim strFileName As String
Dim lastRow As Integer
Set xl = New Excel.Application
xl.DisplayAlerts = False
Set wbk = xl.Workbooks.Add("c:\Temps\Temp.xls")
wbk.SaveAs "c:\Temps\Temp.xls"
wbk.Close
Set xl = Nothing
End Sub
-
Jan 16th, 2009, 10:01 AM
#4
Thread Starter
Hyperactive Member
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
-
Jan 16th, 2009, 03:04 PM
#5
Re: Access VBA to open, change & save excel file
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?
-
Jan 19th, 2009, 02:38 PM
#6
Thread Starter
Hyperactive Member
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?
-
Jan 20th, 2009, 05:40 AM
#7
Hyperactive Member
Re: Access VBA to open, change & save excel file
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
-
Jan 20th, 2009, 05:56 AM
#8
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).
-
Jan 20th, 2009, 08:44 AM
#9
Thread Starter
Hyperactive Member
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.
-
Jan 20th, 2009, 09:00 AM
#10
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.
-
Jan 20th, 2009, 03:35 PM
#11
Thread Starter
Hyperactive Member
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
-
Jan 20th, 2009, 03:53 PM
#12
Thread Starter
Hyperactive Member
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?
-
Jan 20th, 2009, 04:29 PM
#13
Thread Starter
Hyperactive Member
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?
-
Jan 20th, 2009, 05:17 PM
#14
Re: Access VBA to open, change & save excel file
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.
-
Jan 20th, 2009, 08:52 PM
#15
Thread Starter
Hyperactive Member
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
-
Jan 21st, 2009, 08:14 AM
#16
Re: Access VBA to open, change & save excel file
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).
-
Jan 21st, 2009, 07:18 AM
#17
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.
-
Jan 21st, 2009, 02:29 PM
#18
Thread Starter
Hyperactive Member
Re: Access VBA to open, change & save excel file
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.
-
Jan 21st, 2009, 03:16 PM
#19
Thread Starter
Hyperactive Member
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?
-
Jan 21st, 2009, 03:38 PM
#20
Re: Access VBA to open, change & save excel file
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|