Code to append database – Please, please help me

[Excel Nebula]

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 ID’s 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 ID’s data.


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

Is there someone that can help me with this – O! You’ll 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

 

[DonkeyOte]

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?

 

[Excel Nebula]

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

 

[DonkeyOte]

Re: Code to append database – Please, please help

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

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)