Assistance with BobLarson Code-Transfer object,format (1 Viewer)

[HeelNGville]

Still not working. Code runs passed the data to Excel, then stalls. Workbook remains open as filename Test.xls. Error code in VB is "No current record" 3021. Any ideas?

Public Function TferTbls()
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
 
 
' strFilePath is the name and path of the file you want to send this data into.
 
 
    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim nfile As String
    
 
    Dim strPath As String
 
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
 
    On Error GoTo err_handler
 
 
 
 
    strPath = strFilePath
 
    Set rst = CurrentDb.OpenRecordset("tblReqDetails")
   
    
    Set ApXL = CreateObject("Excel.Application")
 
    Set xlWBk = ApXL.Workbooks.Open("H:\My Documents\Testxls")
    ApXL.Visible = True
 
    Set xlWSh = xlWBk.Worksheets("Details")
 
    xlWSh.Range("A1").Select
 
    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).Select
    Next
 
    rst.MoveFirst
    xlWSh.Range("A2").CopyFromRecordset rst
    xlWSh.Range("1:1").Select
    ' This is included to show some of what you can do about formatting.  You can comment out or delete
    ' any of this that you don't want to use in your own export.
    With ApXL.Selection.Font
        .Name = "Verdana"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    End With
    ApXL.Selection.Font.Bold = True
    With ApXL.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select
    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
    ' selects the first cell to unselect all cells
    xlWSh.Range("A1").Select
    nfile = "H:\MyDocuments\" & rst(0) & "-" & rst(1) & ".xls"
 xlWBk.SaveAs nfile
xlWBk.Close
 
ApXL.Quit
 
Set ApXL = Nothing
 
    rst.Close
    Set rst = Nothing
 
    Exit Function
err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Exit Function
 
End Function

 

[boblarson]

Okay, so the recordset probably goes to EOF (end of file) when it sends to Excel. So we need to set it back to the first record so we have a value to use:

    xlWSh.Range("A1").Select
    [B][COLOR=blue]rst.MoveFirst[/COLOR][/B]
    nfile = "H:\MyDocuments\" & rst(0) & "-" & rst(1) & ".xls"
 xlWBk.SaveAs nfile
xlWBk.Close

 

[HeelNGville]

Got the file renamed accordingly. However the workbook will not close and the error code: 1004 "SaveAs Method of Workbook Class Failed". Thoughts?

 

[boblarson]

Not sure why that would do that especially if the file is saved as such. But you can try using SaveCopyAs instead of just SaveAs.

    xlWSh.Range("A1").Select
    [COLOR=black]rst.MoveFirst[/COLOR]
    nfile = "H:\MyDocuments\" & rst(0) & "-" & rst(1) & ".xls"
 xlWBk.Save[B][COLOR=red]Copy[/COLOR][/B]As nfile
xlWBk.Close

 

[HeelNGville]

Well, unfortunately that did not work either. Also, this revision did not peform a rename of the file. The data was transferred, however the file remained name Test.xls

Error: File could not be accessed.

 

[boblarson]

Have you made sure that there are no hidden Excel instances (use CTRL + ALT + DELETE and select Task Manager and if there are any Excel.exe process still there kill them and try again).

The other option is you upload a copy of your database here (with the Test.xls file and bogus data) so we can see what is happening.

 

[HeelNGville]

Have you made sure that there are no hidden Excel instances (use CTRL + ALT + DELETE and select Task Manager and if there are any Excel.exe process still there kill them and try again).

Yes, I checked to ensure that no instances were running & all was well. I am currently out of the office, however I will post the DB & test file tomorrow morning. I really appreciate all the help!

 

[HeelNGville]

The other option is you upload a copy of your database here (with the Test.xls file and bogus data) so we can see what is happening.

Ok Bob, attached is a very stripped down version of the DB and export file. With the exception of the test module and data table, all remaining objects were removed as they are irrelevant to this.

When running the code, the data is transferred from tblReqDetails to MS Excel file Test.xls (worksheet Details). This occurs correctly, then the file appears to be renamed in the Excel view, however the 'save as' and close step will not occur and the file remains open. Error code in module is "1004 "SaveAs Method of Workbook Class Failed".

Thanks for your help!

 

[DCrake]

Tried you mdb and it did not fall over, however I saw no reason for the following lines

ApXL.Visible = True
strPath = strFilePath - This was not used in the code.

for testing purposes I replaced "H:\MyDocuments\" with CurrentProject.Path

 

[HeelNGville]

Thanks for the feedback.

Removed the lines you suggested, however I continue to receive the following: "1004 "SaveAs Method of Workbook Class Failed". Any ideas?

 

[DCrake]

Copy the revised mdb into a different location and open up the immediate window (Ctrl+G) and run the function

?TferTbls


Remember to enable content first.

 

[HeelNGville]

Hi, DCrake. I was able to modify and get the code to run without errors. However, the export file is now including the folder name as part of the filename. So instead of:

ABCD-R1234567

I'm getting:

My DocumentsABCD-R1234567

How can I trim out the folder from the actual file name. Thanks!

 

[DCrake]

Show me your revised code

 

[HeelNGville]

Disregard. I made the changes prior to your post with the DB attached.

Your revision worked perfect!!! Many thanks to you and B. Larson for all the patience, explanations and assistance!

Thanks again!!

 

[DCrake]

Finally we have a conclusion Lets hope you have learnt from this.

 

[HeelNGville]

Ok DCrake, quick issue. Code runs perfect, file is saved accordingly, however for some unapparent reason, the second column in my table is not transferred to Excel. This field is used in the filename, but the data does not appear in the Excel file? Any thoughts?

--Editisregard, somehow the fields are being changed to binary. I have corrected.