Hi, i got it working . Thanks. Just need a little bit of help with csv again :)

Just this line im using. Is there a simpler way to do it. so that it works

PropAttachment = GetSystemString("Doc Path") & PropType & "-" & PropID & ".csv"

:( Ok im still working away on this. I aint got it working yet. Anyone have any tips?

What are you trying to do with this line?

PropAttachment = GetSystemString("Doc Path") & PropType & "-" & PropID & ".csv"

This looks like you are trying to generate a file name. What is "GetSystemString"? Is that a variable or a method?

And why "PropAttachment"? Anyway, if you are trying to build the path and file name, I would suggest not having a space in "Doc Path". If you are snatching these variables from the form, do that before this line, not in the line. So you would do this:

Dim PropAttachment As String
Dim sDocPath As String
Dim sPropType As String
Dim sPropID As String

sDocPath = Me.txtDocPath
sPropType = Me.txtPropType
sPropID = Me.PropID

PropAttachment = sDocPath & "\" & sPropType & "-" & iPropID & ".csv"

I added the "\" cause that usually goes missing.

Don't grab variable values in the line as you have done, get them before, and then concatenate them in line. Also note that even though PropID is prabably an integer, use a string since it will end up as a string and not be used as a number in an equation. When you try to concatenate an integer, sometimes it can cause problems and try to do arithmetic when you just want a regualr expression.

Does this help?


mmcdonal

Sorry, that's

PropAttachment = sDocPath & "\" & sPropType & "-" & sPropID & ".csv"

not

PropAttachment = sDocPath & "\" & sPropType & "-" & iPropID & ".csv"


mmcdonal

Basically im trying to fix someones work, but im in the same boat as them. It basically takes the results of a query, puts them into a csv file and attaches the csv file to an email. Ive changed the csv creation back to the original one.

This is the query i have.

SELECT [Clients].[Company Name], [Clients].[Address], [Clients].[Post Code], [Clients].[Sort Code], [Clients].[Acc number], [Clients].[Acc Name], [Proposals].[Date Supplier paid], [Proposals].[Doc Fee], [Proposals].[Commission (£)], [Proposals].[The Goods], [Proposals].[VAT on Commission] AS Expr1, [Proposals].[Fixed /Variable] AS Expr2, [Proposals].[Opt Fee] AS Expr3, [Proposals].Manufacturer AS Expr4, [Proposals].[Chasis Number] AS Expr5, [Proposals].[Final Installment - Balloon] AS Expr6, [Proposals].[Advance Payment] AS Expr7, [Proposals].[Date to First Payment] AS Expr8, [Proposals].[Date of Balloon] AS Expr9, [Proposals].[Serial Number], [Proposals].[Registration Number], [Proposals].[Year Of Manufacture], [Proposals].[New], [Proposals].[Transaction Price(£)], [Proposals].[Transaction Price VAT], [Proposals].[Cash Deposit (£)], [Proposals].[Balance To Finance (£)], [Proposals].[Rental Profile Period], [Proposals].[Rental Profile Details], [Proposal Rental Profiles].[Amount], [Proposals].[Fortis CSV] AS Expr10, [Proposals].[Accepted Finance House]
FROM Products, (Clients INNER JOIN Proposals ON [Clients].[ID]=[Proposals].[ID]) INNER JOIN [Proposal Rental Profiles] ON [Clients].[ID]=[Proposal Rental Profiles].[ID]
WHERE ((([Proposals].[Query CSV])=True) And (([Proposals].[Accepted Finance House])=" Lease"));


 This is the csv creator
DoCmd.TransferText acExportDelim, , "Fortis", "C:\Fortis.csv"


This is the mail to part
Function MailNow(Address, FinanceHouse)
Dim PropID As Long
Dim PropAttachment As String
Dim Found As Boolean
Dim Mesg As String
Dim Subj As String
Dim rcd As New ADODB.Recordset
Dim objWord As Word.Application
On Error GoTo HandleErr
If ProposalFromWizard = True And RemailToFinanceHouse = False Then
    rcd.Open "TempProposals", CurrentProject.Connection, adOpenKeyset, adLockPessimistic, adCmdTableDirect
    rcd.MoveFirst
    PropID = rcd.Fields![ID]
    rcd.Close
    Set objWord = CreateObject("Word.Application")
    Processcsv objWord, PropID, True 'True means Close the Document when finished
    AddOfferToProposal PropID, FinanceHouse
    Mesg = "Proposal for " & GetClientName(Val(SelectedClient)) & " (" & GetClientContact(Val(SelectedClient)) & ")"
Else
    PropID = Forms![Proposals].[ID]
    PropType = Forms![Proposals].[Proposal Type]
    PropType = UCase(Left(PropType, InStr(1, PropType, " ") - 1))
    Set objWord = CreateObject("Word.Application")
    Processcsv objWord, PropID, True 'True means Close the Document when finished
    AddOfferToProposal PropID, FinanceHouse
    Forms![Proposals].pcfinancehouse.Requery
    Mesg = "Proposal for " & GetClientName(Forms![Proposals].[Client ID]) & " (" & GetClientContact(Forms![Proposals].[Client ID]) & ")"
End If
RemailToFinanceHouse = False
Subj = Mesg
Mesg = Mesg & vbCr & vbCr & vbCr
Mesg = Mesg & GetSystemString("Company Name") & vbCr
Mesg = Mesg & "Tel: " & GetSystemString("Phone") & " Fax: " & GetSystemString("Fax") & vbCr
Mesg = Mesg & "E-mail: " & GetSystemString("EMail")
If NewPropType = "PRIVATE" Then

    DoCmd.TransferText acExportDelim, , "Fortis CSV", "C:\Fotris.csv"

Else
    DoCmd.TransferText acExportDelim, , "Fortis CSV", "C:\Fotris.csv"
End If
MakeEmail Address, Subj, Mesg, PropAttachment
ExitHere:
    Exit Function

When i run this, the CSV file is created, an export log is created but nothing is written into the CVS file. Im so close i think.

Is the query saved? You can't export a query unless it is saved with a name to it.


mmcdonal

Yeah, the query is saved in the query section of the database.

Allan

Hello,

I have a similar question about export to csv file. When I use similar way as you guys discussed to export csv file. The accuracy of numbers are lost. In the table, columns have numbers like 1.2345, but in the csv file, the number change to 1.23 always two decimal places. How can I get exactly copy of data in csv file as in the table?

Thanks very much