Append Information from a Website

[thechazm]

That's actually a simple fix. All you need to do is change the step number from 1 to 2. Here is the modifed piece of code highlighted in red where you need to change it.

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(Rows.Count, "A").End(xlUp).Row [COLOR=red]Step 2[/COLOR] ' 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, 1)
            ![Checks moved to escrow] = xlsSheet.Cells(i, 2)
            ![Checks accepted by host] = xlsSheet.Cells(i, 3)
            ![Checks rejected by non-hardware logic] = xlsSheet.Cells(i, 4)
            ![Checks rejected by hardware] = xlsSheet.Cells(i, 5)
            ![Number of Checks Rejected by Image Too Light] = xlsSheet.Cells(i, 6)
            ![Number of Checks Rejected by Image Too Dark] = xlsSheet.Cells(i, 7)
            ![Number of Checks Rejected by Excessive Skew] = xlsSheet.Cells(i, 8)
            ![Number of Checks Rejected by Out Of Focus] = xlsSheet.Cells(i, 9)
            ' Fill in the rest of the field information here
            .Update
        End With
    Next i
Next x
xlsApp.Quit
Set xlsSheet = Nothing
Set xlsWorkSheet = Nothing
Set xlsApp = Nothing
Exit Function
ErrHandler:
MsgBox Err.Number & " - " & Err.Description
Set xlsSheet = Nothing
Set xlsWorkSheet = Nothing
Set xlsApp = Nothing
End Function

 

[thechazm]

As for the extracting of the data from zip files I was working on it but I have some big projects I have to get out of the way before I can finish it. I had it extracting the first zip then was working on the second zip files inside but it needs more work. Anyway catcha later

 

[LadyDi]

That is perfect. Thank you so much for all your help.

 

[LadyDi]

Is there a way to make this code point directly to a specific folder? For example, if my files will always be in the C:\Report_Data folder, is there a way for the database to automatically go to this folder, instead of having to navigate to it each time? I assume, the change would be somewhere in the File Operations module, but I’m not sure where.

 

[thechazm]

Changes are in red.

Function BrowseFile(Optional strFilter As String) As Variant
Dim fdg As FileDialog, vrtSelectedItem As Variant
Dim V() As Variant
ReDim V(1 To 100)
Dim i As Long
Set fdg = Application.FileDialog(msoFileDialogFilePicker)
If strFilter <> "" Then
    fdg.Filters.Clear
    
    Select Case strFilter
        Case "Pictures"
            fdg.Filters.Add "Pictures", "*.jpg; *.gif; *.bmp"
        Case "PDF"
            fdg.Filters.Add "PDF", "*.pdf"
        Case "Excel"
            fdg.Filters.Add "Excel", "*.xls; *.xlsx"
        Case "CSV"
            fdg.Filters.Add "CSV", "*.csv"
            
    End Select
End If
With fdg
    .AllowMultiSelect = True
    [COLOR=red].InitialFileName = "C:\Whatever the folder path is"
[/COLOR]    .InitialView = msoFileDialogViewDetails
    If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems
            i = i + 1
            V(i) = vrtSelectedItem
        Next vrtSelectedItem
        Arr = V
    End If
    If .SelectedItems.Count = 0 Then
        bCanceled = True
    Else
        bCanceled = False
    End If
End With
  
Set fdg = Nothing
End Function

 

[LadyDi]

That works perfectly. Thank you again, for your assistance.

I have one more question. My manager has requested that I house my database on our department's SharePoint site. Before I put the database on SharePoint, I could upload 80 some files in less than 5 minutes. After I saved the database on SharePoint, all of a sudden, it takes over an hour to upload the same number of documents. Do you have any idea why this would be, or how to fix it?

 

[thechazm]

It's mainly because of the speed between your computer and the sharepoint site. Sharepoint is a nice idea but has its flaws as well. If I were you I would split the database into a front end and a back end on your network then you could setup permissions for other's to view/import data as well.

You need to explain to your manager that the sharepoint site is a bad idea because of what you are experiancing and offer another option like the one I stated above to allow for multi user environments.

Hope that makes sense without going into many details.

 

[Galaxiom]

Sharepoint is really only useful for synchronising versions of data among multiple users where live connections are not practical. Databases are better run live where there are no connection issues such as on a local network.

Sharepoint takes a long time because it is managing the synchronisation or at least putting the infrastructure in place to do it.

 

[LadyDi]

Is there a way to make this module select all the .csv files that are showing in the dialogue box, without the user needing to select them? After viewing the database, my manager would like me to take that step out - making fewer key strokes for the user. There can be anywhere from 75 to 85 files showing. If I change the line that reads:

For Each vrtSelectedItem In .SelectedItems
i = i + 1
V(i) = vrtSelectedItem
Next vrtSelectedItem

To

For Each vrtSelectedItem In .SelectedItems
i = i + 85
V(i) = vrtSelectedItem
Next vrtSelectedItem

will that do the trick?

 

[thechazm]

No unfortunatly not like that because once it gets to that point its just building the array to process the data. If you make the directory static and not use the browsefile function then that would do the trick.

Or (which may be the best option) is when the dialog box (or filebox) comes up for you to select the files just press CTRL+A on your keyboard. This should select all the files within that folder then just press ok. Which is only a two step process. This way you would not need to modify the code or write an entire new function.

 

[LadyDi]

I would like to use this module in a second database, but I have a couple questions first. In my new database, I want to only import data where the number in column 33 is greater than 2. I also want to import a couple columns that are hidden on the spreadsheet. Are these two things possible? Also the files I need it to import are xlsb format. Can that be added as well?

 

[thechazm]

Yes all that is just fine. You will need to modify the code a bit to make it happen. If you like just tell me which columns are hidden and where you want them to go in the table and I'll make the changes for you today.

 

[LadyDi]

Thank you so much for you help. I really appreciate it.

I have inserted two columns in the beginning of the Access table, one for Customer name and one for TPM. These two columns are in the spreadsheet, but are hidden in columns I and J. Also, the data in this spreadsheet does not start until row 30 (the column heading is in row 30, and the actual data starts in row 33).

 

[thechazm]

If you can please upload an example file of the excel file with dummy data. Also are the newly created field names called "Customer Name" and "TPM" or are they different names?

Finally the data starts on row 33 are there other rows after that if so what is the interval between them etc...

 

[LadyDi]

The new columns are titled Customer and TPM.
Yes, I do need the data after row 33, as long as the data in column AG is greater than 2. I also realized, there is one more hidden column that I need to import - column D it will go into a column called WeekEndingDate in my table.

Here is a sample spreadsheet. I removed all the macros in this workbook and am only showing the tab I need to import. The actual workbook has several tabs. However, the ATM Data tab is the only one that I care about. The tab is always labeled ATM Data.

 

[thechazm]

Ok so I made the changes I believe for what you wanted to do but remember you will need to fill out the required information and setup the tables correctly for the fields that you want to pull in.

View attachment code.accdb

 

[LadyDi]

That works perfectly. Thank you very much for all your help.

 

[thechazm]

No worries.

 

[LadyDi]

I have one more question for you. Everytime the module closes one of my workbooks, it asks me if I want to save. I will never want to save changes made to the spreadsheet. Is there a way to get the vba to automatically click "NO" to this message?

 

[LadyDi]

This project keeps getting bigger and bigger. My manager would now like me to upload text (.txt) files into this database. I am attaching a sample text file. Each column will need added to the database (I have created a new table for this), in addition to that, I also need to capture the MM9662 found at the very top of the file (that indicates what piece of equipment the text file is about). Is it possible to upload this file like we are uploading the csv files?