Userform Database Help

[kevinwong1991]

Hi,

I have a database which gets updated once a month, with a large inflow of new data. There are mutiple rows and columns that are to be added.

What happens every month is that a report is run with several columns, and then is to be copy and pasted into the database. However, the database has additional columns added within it, so that copy and pasting columns become somewhat tedious.

Is it possible to have a userform which will be able to identify column breaks and paste into a defined column in the database?


Will this work for multiple rows too?
Thanks!

 

[bensonsearch]

hi,

is the database in excel or access?

 

[kevinwong1991]

Hi there,

It's in Excel.

 

[bensonsearch]

so let me try and see if i have this right.

you wish to have a form to deal with new data on a different excel file and move it to the "database" file automatically?

if so no real need for a userform unless you need some kind of user input

 

[kevinwong1991]

Sorry I dont think I was being too clear.

I have a worksheet named "database" in my workbook. New data is to be added to this sheet every month, from external data run from a different program. That program comes up with an excel file which needs to be integrated with the "database" worksheet in the workbook.

The "database" worksheet has more columns added in than the in data file which the external program provides. As a result, I'm trying to find a way to easily copy and paste the data from the external data file and add it in to the "database" worksheet.

Is there a way to do this? I'm not sure if userforms are the way to go. I was thinking that because you can use the offset property but I'm not sure.

 

[bensonsearch]

ok, i understand.

is this workbook that gets the new data always named the same?

do you wish to copy all the data in this new data workbook?

and are the coloums of data setup the same (minus the extra ones in database)?

 

[kevinwong1991]

Yes to all : )

 

[bensonsearch]

ok something like this changed to fit your needs

[COLOR=black]'Opens source file
Workbooks.Open ("d:\test\Survey Extract Agents.xls")

'copy sheet from another workbook before first tab of Book2
Workbooks("Survey Extract Agents.xls").Sheets("SurveyExtractAgentsSheet").Copy After:=Workbooks("Raw_Test_Data.xls").Sheets("RawTestData")
'Sheets("SurveyExtractAgentsSheet").Select
Sheets("SurveyExtractAgentsSheet").Name = "SurveyData"
[/COLOR]

this was taken from here: http://www.mrexcel.com/forum/showthread.php?t=318078&page=2