0

I have a query in Access DB which I need to export to an excel workbook. In the same excel workbook I want to add 2 more excel sheet - one with no data and the other with a pre-coded (using access vba) vlookup/dlookup between sheet1 and sheet2. Below is the code i am using to create export the data but i am not able to add new sheets to it. When i click on the button which is embedded to the below script the access DB hangs. Appreciate any assistance/guidance. How to add additional sheets and how to do a lookup between them. Thanks.

Private Sub Command1679_Click()
DoCmd.TransferSpreadsheet acExport, , "SQL QueryName", "C:/Report" & "_" & Format(Date, "yyyy-mm-dd") & ".xlsx", True, "Sheet1"

Dim objexcel As Excel.Application
Dim wbexcel As Excel.Workbook

Set objexcel = CreateObject("Excel.Application")
Set wbexcel = objexcel.Workbooks.Open("C:/Report" & "_" & Format(Date, "yyyy-mm-dd"))

Set objexcel = wbexcel.Worksheets.Add()

End Sub
Naveen Subramanian
  • 47
  • 2
  • 8
  • You're in MS-Access, are you not (I edited the tags)? `Workbooks` doesn't exist in Access, hence the error you're getting. You need to reference the Excel object model, create an instance of `Excel.Application` (call it say `xlApp`), and then use `xlApp.Workbooks.Open` to get the workbook object (call it say `xlBook`), and then do `xlBook.Worksheets.Add` to add a worksheet. The `Worksheets` collection doesn't exist in Access either. – Mathieu Guindon Mar 08 '17 at 15:37
  • Yes. Thanks for that – Naveen Subramanian Mar 08 '17 at 15:40
  • I tried the below code and it didn't work. I want to add a new sheet to the excel work book i created using -_DoCmd.TransferSpredsheet_ `Private Sub Command1685_Click() xlApp = Excel.Application DoCmd.TransferSpreadsheet acExport, , "Query", "Report" & "_" & Format(Date, "yyyy-mm-dd") & ".xlsx", True, "BRReport" xlBook = xlApp.Workbooks.Open xlBook.Worksheet.Add End Sub` – Naveen Subramanian Mar 08 '17 at 15:46
  • If you have very little to no knowledge, then you need to do a bit of research. This question is answered several hundreds of times over already. go to Tools > References... > Microsoft Excel {version} to add a reference to the Excel object model if you want to use types defined in the Excel type library. – Mathieu Guindon Mar 08 '17 at 15:48
  • I understand .. I did searc for the answers but most of them did not involve exporting a query into an excel workbook and then adding a new sheet to it. That is where i was stuck. i just need some help executing it. Thanks for your help – Naveen Subramanian Mar 08 '17 at 15:56
  • Makes no difference - you've exported the workbook already, so your use case is identical to anyone trying to open a workbook in Excel, given a filename, from an Access VBA project. – Mathieu Guindon Mar 08 '17 at 15:58
  • I still am facing issues implementing this. Please let me know what I am doing wrong. `Private Sub Command1679_Click() DoCmd.TransferSpreadsheet acExport, , "SQL QueryName", "C:/Report" & "_" & Format(Date, "yyyy-mm-dd") & ".xlsx", True, "Sheet1" Dim objexcel As Excel.Application Dim wbexcel As Excel.Workbook Set objexcel = CreateObject("Excel.Application") Set wbexcel = objexcel.Workbooks.Open("C:/Report" & "_" & Format(Date, "yyyy-mm-dd")) Set objexcel = wbexcel.Worksheets.Add() End Sub` – Naveen Subramanian Mar 09 '17 at 16:35
  • If the linked question doesn't fix your problem, [edit] your question and tell us how it's different - we'll reopen it accordingly. – Mathieu Guindon Mar 09 '17 at 16:36
  • edited the question as suggested. – Naveen Subramanian Mar 09 '17 at 17:54

0 Answers0