Need Help with Separating Data From Excel File

[cew75]

Hey guys, I'm not sure if Im posting in the right section, but I really hope you guys can help me with this!!! I work at a college & I have an excel file for my contracts that we do to pay the professors for the classes they teach in the summer. The file is a list of the contracts created & I import this file into my access database. A contract can have up to 10 courses on it. So the main identifier in the excel file is the WFID which is the contract ID & 1 row is created for that WFID. However, the CRN is the identifying factor for me, so I need an individual row for each CRN, rather than an individual row for a WFID # with multiple CRNs. I hope this makes sense...


Does anyone know how I could separate the data in excel or have access do it for me? Right now I have to do alot of sorting, copying, pasting & deleting in the excel file in order to get what I want, which takes quite a bit of time since this file typically has over 500 rows of data... So I very much appreciate any help you guys can give!!


Oh & how do I attach the excel file so you to get an idea of what I need?


Thanks again guys!!

Also posted here https://www.mrexcel.com/forum/excel-questions/1080848-need-help-separating-data-excel-file.html

 

[UncleCatwoman]

I do this a lot. Between the clients I program for we probably import 100 different Excel files in Access each day.

Here's the basic framework of how I do it. You can expand and tweak it for your specific needs:

Set this up in Access.

' declare variables
Dim xlApp as Excel.Application
dim strFileName as String
Dim xlWB as Excel.Workbook
Dim xlWS as Excel.Worksheet
Dim intHeaderRow as Integer
Dim strColWFID as String
Dim strColCRN(10) as String
Dim dbMe as Database
dim rsT as DAO.Recordset
Dim intRow as Integer
Dim intBlanks as Integer
dim intLooper as Integer


' open an instance of Excel and the Excel file:
Set xlApp = New Excel.Application
xlApp.Visible = True (set this to False once you have it working)
strFile = "C:\FileLocation\FileName.xlsx"
Set xlWB = xlApp.Workbooks.Open(strFile)
Set xlWS = xlWB.Sheets("AppropriateSheetName")

' here is where I find the header row and set the column variables.
' since it sounds like you have full control over the spreadsheet you can tweak the format to meet your needs and hardcode the columns in the code
' as such, I'm just going to assume the CRN columns and assign the column variables.  You can expand on this if you need to
intHeaderRow = 1
strColWFID = "A"
strColCRN(1) = "B"
strColCRN(2) = "C"
strColCRN(3) = "D"
strColCRN(4) = "E"
strColCRN(5) = "F"
strColCRN(6) = "G"
strColCRN(7) = "H"
strColCRN(8) = "I"
strColCRN(9) = "J"
strColCRN(10) = "K"

' if you need to purge the Access table before running this process, do it here

' then
' open the Access table as a recordset
Set dbMe = CurrentDb
Set rsT = dbMe.OpenRecordset("AccessTableName")

' loop through the spreadsheet and add the records
' OK, I'm going to assume that whether or not there is a WFID is whether there is data in the Excel row.
' and we're going to loop the code until we hit five records without data in a row
intRow = intHeaderRow
intBlanks = 0
Do Until intBlanks >= 5
   ' move to the next record
   intRow = intRow+1
   ' is there a WFID in the record?
   If Nz(xlWS.Range(strColWFID & intRow).Value,"") <> "" Then
      ' if so, loop through all of the CRN fields and, if there is a value, add it to the Access table
      intLooper = 1
      For intLooper = 1 to 10
         If Nz(xlWS.Range(strColCRN(intLooper)  & intRow).Value,"") <> "" Then
             rsT.AddNew
            rst!WFID = xlWS.Range(strColWFID & intRow).Value
            rsT!CRN = xlWS.Range(strColCRN(intLooper) & intRow).Value
            rsT.Update
         End If
      Next intLooper
   ' otherwise ...
   Else
      intBlanks = intBlanks +1
      If intBlanks >= 5  Then Exit Do
   End If
Loop

Then close and deallocate all of the Excel and recordset objects.


I hope this helps!

 

[cew75]

UncleCatwoman... Thank you so much foir taking the time to do this. I'm excited to know you do this kind of query alot!! I am attaching a link to some files, and I am hoping you could set up what you are talking about in the database Im including because I'm just a little confused... Maybe if I see what you do in the database I'll understand..

I have included 3 files in this folder...

Original WWFID.xslx is the way I get the data originally... This is the file I would like to have changed.

upWWFID. xslx is just an example of how I need the data to look at the end... It has 1 CRN column instead of 10..

UncleCatwoman DB is just a blank database for you put your code in so I can see exactly what you mean..

If you can take the time to do that for me I would be forever grateful..

https://www.dropbox.com/sh/ezugwehuw33nxh4/AAAAqWmGVBXZIXEhF9wmA9Tka?dl=0

Thanks so much for what you've done already..

 

[UncleCatwoman]

Can you give me rights to upload the file to the DropBox folder?

 

[cew75]

Dropbox wanted me to sign up for Business account so I had to move the folder to OneDrive to give you Upload access...

Here is the new link

https://1drv.ms/f/s!AgOhQFYyJtlCwDsqwVKs7tv-adkx

Can you give me rights to upload the file to the DropBox folder?

 

[UncleCatwoman]

Uploaded the database.

Couple of things:

> I saved the file (to import) as C:\WWFID\WWFID.xlsx. Either add the folder to your machine or change the path in the code.

> I imported the 'updated' file as table tbl_WWFID in the database

> Depending on the version of Access you're running, you may have to add the code references for Excel and DAO.

> I added the CRN fields and a few more for examples to the code but you're going to have to add all of the rest of the fields. Just do the same thing I did. You'll also want to expand the error handler.

Hope this helps! Please let me know if you have any questions or if something doesn't work.