adding items to existing database

Carlit007

New Member
Joined
Sep 5, 2018
Messages
47
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
Hi just checking If anybody can help be with this as I have a long way to go

I have a simple VBA code that lets you import data from another workbook into active workbook by using the file open dialog.
currently the code just clears the sheet and imports all the data into active workbook.

I would like to be able to import new data onto an existing database I have built to track equipment location based on Serial numbers
this database looks similar to the one listed below (with a few other columns to track the equipment location)

SERIAL NUMBERLINMATERIALDESCRYPTIONADMINLOCATIONDATE ADDED
2HH1GK270209N01C945562COMPUTER 7050NGMAMDLC3WK01ROOM 11112/25/2021
2UA64134QK70209N01X409459COMPUTER HP PRODESK 600 G2 SFFNGMAWK244700001ROOM 2105/4/2020
2UA64134QP70209N01X409459COMPUTER HP PRODESK 600 G2 SFFNGMAWK244700002ROOM 1104/11/2019
2UA64035R470209N01X409459COMPUTER HP PRODESK 600 G2 SFFNGMAWK301700001ROO 1198/7/2021



I want to be able to click on a button and get the fileopen dialog to let me open and import from another report that has both a mix of new & old equipment this report changes very so often and I just want to grab whatever is new to be able to track.

Note every item has a unique Serial number on column ( A) If the data already exist on my equipment tracker database I would like for it to not be added/Overwritten when imported from the new sheet with the opefile dialog a bonus would be to include the date added for every new item on column G

Ideally I would like to use a Dictionary but am open for any other solution

the code that I'm currently using to import is as follow;


VBA Code:
Sub ImportNewEquip()
   Dim Fname As Variant
   Dim Wbk As Workbook
   Dim Ws As Worksheet
   
   Set Ws = ActiveSheet
   Fname = Application.GetOpenFilename
   If Fname = "" Then Exit Sub
   Set Wbk = Workbooks.Open(Fname)
   Ws.UsedRange.Offset(1).Clear
   Wbk.Sheets("Sheet1").UsedRange.Offset(1).Copy Ws.Range("A2")
   Wbk.Close False

End Sub

Looking forward to seeing what the best way to solve this VBA problem is

thanks in advance
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
then you will need a Microsoft access database.
It will let you import only New data, or just the changes (via queries).
Excel does not do this.
 
Upvote 0
then you will need a Microsoft access database.
It will let you import only New data, or just the changes (via queries).
Excel does not do this.

ranman256 I understand Access would be a better software for this.. unfortunately I am not too familiar with the program and already invested a lot of time learning VBA & excel, I am trying to stay in a platform everybody is already familiar with at work.​

 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top