Export to Excel

[Snowflake68]

The code below exports data to an Excel workbook with 3 tabs. The code works without any issues however when you open up the Excel file for the very first time it has all 3 tabs are selected and you cannot use any of the buttons on the Data tab ribbon until you select one on the tabs. Not a real issue but the manager of my end users have asked me if there is anything I can do to stop this as this sometimes confuses the less familiar users.

Private Sub cmdPortFolioOutput_Click()

On Error GoTo Errorhandler
 
DoCmd.SetWarnings False

Dim outputFileName As String
strOutputFileName = [Application].[CurrentProject].[Path] & "\DE_Data\Portfolio\OUT\PortFolioTemplate_" & DLookup("[CustomerFileName]", "1_CustomerProspectSelection") & "_" & DLookup("[LISTREFERENCE]", "LookupListRef") & ".xlsx"

    DoCmd.OpenQuery "qry_PortFolioOutputCreated"
    cboPortFolioOutput.Locked = True
    PortFolioOutputCreated.Visible = True
    cmdPortFolioImport.Visible = True
    lblDataIn.Visible = True ' Show export DIR link
  
DoCmd.OpenQuery "qry_Portfolio_MainLive"
DoCmd.OpenQuery "qry_Portfolio_Clearance"
DoCmd.OpenQuery "qry_Portfolio_LimitedAvailability"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Portfolio_MainLive", strOutputFileName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Portfolio_LimitedAvailability", strOutputFileName, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Portfolio_Clearance", strOutputFileName, True

MsgBox "Portfolio Template Created", vbInformation, "PortFolio Template"

Requery

DoCmd.SetWarnings True

Exit Sub

Errorhandler:

MsgBox "Error " & Err.Number & Err.Description
Exit Sub

End Sub

 

[Ranman256]

I don't see your Tab Names:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Portfolio_MainLive", strOutputFileName, True, "mainLive"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Portfolio_LimitedAvailability", strOutputFileName, True, "Limited"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Portfolio_Clearance", strOutputFileName, True ,"Clearance"

 

[Snowflake68]

I don't see your Tab Names:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Portfolio_MainLive", strOutputFileName, True, "mainLive"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Portfolio_LimitedAvailability", strOutputFileName, True, "Limited"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Portfolio_Clearance", strOutputFileName, True ,"Clearance"

It just automatically names the tabs with the name of the query that it is exporting. So there are 3 tabs named as follows.
Portfolio_MainLive
Portfolio_LimitedAvailability
Portfolio_Clearance

 

[Cronk]

You could try using Excel automation to open the spreadsheet and set one of the worksheets as the active one, then save the spreadsheet file.

 

[Snowflake68]

You could try using Excel automation to open the spreadsheet and set one of the worksheets as the active one, then save the spreadsheet file.

Thanks any idea what VBA code I would need to open the excel file that I have created and set an active sheet?

 

[vba_php]

Thanks any idea what VBA code I would need to open the excel file that I have created and set an active sheet?

there are many ways, but this is one. And i believe Colin says this is either "late" or "early" binding. can't remember which one.

Dim xl As Object
Set xl = CreateObject("excel.application")
xl.Visible = True
Dim wb As Object
Set wb = xl.workbooks.Open("path of book to open")
Dim ws As Object
Set ws = wb.worksheets("worksheet to set focus on or activate")

ws.Activate

 

[June7]

Don't think even need to open queries first for export.

 

[Snowflake68]

Don't think even need to open queries first for export.

They are make table queries.

 

[Snowflake68]

there are many ways, but this is one. And i believe Colin says this is either "late" or "early" binding. can't remember which one.

Dim xl As Object
Set xl = CreateObject("excel.application")
xl.Visible = True
Dim wb As Object
Set wb = xl.workbooks.Open("path of book to open")
Dim ws As Object
Set ws = wb.worksheets("worksheet to set focus on or activate")

ws.Activate

Thank you I will try this on Monday

 

[Snowflake68]

there are many ways, but this is one. And i believe Colin says this is either "late" or "early" binding. can't remember which one.

Dim xl As Object
Set xl = CreateObject("excel.application")
xl.Visible = True
Dim wb As Object
Set wb = xl.workbooks.Open("path of book to open")
Dim ws As Object
Set ws = wb.worksheets("worksheet to set focus on or activate")

ws.Activate

I have tried this code but the excel file opens but all 3 tabs are selected together. It isnt selecting just the first tab.
I know I can change the code so that I dont see the Excel file open by changing the xl.visible to False but then I need to select the first cell in the first sheet and then save and close the file.
Are you able to help me with the code I need to do that please.

Thanks
Caroline

 

[vba_php]

I have tried this code but the excel file opens but all 3 tabs are selected together. It isnt selecting just the first tab.

