Exporting to Excel what am I missing? (1 Viewer)

[crownedzero]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblBSLR", Location, True, "", ""

I append the table just prior to exporting, there are no errors in the code but there's not brand new spreadsheet on my desktop.

 

[SOS]

Well, for one - what is LOCATION and how are you building it? What is the rest of the code for that part?

 

[SOS]

Oh, and also, if you aren't passing parameters to the last items, do not put anything after the word TRUE.

 

[ajetrumpet]

make sure the arguments in the syntax is correct. also, is the excel spreadsheet type correct? there are many...

 

[crownedzero]

You again >.> Lol

Private Sub BSLR_Report_Click()
    Dim Identity As Variant
    Dim Location As String
    Dim Extension As String
    Dim Identity1 As String
    Dim stDocName As String
        
    On Error Resume Next
        
        Extension = ".csv"
        Identity = Format(Now(), "mmddyy")
        Identity1 = Identity & Extension
        Location = "C:\Documents and Settings\user\Desktop\test" & Identity1
        stDocName = "qryBSLR"
        
    'DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM tblBSLR"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.RunSQL "UPDATE tblBSLR SET [Closeout]='N' WHERE [Type] = 'F'"
    DoCmd.RunSQL "UPDATE tblBSLR SET [Closeout]='Y' WHERE [Type] = 'D'"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblBSLR", Location, True, "", ""
    'DoCmd.SetWarnings True
End Sub

Remember I'm new to this, I know its ugly.

 

[ajetrumpet]

you dimmed Identity as a variant. you are trying to concatenate that variable with a string. could that be the problem I wonder?

try this, and read the variables in the immediate window to see if they are correct:

        Extension = ".csv"
        Identity = Format(Now(), "mmddyy")
        Identity1 = Identity & Extension
        Location = "C:\Documents and Settings\user\Desktop\test" & Identity1
        stDocName = "qryBSLR"

debug.print Identity
debug.print Identity1
debug.print Location

 

[crownedzero]

It's possible, but I've used the same code for a | delimited text file I put together earlier and it worked.

 

[SOS]

Location = "C:\Documents and Settings\user\Desktop\test" & Identity1


Are you really using this exact code (including the user part? You would need to build that in to put in a valid user if you need it.

For example:

Location = "C:\Documents and Settings\" & Environ("username") & "\Desktop\test" & Identity1

 

[crownedzero]

User was edited out to maintain my anonymity

 

[ajetrumpet]

step through the code line by line with the F8 key, and after every line, when a variable or changes, or is assigned a value, hover your mouse over it in the line of the code to see what value it really has after being assigned. if there is no problem with any of them after going all the way through, then you obviously know that it's the export line.

 

[SOS]

A couple of other things.
1. You do not need to open the qryBSLR. That code is irrelevant unless it is an update, delete or append query. If it is a select query then you have no need to open it.

2. Try to use the appropriate types if possible. Don't use variants when you know you are going to have a string. Also don't use NOW because it includes time and you just need DATE() instead (with the Format(Date, "mmddyyyy") stuff like that.

 

[crownedzero]

It's got to be something to do with the export line. The data is removed, query is run, table is re-populated, even the F's have been replaced with N's, D's with Y's. Its running as it should up until the export line upon which nothing happens.

 

[crownedzero]

Silly question, the Extension is predefined as a .csv, I'm betting that's it?

 

[SOS]

Are you using this now:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tblBSLR", Location, True

without the other parameters at the end, like I suggested? If you aren't passing paremeters you don't add the , "", "" at the end - you just leave them off.

 

[crownedzero]

Yeah, it's working now that I changed the .csv to .xls. I didn't even look that far up I was so intent on the export code. I changed up a couple of things that you suggested SOS but if I remove the OpenQuery for some reason it doesn't append any data back into the table.

Thanks a bunch guys.

 

[ajetrumpet]

Yeah, it's working now that I changed the .csv to .xls.

(Laughing). Good luck to ya.