Uploading an Excel Spreadsheet Then Pull Info from that Spreadsheet to Another Spreadsheet or Access Database

Greetings Experts.  I really really need some help and guidance here.  What I am trying to do is enable my users the ability to upload a spreadsheet to a specified directory.  Once the spreadsheet has been uploaded, have a process that will read that spreadsheet and pull information from certain columns to a "Master" spreadsheet or an Access Database.  The "Master" spreadsheet or Access database will be the collected data from each uploaded spreadsheet.  Once the user has uploaded the spreadsheet and the data has been collected successfully, the uploaded file will be deleted to make room for the next user to upload.  Please help!!

This will be in production with a department of 20 personnel.  The Master sheet will be processed every 10 minutes or so into an Oracle database.  I have the Oracle process already developed.  As of now, my users have to manually process each spreadsheet; therefore, I am attempting to consolidate this and have the data validated before it gets pushed to the Oracle database.  This will occur when the process runs every 10 minutes or so.

What is the best approach here?  The spreadsheets that are uploaded to this directory are not setup identically.  What I mean is that each column may be in different locations.  Or there may be more data than needed.  Is there a way that I can look for column headings and pull data based on that? Thank you everyone for your help with this.
Christopher WrightDirector, Service DeliveryAsked:
Who is Participating?
Jim P.Connect With a Mentor Commented:
Okay. Assuming every spreadsheet has a header line then you can use the following code to attach the SS to Access and import it into a "holding" table for further processing.

Public Function Q_28246095_AttachAndImporSheet()

Dim Path As String
Dim FileName As String

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim OutRS As DAO.Recordset
Dim TableName As DAO.TableDef
Dim FieldName As DAO.Field
Dim TableRS As DAO.Recordset
Dim SQL As String
Dim TblName As String
Dim FieldProperty As Property

Dim I As Integer

'If the user is running this and has the file in their "My Documents" folder _
 otherwise change the path variable.
Path = Environ("USERPROFILE")
FileName = "NameOfFile.xls"

If Dir(Path & FileName, vbDirectory) <> "" Then
    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "ExcelSheet", Path & FileName, True, "Sheet1"
    Exit Function
End If

TblName = "MyTempTable"
I = 0
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("ExcelSheet")
'I've attached the DoesTblExist function.
If DoesTblExist(TblName) = False Then
'Create the table to write to.
    Set TableName = DB.CreateTableDef(TblName)
        With TableName
            For I = 0 To RS.Fields.count - 1
                .Fields.Append .CreateField(RS.Fields(I).NAME, dbText, 255)
            Next I
        End With
    I = 0
        With TableName
            For I = 0 To RS.Fields.count - 1
                .Fields(RS.Fields(I).NAME).AllowZeroLength = True
            Next I
        End With
    DB.TableDefs.Append TableName
End If

Set OutRS = DB.OpenRecordset(TblName)

If RS.EOF = False Then
    Do Until RS.EOF = True
        With OutRS
            For I = 0 To RS.Fields.count - 1
                .Fields(I).Value = RS.Fields(I).Value
            Next I
        End With
        I = 0
End If


Set RS = Nothing
Set OutRS = Nothing
Set DB = Nothing

DoCmd.DeleteObject acTable, "ExcelSheet"

End Function

Open in new window

'The DoesTblExist function validates the existence of a TableDef
'object in the current database. The result determines if an
'object should be appended or its Connect property refreshed.

Public Function DoesTblExist(strTblName As String) As Boolean
    On Error Resume Next
    Dim DB As Database, tbl As TableDef
    Set DB = CurrentDb
    Set tbl = DB.TableDefs(strTblName)
    If Err.NUMBER = 3265 Then   ' Item not found.
       DoesTblExist = False
       Exit Function
    End If
    DoesTblExist = True
End Function

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
You seem to have more than one question here:
1. upload a spreadsheet to a specified directory
2. a process that will read that spreadsheet and pull information from certain columns to a "Master" spreadsheet or an Access Database.
3. uploaded file will be deleted to make room for the next user to upload.

Can you perhaps narrow the focus of this question to #1, then post separate, sequential questions for 2 and 3?

As it stands now, (3 questions) it would be difficult to determine what form an "Answer" would take here...

But to be open minded, I can help with 3:to delete a file in VBA you can try code like this:
    Kill "C:\YourFolder\YourFile.xls"
...However, "Kill" might be a restricted VBA command on your system, ...Then you would have to use code like this:
    Dim fso As New FileSystemObject
        fso.DeleteFile "C:\YourFolder\YourFile.xls"
(You will to add a reference to the "Microsoft Scripting Runtime library" to your VBA editor.)

Christopher WrightDirector, Service DeliveryAuthor Commented:
Roger that, I will do.  To clarify, I already have the process of upload the file to the directory.  What I need is the ability to actually read that document once it has been uploaded.  Should I post another question to deal with the second portion of this question?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Jeffrey CoachmanMIS LiasonCommented:
lets wait till early in the week to see if another expert chimes in...
Christopher WrightDirector, Service DeliveryAuthor Commented:
Thanks Jeff!
slubekConnect With a Mentor Commented:
1. Uploading files should be done by client (user) on his side and importing on server side. I would create two different processes for these tasks. If you want to ask question about uploading file by user, you should tell us which protocol you want to use (http/ftp/SMB etc).

