Export to Excel (1 Viewer)

vba_php

Forum Troll
Local time
Today, 16:12
Joined
Oct 6, 2019
Messages
2,884
I have just taken your code and exported 3 queries to a non existing excel workbook and when I
GM,

it's unknown what sheet she wants, therefore this is confusing:
Code:
worksheets(1).select
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:12
Joined
Sep 21, 2011
Messages
14,047
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)

Code:
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
If I open that file I am on cell C5 on first sheet?. That is the only one selected.?
Took a while to open for me as well?
 

vba_php

Forum Troll
Local time
Today, 16:12
Joined
Oct 6, 2019
Messages
2,884
let's not post anymore GM. she's getting confused more than likely. if you want me to consolidate the code so it runs faster, PM me
 

Snowflake68

Registered User.
Local time
Today, 21:12
Joined
May 28, 2014
Messages
452
Do you think there is an issue with the original code selecting the 3 tabs. I have no idea why it does this but I am using MS Office Pro 2019.

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

The first line above creates the Excel file and then the next two lines add additional sheeets to the same file. There is nothing clever going on here.

The user then needs to be able to open this file and immediately use the data ribbon without having to select one of the tabs. I think I need to find a way of deselecting the other two sheets as this is what seems to be the issue.
 

vba_php

Forum Troll
Local time
Today, 16:12
Joined
Oct 6, 2019
Messages
2,884
flake,

I'll let gasman take over from here. I hope you get it done. if I can help you further, just ask here or in a PM.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:12
Joined
Sep 21, 2011
Messages
14,047
I think you need someone else with 2019 to see if that happens to them? It certainly does not happen to me, whether file exists or not, but then I am on 2007.
Again, even when selecting a cell in the worksheet, the tabs should not be selected.?

Are they automatically grouped at all?. When googling on how to deselect there was mentioning of grouping worksheets. Another google seems to indicate selecting a sheet should do the trick, whether it is Worksheets(1). Select or ActiveSheet.Select

Try activating a worksheet first as well?
 

Snowflake68

Registered User.
Local time
Today, 21:12
Joined
May 28, 2014
Messages
452
flake,

I'll let gasman take over from here. I hope you get it done. if I can help you further, just ask here or in a PM.
Thanks, yes I still need help.

Just to confirm that the file I attached earlier, if I open it all 3 tabs are selected even though cell C5 is the active cell. But if I click on the Data Ribbon then none of the buttons are active. The only way I can activate them is if I click on one of the sheet names.
 

Snowflake68

Registered User.
Local time
Today, 21:12
Joined
May 28, 2014
Messages
452
I think you need someone else with 2019 to see if that happens to them? It certainly does not happen to me, whether file exists or not, but then I am on 2007.
Again, even when selecting a cell in the worksheet, the tabs should not be selected.?

Are they automatically grouped at all?. When googling on how to deselect there was mentioning of grouping worksheets. Another google seems to indicate selecting a sheet should do the trick, whether it is Worksheets(1). Select or ActiveSheet.Select

Try activating a worksheet first as well?

The code below opens the file that I create (containing the 3 sheets all selected) and it does actually work and only the first sheet is selected (the other two arent) which is perfect BUT when I close the excel file it doesnt save it in that state.

So I need to find a way of saving the changes automatically before it is closed. What code do I need to add in order to save the changes before it is closed?

Code:
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_LimitedAvailability")

ws.Activate
Set ws = wb.Worksheets("Portfolio_MainLive")
ws.Activate

when I have this all working I dont actually want to see the Excel file open so I will change the xl.visible = True to be False once I have it all saving the changes and closing the file. then I will add this code below but I need to add a line to save the changes.

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

vba_php

Forum Troll
Local time
Today, 16:12
Joined
Oct 6, 2019
Messages
2,884
What code do I need to add in order to save the changes before it is closed?
you need this:
SQL:
application.displayalerts=false
activeworkbook.Save 'or rather, if ur using my code => WB.SAVE
application.displayalerts=true
 

Snowflake68

Registered User.
Local time
Today, 21:12
Joined
May 28, 2014
Messages
452
you need this:
SQL:
application.displayalerts=false
activeworkbook.Save 'or rather, if ur using my code => WB.SAVE
application.displayalerts=true
Thanks but I get an error saying Compile error: Method or data member not found which points to this line.

Code:
Application.DisplayAlerts = False
 

vba_php

Forum Troll
Local time
Today, 16:12
Joined
Oct 6, 2019
Messages
2,884
Thanks but I get an error saying Compile error: Method or data member not found which points to this line.
Code:
Application.DisplayAlerts = False
LOL. this whole thread has confused everyone. no doubt. try this (I forgot you were running your code from access):
Code:
docmd.setwarnings = false
WB.SAVE
docmd.setwarnings = true
would you like someone here to give you a little assistance on how to search appropriate terms on google so you can look this stuff up yourself? it's actually quite simple. :)
 

Snowflake68

Registered User.
Local time
Today, 21:12
Joined
May 28, 2014
Messages
452
LOL. this whole thread has confused everyone. no doubt. try this (I forgot you were running your code from access):
Code:
docmd.setwarnings = false
WB.SAVE
docmd.setwarnings = true
would you like someone here to give you a little assistance on how to search appropriate terms on google so you can look this stuff up yourself? it's actually quite simple. :)
Thanks for all your help, I do appreciate it. I did already try searching on Google for help before coming on here which is what I always try to do but I am not too clever but do actually try before pestering the experts.
You will see that I already use the docmd.setwarnings = False statement if you read by original post but I thought it would be a different statement for the Excel prompts which I have had trouble searching for. Apologies if I have been a pain keeping on but I got there in the end.

Thanks again to you and Gasman. ;)
 

Snowflake68

Registered User.
Local time
Today, 21:12
Joined
May 28, 2014
Messages
452
so you got what you needed, then?
Yes thank you.

Here is my complete code if it helps anyone else.
Code:
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


Dim xl As Object
Set xl = CreateObject("excel.application")
Dim wb As Object
Set wb = xl.Workbooks.Open(strOutputFileName)
Dim ws As Object
Set ws = wb.Worksheets("Portfolio_LimitedAvailability")

ws.Activate
Set ws = wb.Worksheets("Portfolio_MainLive")
ws.Activate

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

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

Requery

DoCmd.SetWarnings True

Exit Sub

Errorhandler:

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

End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:12
Joined
Sep 21, 2011
Messages
14,047
So an Activate works? You should only need to activate one sheet?

BTW, your strOutputFileName is not dimmed in your first code posted?
 

Snowflake68

Registered User.
Local time
Today, 21:12
Joined
May 28, 2014
Messages
452
So an Activate works? You should only need to activate one sheet?

BTW, your strOutputFileName is not dimmed in your first code posted?
just doing the 1 activate didnt work hence having to do one then another.

Thanks about the Dim it was a typo
 

Users who are viewing this thread

Top Bottom