Hello,
I am having difficulty transferring data from Access to Excel using VBA. Originally, I wanted to use the DoCmd.TransferSpreadsheet acExport method. However, I was having difficulties exporting to a specific Excel file, sheet, and cell. This is what my coding looked like
mydb.DoCmd.TransferSpreadsheet acExport, 8, "NB Savings Final", "K:\nataccts\1Reports\1 - Strategic Partners\Customer\Cost Savings\2016\Customer_Cost Savings_Jan" & YTDMonth & "2016.xlsx", True, "NB Savings!B4:N2467"
I decided to give up on this since I was having problems and copying a table to my clipboard would have worked just as well. However, I am having issues with this, too. I have written a similar code that moves info between 2 excel files in a previous project. I have provided this coding.
Windows("CSA for Requisition Review Program_Master.xls").Activate
Sheets("DCS Form").Activate
Rows("5").Select
Selection.Copy
Windows("Req Review Program CSA YTD File v3.xlsb").Activate
Sheets("Sheet1").Activate
Rows("1").Select
ActiveSheet.Paste
Windows("CSA for Requisition Review Program_Master.xls").Activate
Sheets("DCS Form").Activate
This is what I am looking to do between Access and Excel. I am having difficulties switching to Access then copying the table data. I have gotten this far...
mydb.DoCmd.OpenTable "EB Savings Final", acViewNormal, acEdit
mydb.DoCmd.SelectObject acTable, "EB Savings Final"
mydb.RunCommand acCmdSelectAllRecords
mydb.RunCommand acCmdCopy
mydb.DoCmd.Close acTable, "Changeboard query", acSaveNo
Windows("Customer_Cost Savings_Jan" & YTDMonth & "2016.xlsx").Activate
Sheets("EB Savings").Activate
Range("B4").Paste
I am hoping that someone would be able to provide me with the correct coding for either the DoCmd.TransferSpreadsheet acExport or clipboard method. Thanks in advance.
-Rob
I am having difficulty transferring data from Access to Excel using VBA. Originally, I wanted to use the DoCmd.TransferSpreadsheet acExport method. However, I was having difficulties exporting to a specific Excel file, sheet, and cell. This is what my coding looked like
mydb.DoCmd.TransferSpreadsheet acExport, 8, "NB Savings Final", "K:\nataccts\1Reports\1 - Strategic Partners\Customer\Cost Savings\2016\Customer_Cost Savings_Jan" & YTDMonth & "2016.xlsx", True, "NB Savings!B4:N2467"
I decided to give up on this since I was having problems and copying a table to my clipboard would have worked just as well. However, I am having issues with this, too. I have written a similar code that moves info between 2 excel files in a previous project. I have provided this coding.
Windows("CSA for Requisition Review Program_Master.xls").Activate
Sheets("DCS Form").Activate
Rows("5").Select
Selection.Copy
Windows("Req Review Program CSA YTD File v3.xlsb").Activate
Sheets("Sheet1").Activate
Rows("1").Select
ActiveSheet.Paste
Windows("CSA for Requisition Review Program_Master.xls").Activate
Sheets("DCS Form").Activate
This is what I am looking to do between Access and Excel. I am having difficulties switching to Access then copying the table data. I have gotten this far...
mydb.DoCmd.OpenTable "EB Savings Final", acViewNormal, acEdit
mydb.DoCmd.SelectObject acTable, "EB Savings Final"
mydb.RunCommand acCmdSelectAllRecords
mydb.RunCommand acCmdCopy
mydb.DoCmd.Close acTable, "Changeboard query", acSaveNo
Windows("Customer_Cost Savings_Jan" & YTDMonth & "2016.xlsx").Activate
Sheets("EB Savings").Activate
Range("B4").Paste
I am hoping that someone would be able to provide me with the correct coding for either the DoCmd.TransferSpreadsheet acExport or clipboard method. Thanks in advance.
-Rob