Code to append database Please, please help me

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Code to append database Please, please help me

  1. #1
    New Member
    Join Date
    Jun 2003
    Location
    Cape Town
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code to append database Please, please help me



    Hi - I'm new but desperate

    I receive on a daily basis a number of data form different places (each with a unique ID). The data is cumulative meaning that a may skip one day and the next day data include all previous dates.

    On receiving I copy all the data in one xls file in one sheet (I identify them with the ID and date)

    I then append a small database (xl) with all the IDs new date or dates.

    I try to write VBA code to first look in a small database (xl) for ID (unique number) and the date for that ID.

    If the date is present the code does not append data for that ID and date to the database

    If the date for that ID is not present it must append the database with that date and IDs data.


    BUT this is above my fireplace and I battle for weeks without success.

    Is there someone that can help me with this O! Youll make my day, month, and year!!

    Thanks in the meantime

    NS - I can e-mail you a sample if you like - yust give me the e-mail adress

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to append database – Please, please help

    can you give us the path of your file (data received) and the path of the database? Sheet name of database - which column within both the database and received data contains the unique ID, date etc?

    With that we can give you some code.

    Also, how many rows of data do you have in your database?
    Does my a$$ look big in this picture ?

  3. #3
    New Member
    Join Date
    Jun 2003
    Location
    Cape Town
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to append database Please, please help me

    The paths is

    Received data = c:\dailyfiles\income\[file names]
    Database = c:\dailyfiles\database\dailydata.xls

    File names is

    Recieved data = daydatatemp.xls
    database = dailydata.xls

    Sheet name is

    Recieved data = sheet1
    Database = data

    Column is

    In all the files = In Column A is the ID
    In column B is the Date

    There is data from 1999 and the no of rows is at 6187


    Hope this will get me there

  4. #4
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to append database – Please, please help

    OK so let's presume we're starting in the received data file.

    Code:
    Sub CHECK_DB()
    
    NM = ActiveWorkbook.Name 'will return daydatatemp.xls
    Workbooks.Open("C:\dailyfiles\database\dailydata.xls") 'open database
    Sheets("data").Select 'activate database sheet
    Windows(NM).Activate 'return to received data file
    Sheets("sheet1").Select 'select data sheet in daydatatemp.xls
    
    'Create a loop to go through ID's of data
    n = 1999 'assuming 1999 was first row of ID?
    Do Until Cells(n,1) = "" 'continue looping through IDs until A becomes blank
    IDNO = Cells(n,1) 'set id no
    IDDATE1 = Cells(n,2) 'set date
    Windows("dailydata.xls").Activate 'return to database
    On Error GoTo 100
    x = Application.WorksheetFunction.Match(IDNO,Sheets("data").Range("A:A"),0)
    IDDATE 2= Cells(x,2) 'gets date of ID from db
    If IDDATE2 = "" then 'no date therefore put data in
    Cells(x,2) = IDDATE1 'insert date from received date file
    End If
    100
    Windows(NM).Activate 'return to received date file
    n = n + 1 'increase row identifier and loop to next ID
    Loop
    
    End Sub
    I must confess that I've not tested this - will do so now.

    EDIT - works fine for me. in VBA go Tools - Options - General - ensure you select Break on Unhandled Errors (as you know have an error handler there - if there's no match if jumps to the next ID)
    Does my a$$ look big in this picture ?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com