Code to append database Please, please help me :: MrExcel Message Board



 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Code to append database Please, please help me
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

Excel Nebula
Welcome to the Board


Joined: 12 Jun 2003
Posts: 3
Location: Cape Town
Flag: Southafrica

Status: Offline

 Reply with quote  

Code to append database Please, please help me

icon_help.gif icon_help.gif icon_help.gif icon_help.gif icon_help.gif icon_help.gif icon_help.gif icon_banghead.gif icon_banghead.gif icon_banghead.gif

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

Post Thu Jun 12, 2003 7:51 am 
 View user's profile Send private message Send e-mail

lasw10
Board Master
Board  Master


Joined: 07 Sep 2002
Posts: 1936
Location: TCR, London, UK
Flag: England

Status: Offline

 Reply with quote  

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?

Post Thu Jun 12, 2003 8:42 am 
 View user's profile Send private message Send e-mail Visit poster's website

Excel Nebula
Welcome to the Board


Joined: 12 Jun 2003
Posts: 3
Location: Cape Town
Flag: Southafrica

Status: Offline

 Reply with quote  

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

Post Thu Jun 12, 2003 8:58 am 
 View user's profile Send private message Send e-mail

lasw10
Board Master
Board  Master


Joined: 07 Sep 2002
Posts: 1936
Location: TCR, London, UK
Flag: England

Status: Offline

 Reply with quote  

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)

Post Thu Jun 12, 2003 9:08 am 
 View user's profile Send private message Send e-mail Visit poster's website
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.