that shouldn't be happening, unless you saved the file the last time you closed it with all 3 sheets selected. i believe excel *remembers* that stuff. the line of my code called ws.activate was produced by the macro recorder in excel, so more than likely it is correct code. if you're still having problems with it, try this code instead as a substitute:

wb.worksheets("YOUR WORKSHEET").select

and if that doesn't work, try this as a 3rd alternative:

wb.worksheets("YOUR WORKSHEET").activate

I know I can change the code so that I dont see the Excel file open by changing the xl.visible

you don't need the .visible=[boolean] line of code in there if you want it to remain in the background and not seen. it opens invisible by default. give this a try:

Function for_Caroline()
Dim xl As Object
Set xl = CreateObject("excel.application")
Dim wb As Object
Set wb = xl.workbooks.Open("PATH OF YOUR FILE HERE")
Dim ws As Object
Set ws = wb.worksheets("NAME OF YOUR SHEET")

    ws.Activate
    ws.Range("A1").Select 'select the first cell of the worksheet

    xl.activeworkbook.Close 'close the workbook
    xl.Quit 'quit the excel instance
End Function

 

[Snowflake68]

Dim xl As Object Set xl = CreateObject("excel.application") Dim wb As Object Set wb = xl.workbooks.Open("PATH OF YOUR FILE HERE") Dim ws As Object Set ws = wb.worksheets("NAME OF YOUR SHEET") ws.Activate ws.Range("A1").Select 'select the first cell of the worksheet xl.activeworkbook.Close 'close the workbook xl.Quit 'quit the excel instance

Thanks for this. I still have an issue with the other tabs that are selected. I believe you code is actually working and activating cell A1 in the first tab but because the other two tabs are still selected I still have the issue of not being able to use any of the buttons on the Data ribbon until I click on one of the tabs.

Is there any code that will 'deactive' the other 2 tabs?

 

[Snowflake68]

Here is an example of the Excel file containing the 3 tabs which are all selected after using this code below. (the data in the tabs is just made up and would usually have lots of columns and hundreds of lines of data but I just need to protect the data)

Dim xl As Object
Set xl = CreateObject("excel.application")
xl.Visible = True
Dim wb As Object
Set wb = xl.Workbooks.Open(strOutputFileName)
Dim ws As Object
Set ws = wb.Worksheets("Portfolio_MainLive")
ws.Activate
    
    ws.Range("A1").Select 'select the first cell of the worksheet

    xl.ActiveWorkbook.Close 'close the workbook
    xl.Quit 'quit the excel instance

Hope you can help please
Caroline

 

[vba_php]

I ran a test on what I said earlier. I opened a workbook, selected 3 sheets at the same time, saved the file, closed it, then re-opened it. and sure enough, those 3 sheets were still selected. so you see, excel *does* remember you do after saving. I don't know what the issue is that you're having with these 3 sheets being selected, but if the following code doesn't work, upload the file and we'll take a look at it. here, try this:

Function for_Caroline()

Dim xl As Object
Set xl = CreateObject("excel.application")
Dim wb As Object
Set wb = xl.workbooks.Open("YOUR FILE")
Dim ws As Object

xl.Visible = True 'OPTIONAL CODE HERE

    For Each ws In wb.worksheets
        ws.Activate
    Next ws

Set ws = xl.worksheets("SHEET THAT YOU WANT TO SELECT RANGE ON")
ws.Activate

ws.Range("A1").Select

xl.ActiveWorkbook.Close
xl.Quit

End Function

 

[Gasman]

Does

worksheets(1).select

not work?

 

[vba_php]

Does

worksheets(1).select

not work?

I think she's unaware of a mishap that happened, Gasman. There might be something wrong that she's unaware of.

 

[Snowflake68]

I think she's unaware of a mishap that happened, Gasman. There might be something wrong that she's unaware of.

Does

worksheets(1).select

not work?

Nope, all 3 tabs are still selected.

 

[Gasman]

Ok, how about just selecting cell A1 on each worksheet?

 

[vba_php]

use this workbook, and then put this code in it:

Dim xl As Object
Set xl = CreateObject("excel.application")
Dim wb As Object
Set wb = xl.workbooks.Open("YOUR PATH\PortFolioTemplate_A3D2_11252002034.xlsx")
Dim ws As Object

xl.Visible = True 'OPTIONAL CODE HERE

Set ws = xl.worksheets("Portfolio_LimitedAvailability") 'or whatever sheet you want here
ws.Activate

ws.Range("A1").Select

xl.ActiveWorkbook.Close
xl.Quit

 

[Gasman]

Hmm,
I have just taken your code and exported 3 queries to a non existing excel workbook and when I open it I am on cell A1 of first sheet.?
I closed the file and ran again, same effect.?
I am using Excel 2007 and exported to xlsx file.?