Call the defined worksheet using Access

[kate middleton]

Hello Everybody

I want to do something like this

Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim wks As Excel.Worksheet
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = True
        Set xlWB = Workbooks.Add
        Set wks = xlWB.Worksheets("Sheet1")
    End With
    
                 
DoCmd.OutputTo acOutputQuery, "Query_123", acFormatXLS _
                 , "wks", False, "", 0

But it does not works. But when i give something like this

DoCmd.OutputTo acOutputQuery, "Query_123", acFormatXLS _
                 , "C:\ABC\test1.xls", False, "", 0

then it works. But i dont want to give the fix path, i alway want to add the new workbook.

 

[xenou]

As far as I know it is only possible to export to a file that has a path -- i.e., not possible to export to a new/unsaved workbook. Probably not to an open workbook either. I guess an option would be to use a file in a temp directory. Not sure what you really want here though -- why is it a problem have a file name?

 

[kate middleton]

I just want to open a new workbook and then transport the userform in that workbook. Isn't it possible that i open the new workbook which have offcourse a worksheet with standard name (Sheet1) and then transfer the data there???

 

[xenou]

No, not really. Access transfers/exports data to named files only (i.e., files with file paths). I don't know what you mean by "transport the userform in that workbook" so it's still not clear what you are trying to do. How did a userform get involved in this now?