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.
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.