2. For reading data from many Excel files to Access database I'm using this VBA (in Access module):
Public Const Rootdir = "C:\Data\ExcelFiles\"
Public File As String
Public name, code, city as string
Sub ReadRootDir()

    Set dbFirms = CurrentDb
    Set rst = dbFirms.OpenRecordset("Firms")

    Set xlApp = CreateObject("Excel.Application")
    File = dir$(Rootdir & "*.xls")
    Do While File <> ""
        If File <> "" Then
            ReadFile (File)
            File = dir$
        End If
    Set xlApp = Nothing

    Set rst = Nothing
    Set dbFirms = Nothing
    'MsgBox "End!"
End Sub

Sub ReadFile(n As String)
    FileName = n
    On Error GoTo ImportError
        xlApp.Workbooks.Open rootdir & n, UpdateLinks = False, ReadOnly = True
    On Error GoTo 0

    With xlApp.Workbooks(1).Sheets(1)
        name = .cells(1, 1)
        code = .cells(5, 3)
        city = .cells(6, 3)

        rst("file").Value = FileName
        rst("name").Value = name
        rst("code").Value = code
        rst("city").Value = city
        ' ...
    End With
    ' Close Excel window
    xlApp.Workbooks(1).Saved = True
    MoveTo ("Done")
    Exit Sub
End If

GoTo koniec
   MoveTo ("Error")

End Sub

Sub MoveTo(k As String)

If Not FolderExists(Rootdir & k) Then MkDir Rootdir & k

On Error GoTo FileError
    Name Rootdir & Plik As Rootdir & k & "\" & File
On Error GoTo 0

    GoTo FileEnd
    If Err = 75 Then
        Resume Next
    End If

End Sub

Open in new window

It reads all *.xls files in C:\Data\ExcelFiles folder and put data from then into Firms table in Access database in which the module is run. Because I can't delete files after processing, script moves them to Done\ forlder.

My input files have strict strructure (name in A1, city in C6 etc.). If your files have different structure, you need to enumerate cells with some kind of loop.

PS.My code is much more complex (and variables' names are in polish), so I had to cut some of it to show you. Sorry if above code has syntax errors.
Christopher WrightDirector, Service DeliveryAuthor Commented:
Hi slubek, thanks for the GREAT info sir.  To answer your question in terms of protocols to use, I am totally open. Our Oracle DBA's have used SMB for file processes between Oracle and a network share, so I would continue with that.  Unless you think I would be better suited to with another protocol.  I will test your code out here in a bit.  Thank you so much for the help again!!
Is the MS Access database just a go-between for Oracle?  If so, couldn't you just import the spreadsheets directly into Oracle?  If there is some other purpose for the Access database, could you not import the spreadsheets into Oracle and then have Oracle insert the appropriate rows into Access?
If you can use SMB, uploading file is as simple as copying it to another folder (it can be network disk or SMB resource: \\server\resource\folder\file.xls). You can use Name function in VBA (see my SUB MoveTo - BTW, replace Plik with File).
But if your clients are not within LAN, you cannot use SMB. I don't know how to use http or ftp from VBA, so you'd better ask another question.
Christopher WrightDirector, Service DeliveryAuthor Commented:
Hi Alex and slubek.  In response to Thread ID's: 39515039 & 39515572:

- Yes, the Access Database is a go between for Oracle. The reason for this is to protect the database from direct user input.  Users do not have write access nor are they a high skilled group.  It would be a nightmare to allow them full reign to the database or to allow them a way of uploading files.  There are a select few of us that are currently using SMB - samba to do just that.  We save our files to a certain name (.csv) then save to a specified drive, then run the appropriate process name in Oracle.  The rest is automated since Oracle grabs the .csv file and it disappears from the drive and is now in the Oracle table space.  I would love the ability to do that for this project; however, for now, I feel that Access is a good buffer for me to validate what has been attempted to upload.  Thanks
Christopher WrightDirector, Service DeliveryAuthor Commented:
Thanks for the code to test jimpen.  I sincerely appreciate the help.  I od have one question, is there an alternate way to use this script via ADO instead of DAO?  I'm running MO 2010 in 64 bit. My users range from MO 2007 - MO 2013.  Both 32 bit and 64 bit.  Just in case you were curious. Thanks again!
Jim P.Commented:
I'm sure there is ADO coding for it as well, but I'm not familiar with it off hand.

But all you have to do is go into the VBA window, find Tools --> References and turn on the DAO library. It is by DB not by the Office/Access configuration. So once it is turned on, it should work for everybody.
Christopher WrightDirector, Service DeliveryAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 350 points for jimpen's comment #a39637421
Assisted answer: 150 points for slubek's comment #a39514061
Assisted answer: 0 points for xfitguru's comment #a39641875

for the following reason:

Thanks for the help with this Gents!!
Christopher WrightDirector, Service DeliveryAuthor Commented:
Thanks for all of your help and support with this gentlemen!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.