Append Information from a Website

[thechazm]

Add the following red lines to your function in the module at the exact spot I have shown here and it should resolve the issue. But if you keep getting error messages to get to that point then that needs figured out as well.

Function GetDataFromExcelSheets(lSheetNumber As Long)
Dim xlsApp As Excel.Application, xlsWorkBook As Excel.Workbook, xlsSheet As Excel.Worksheet
Dim db As Database, rs As DAO.Recordset
ReDim Arr(1 To 100)
'On Error GoTo ErrHandler
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from [IDMSummary]", dbOpenDynaset)
BrowseFile "CSV"
Set xlsApp = New Excel.Application
For x = LBound(Arr) To UBound(Arr)
    If CStr(Arr(x)) = "" Then Exit For
    Set xlsWorkBook = xlsApp.Workbooks.Open(CStr(Arr(x)))
    Set xlsSheet = xlsApp.Worksheets(lSheetNumber)
    For i = 2 To xlsSheet.Cells(xlsSheet.Rows.Count, "A").End(xlUp).Row Step 2 ' Starts at row 3 in excel sheet and gets a total row count on column O. &
    'If your longest column is different then change the letter to that column. Also if your data starts on a different row make sure to change the 3 to whatever number it needs to be.
        ' Do operations here on the excel sheets
        'Debug.Print xlsSheet.Cells(i, 15) ' Prints out column 15 data in the immediate window
        With rs
            .AddNew
            ![SpreadsheetName] = xlsSheet.Name
            ![Total Number of checks inserted into device] = xlsSheet.Cells(i, 3)
            ![Checks moved to escrow] = xlsSheet.Cells(i, 4)
            ![Checks accepted by host] = xlsSheet.Cells(i, 5)
            ![Checks rejected by non-hardware logic] = xlsSheet.Cells(i, 6)
            ![Checks rejected by hardware] = xlsSheet.Cells(i, 7)
            ![Number of Checks Rejected by Image Too Light] = xlsSheet.Cells(i, 8)
            ![Number of Checks Rejected by Image Too Dark] = xlsSheet.Cells(i, 9)
            ![Number of Checks Rejected by Excessive Skew] = xlsSheet.Cells(i, 10)
            ![Number of Checks Rejected by Out Of Focus] = xlsSheet.Cells(i, 11)
            ![Number of Checks Rejected by Payee Endorsement Presence] = xlsSheet.Cells(i, 12)
            ![Number of Checks Rejected by Piggyback] = xlsSheet.Cells(i, 13)
            ![Number of Checks Rejected by Signature Presence] = xlsSheet.Cells(i, 14)
            ![Number of Checks Rejected without magnetics] = xlsSheet.Cells(i, 15)
            ![Number of Deposit Slip Rejects] = xlsSheet.Cells(i, 16)
            ![Number of Travelers Check Rejects] = xlsSheet.Cells(i, 17)
            ![Number of IRD Rejects] = xlsSheet.Cells(i, 18)
            ![Number of Saving Bond Rejects] = xlsSheet.Cells(i, 19)
            ![Manual amount entry required] = xlsSheet.Cells(i, 20)
            ![Hardware Document Return Reason Too Short (1)] = xlsSheet.Cells(i, 21)
            ![Hardware Document Return Reason Too Long (2)] = xlsSheet.Cells(i, 22)
            ![Hardware Document Return Reason Too Narrow (3)] = xlsSheet.Cells(i, 23)
            ![Hardware Document Return Reason Multiples (4)] = xlsSheet.Cells(i, 24)
            ![Hardware Document Return Reason Can't Align (5)] = xlsSheet.Cells(i, 25)
            ![Hardware Document Return Reason Can't Move to Align (6)] = xlsSheet.Cells(i, 26)
            ![Hardware Document Return Reason Shutter Failed to Close (7)] = xlsSheet.Cells(i, 27)
            ![Hardware Document Return Reason Can't Move to Escrow (8)] = xlsSheet.Cells(i, 28)
            ![Hardware Document Return Reason Height Rejection (9)] = xlsSheet.Cells(i, 29)
            ![Hardware Document Return Reason Document too wide (10)] = xlsSheet.Cells(i, 30)
            ![Hardware Document Return Reason UDD Learning Document (11)] = xlsSheet.Cells(i, 31)
            ![Hardware Document Return Reason UDD Learning Failure (12)] = xlsSheet.Cells(i, 32)
            ![Hardware Document Return Reason UDD Learning Done (13)] = xlsSheet.Cells(i, 33)
            ![Hardware Document Return Reason Last in first out reject (14)] = xlsSheet.Cells(i, 34)
            ![Hardware Document Return Reason Processing failure (15)] = xlsSheet.Cells(i, 35)
            ![Hardware Document Return Reason Unknown Reason (100)] = xlsSheet.Cells(i, 36)
            ![Hardware Media Return Reason Too Short (1)] = xlsSheet.Cells(i, 37)
            ![Hardware Media Return Reason Too Long (2)] = xlsSheet.Cells(i, 38)
            ![Hardware Media Return Reason Can't Strip (3)] = xlsSheet.Cells(i, 39)
            ![Hardware Media Return Reason Can't Pick (4)] = xlsSheet.Cells(i, 40)
            ![Hardware Media Return Reason Max Documents on Escrow (5)] = xlsSheet.Cells(i, 41)
            ![Hardware Media Return Reason Irregular Media (6)] = xlsSheet.Cells(i, 42)
            ![Hardware Media Return Reason Document Stopped (7)] = xlsSheet.Cells(i, 43)
            ![Hardware Media Return Reason Can't Move to Pick (8)] = xlsSheet.Cells(i, 44)
            ![Hardware Media Return Reason Can't Clamp Documents (9)] = xlsSheet.Cells(i, 45)
            ![Hardware Media Return Reason Can't Close Shutter (10)] = xlsSheet.Cells(i, 46)
            ![Hardware Media Return Reason Processing Error (11)] = xlsSheet.Cells(i, 47)
            ![Hardware Media Return Reason Document too Narrow (12)] = xlsSheet.Cells(i, 48)
            ![Hardware Media Return Reason Failed to Align Transport (13)] = xlsSheet.Cells(i, 49)
            ![Hardware Media Return Reason Unknown Reason (100)] = xlsSheet.Cells(i, 50)
            ![Total number of Store Transactions that were started] = xlsSheet.Cells(i, 51)
            ![Total number of DA Transactions stored in DB] = xlsSheet.Cells(i, 52)
            ![Total number of Cash and Check Items stored in DB] = xlsSheet.Cells(i, 53)
            ![Total number of DA Transactions that failed to be stored in DB] = xlsSheet.Cells(i, 54)
            ![Total number of Cash and Check Items that failed to be stored in] = xlsSheet.Cells(i, 55)
            ' Fill in the rest of the field information here
            .Update
        End With
    Next i
Next x
 
[COLOR=red]rs.close[/COLOR]
[COLOR=red]set rs = nothing[/COLOR]
[COLOR=red]set db = nothing[/COLOR]
 
xlsApp.Quit
Set xlsSheet = Nothing
Set xlsWorkSheet = Nothing
Set xlsApp = Nothing
Exit Function
ErrHandler:
MsgBox Err.Number & " - " & Err.Description
 
[COLOR=red]rs.close[/COLOR]
[COLOR=red]set rs = nothing[/COLOR]
[COLOR=red]set db = nothing[/COLOR]
[COLOR=red][/COLOR] 
[COLOR=red]xlsApp.Quit[/COLOR]
Set xlsSheet = Nothing
Set xlsWorkSheet = Nothing
Set xlsApp = Nothing
End Function

 

[LadyDi]

I added the lines you suggested, and now I'm getting a different error message. Now it says "Run Time Error 429: ActiveX component can't create object". What should I do next?

 

[thechazm]

I'll upload a new database sometime either today or tomorrow. It just sounds like a misunderstanding because the lines of code I put in red are only for closing an object not creating one.

 

[thechazm]

Attached to this post should have the modified code. If this does not work then send me a few sample files and I'll debug the code.

 

[LadyDi]

That works perfectly. Thank you so much.

 

[thechazm]

Your welcome and good luck

 

[LadyDi]

I just got Windows 7 installed on my computer and now I am having a little difficulty with the File Operations module. It all seems to work fine with the exception of selecting multiple files at once. For some reason, now it will only allow me to select one file at a time. I haven't changed anything in my programming and I still have Access 2010. The only thing that has changed is the operating system. I had Windows XP and now I have Windows 7 (32 bit). Is there something I need to add to my code to allow this to work in Windows 7?

 

[thechazm]

I don't know for sure but I'll take a look today.

 

[thechazm]

Hmm I just tested it on a Windows 7 machine and it worked just fine. Does it give you errors when you try and select multiple files or does it just not let you select multiple files?

Also did you make sure you created the folder on your C drive that says Report_Data

?

 

[LadyDi]

I don't get an error message, it just doesn't let me select multiple files.

Yes, I have the Report_Data folder on my C drive.

 

[LadyDi]

I wasn't sure if it would help or not, but I am attaching snap shots of how the Windows Explorer looks when open from the database and when open from My Computer. I don't know if the difference in appearance is why I can't select multiple files at once or not.

 

[thechazm]

The FileDialog Class and it's property .AllowMutiSelect (which is set to true in the function) are appart of the Office Library. I tested this on a Windows 7 machine and it worked correctly. I can't imagine what exactly could be wrong as I believe even if they did a minimal install of office on your machine it should still work.

I would recommend trying it on a different windows 7 machine and if that does not work then either install the complete office program instead of minimal or maybe run a repair on your office that you have now.

This problem seems very weird and I will try to do some research on if anyone else has had this problem.

 

[thechazm]

Hmm can you make a copy of your database and dump out the data and post it here so I can have a look at the code? I used the old one we had I would just like to review a few things but as far as I can see there are no problems.

 

[LadyDi]

Here is a copy of the database I am having trouble with. This is actually a different database than what was posted previously, but it is the same module. Thank you very much for your help. I really appreciate it.

 

[thechazm]

Can you remove your password protection on the VBA modules so I can look and repost the database please. BTW I like the main form Looks like you been busy

 

[LadyDi]

Thank you

Sorry, I forgot about the password.

 

[thechazm]

Ok try this one.

View attachment Copy WOW Data.zip

I noticed that the program would not compile so I debugged and noticed it had a missing reference for the Microsoft DAO 3.6 Library. This should have not directly affected the FileDialog library but was a necessary addition for it to work.

Please let me know if this fixes the issue.

Thanks

 

[LadyDi]

Thank you very much for checking into this for me. Unfortunately, it is still doing the same thing. It opens the Browse dialog box, but it won't let me select more than one file at a time.

Could it have anything to do with the fact that the folder originated on a SharePoint site? I tried substituting a folder that was on my hard drive and it worked like it was supposed to.

 

[LadyDi]

I had a question about the ExcelFunctions module. I currently have it set up to import every other line starting from line 2 from the Excel sheet into the database. The reason for this is that the people who generate the Excel files show the column headings above each row of data. I just received a file from them where they duplicated the headings. As a result, the data I wanted to import was actually on row three. This is the first time this has happened and it only happened on one of 3,000 Excel files. Is there a way to program this to look through the Excel sheet and start on the first line that has a number in column D?

 

[thechazm]

Yes you should be able to scan the couple of rows and use a function called isnumeric() and evaluate to see where the actual rows of numbers start. Depending on how many rows it had to scan down then that should tell you how many rows it needs to step through.

After you have determined that then you can make the step 2 statement simply use a variable like R and just say step R which would then have it step dynamically based on the differences in the file.

Did you figure out the multiple select issue with sharepoint?