DoCmd.TransferSpreadsheet acExport

[aziz rasul]

I have the following code

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryClaimantNonClaimantReport4", strExcelFile, True

I have a routine which now changes the name of the worksheet "qryClaimantNonClaimantReport4".

When I change the SQL of the query (using VBA) and retry the above code, the data is not transferred? However if I change the name of the query then it does.

Does anyone understand why the data can't be transferred on the second occasion without having to change the query name?

 

[mdlueck]

When I change the SQL of the query (using VBA) and retry the above code, the data is not transferred? However if I change the name of the query then it does.

Does anyone understand why the data can't be transferred on the second occasion without having to change the query name?

The DoCmd.TransferSpreadsheet call must try to perform the export upon an existing DAO.QueryDef object in the same database of the same name. DoCmd.TransferSpreadsheet merely executes the DAO.QueryDef object and exports the results to a spreadsheet file.

So you open the existing DAO.QueryDef, change the SQL, then close it. With the query updated, you do not receive a spreadsheet file? If that is the case, try letting the VBA code make the update to the DAO.QueryDef object and save its changes... THEN go interactively and execute the QueryDef object via the Access GUI... see if you are getting some sort of error message which DoCmd.TransferSpreadsheet is not displaying.

 

[aziz rasul]

I change the SQL of the query using VBA and using the GUI and it makes no difference.

Just wondering whether I'm doing something wrong when I change the SQL of the select query using the following code: -

Public Sub ChangeSQLofQuery(strQueryName As String, strSQL As String)

    Dim qdf As DAO.QueryDef
    
    Set qdf = CurrentDb.QueryDefs(strQueryName)
    qdf.SQL = strSQL
    
    Set qdf = Nothing

End Sub

 

[vbaInet]

I have a routine which now changes the name of the worksheet "qryClaimantNonClaimantReport4".

How is this the name of your worksheet?

When I change the SQL of the query (using VBA) and retry the above code, the data is not transferred?

How are you changing it? Show us some code.

 

[aziz rasul]

Here's how I change the name of the worksheet

Public Sub ChangeWorkSheetName(strExcelFile As String, strWorksheetOldName As String, strWorksheetNewName As String)

    Dim objExcelApp As Excel.Application
    Dim ws As Excel.Worksheet
    
    Set objExcelApp = New Excel.Application
    
    With objExcelApp
        .Workbooks.Open Filename:=strExcelFile
        .Visible = False
        For Each ws In .Worksheets
            If ws.Name = strWorksheetOldName Then
                ws.Select
                .Sheets(strWorksheetOldName).Name = strWorksheetNewName
                ws.Range("A1").Select
            End If
        Next
        .DisplayAlerts = False
        .ActiveWorkbook.SaveAs strExcelFile
        .DisplayAlerts = True
        .Quit
    End With
    
    Set objExcelApp = Nothing

End Sub

 

[mdlueck]

Just wondering whether I'm doing something wrong when I change the SQL of the select query using the following code: -

Public Sub ChangeSQLofQuery(strQueryName As String, strSQL As String)

    Dim qdf As DAO.QueryDef
    
    Set qdf = CurrentDb.QueryDefs(strQueryName)
    qdf.SQL = strSQL
    
    Set qdf = Nothing

End Sub

My code to build a brand new DAO.QueryDef object is as follows:

  Dim daoQDFfe As DAO.QueryDef

  'Build the FE Query
  Set daoQDFfe = daoDB.CreateQueryDef(strQryNameFE)
  With daoQDFfe
    .SQL = strSQLfe
    .Close
  End With

  'Clean up the connection to the database
  Set daoQDFbe = Nothing

I notice you never close it once you have built it. Oh, do you leave the DAO.QueryDef always in the database? I noticed you do not call CreateQueryDef either. I always cleanup / delete all of the querydef objects in the application I am developing.

 

[vbaInet]

You need to specify the worksheet you want to export data to. So for the Range part you need to put an exclamation mark at the end. E.g.:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryClaimantNonClaimantReport4", strExcelFile, True, "WorksheetName[COLOR=Red]![/COLOR]"

The exclamation mark is very important.

 

[vbaInet]

I notice you never close it once you have built it. Oh, do you leave the DAO.QueryDef always in the database? I noticed you do not call CreateQueryDef either. I always cleanup / delete all of the querydef objects in the application I am developing.

He's referring to an existing query and changing the SQL statement, not building a new one.

 

[mdlueck]

He's referring to an existing query and changing the SQL statement, not building a new one.

I was trying to come up with reasons that it would stop working if he changed it via code. Perhaps the change never got fully committed to the database due to the omitted explicit close.

 

[aziz rasul]

I tried your code mdlueck, but it made no difference

vbaInet, my Excel file only contains 1 sheet as I delete all the sheets except the first one before I begin using DoCmd.Spreadsheet. On the first wave of using DoCmd.Spreadsheet it adds the new sheet, but on the second and subsequent waves it doesn't. However I tried your suggestion, and guess what it worked.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryClaimantNonClaimantReport4", strExcelFile, True, "YClaimantYSchoolNSignedWEV"
Call ChangeWorkSheetName(strExcelFile, "YClaimantYSchoolNSignedWEV", "YClaimant YSchool NSigned WEV")

I couln't find a way of not using ChangeWorkSheetName to change the worksheet name as I wanted i.e. "YClaimant YSchool NSigned WEV".

 

[mdlueck]

my Excel file only contains 1 sheet as I delete all the sheets except the first one before I begin using DoCmd.Spreadsheet. On the first wave of using DoCmd.Spreadsheet it adds the new sheet, but on the second and subsequent waves it doesn't. However I tried your suggestion, and guess what it worked.

I have never heard of Access being able to make modifications (add an additional sheet) to an existing spreadsheet file. I thought all such API's in Access replaced the spreadsheet file if it found an existing one. Learn something new every day!

 

[vbaInet]

I couln't find a way of not using ChangeWorkSheetName to change the worksheet name as I wanted i.e. "YClaimant YSchool NSigned WEV".

If it contains spaces I think it needs to be enclosed in single quotes:

"'YClaimant YSchool NSigned WEV'!"

 

[boblarson]

This whole thing is why I use code like this:

to put the data on an existing sheet
http://www.btabdevelopment.com/ts/default.aspx?PageId=49

or to export to a new named sheet of my own choice
http://www.btabdevelopment.com/ts/export2ExcelNewNamedSheet

 

[aziz rasul]

It doesn't like the ! in "'YClaimant YSchool NSigned WEV'!". I get an error 3125

(''YClaimant YSchool NSigned WEV'$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.)

The file and filepath is

\\fas3040-sata\wccmydocs$\hrlr155\My Documents\Databases\Claimant - Non Claimant Stats Report.xlsx

If I take the ! out, it opens the file with a prompt saying there is an issue and file essentially gets corrupted. Hence my original code where I change the name of the sheet seems to be the option.