Exporting copied data from Excel into Access

NewbieMan

New Member
Joined
Nov 25, 2017
Messages
33
Hi

I unfortunately have little experience with Access and just learning VBA which I am enjoy but I am quite stuck on trying to import data from Excel to Access. The board has some info from previous posts but its not really resonating with me at this point. I have the following code ...

Option Explicit
Dim AccessApp


Sub CreatingRealWorldCoaches()


Set AccessApp = CreateObject("Access.Application")
AccessApp.Visible = True


Call AccessApp.OpenCurrentDatabase("C:\a\b\c\d\e\f.mdb")


Dim TeamID As Long
Dim foundRow As Long




AccessApp.DoCmd.opentable ("Coaches")


'user manually locates the TeamID and inputs the number into the inputbox, closes access and then macro continues to run

TeamID = InputBox(prompt:="Type the TeamID for your coach in the box below", Title:="Importing Coaches")
foundRow = Columns("C:C").Find(what:=TeamID).Row
ActiveSheet.Range("A3:BC" & foundRow - 1).Copy

Set AccessApp = CreateObject("Access.Application")
AccessApp.Visible = True


Call AccessApp.OpenCurrentDatabase("C:\a\b\c\d\e\f.mdb")

'AccessApp.DoCmd.opentable ("Coaches")

'OR
'AccessApp.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Coaches", "C:\Users\User\Desktop\DDSCB2017 ACCESS MACRO TESTING\Real World Coaches.xlsm", False


MsgBox "Worksheet imported"

End Sub

Here is what is happening...with a current open spreadsheet in Excel, (lets call it "Spreadsheet A") I run the macro...

whats happening so far is that
1) a "Coaches" table in Access opens,
2) the user then locates the TeamID value within that Access table manually
3) user then manually closes Access (keeping in mind the TeamID value)
4) user types in the TeamID value in an inputbox in Excel
5) After which, Excel locates the cell with the TeamID value and the entire region of cells beginning one row ABOVE the row and columns containing that TeamID cell are copied (thanks Mumps). Please note that the TeamID row will always be in Column C but can be anywhere depending on what the user specifies This part all runs fine.

now what is supposed to happen next...

6)that copied info from Excel is supposed to be pasted into the Access table "Coaches" overwriting the information already there (the size of the region is the same as the size of the region of the copied data from Excel and should be pasted beginning one row down in the Access Coaches table.
7)macro should go back into the Excel Spreadsheet A and then copy all the rows and columns (A:BC) one row BELOW the row which contains the TeamID value and the paste that information back into the Access table "Coaches" below the region that was originally pasted in the first part above.
8)Everything should save and close.

A couple of notes just to help conceptualize....In Spreadsheet A you have a cell with the TeamID..part 1 is supposed to copy all the rows and columns with data above that row and paste into an already existing table in Access. Then go back and copy all the corresponding rows and columns in Excel below that row which has the TeamID value (so the row containing the TeamID is sort of the divider - first copying everything above, and then copying everything below), and then pasting that second set of information below the first half...so there are no gaps (almost like appending that data to the first set, although there is already data in that table that currently exists).

If someone could help it would be greatly appreciated..i put a couple of thoughts about how to proceed but made no headway (the font in orange at the end)

Thanks a million.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,130,337
Messages
5,641,571
Members
417,223
Latest member
jelena_

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
Top