Export text file with VBA

[CB_DFW]

I have some code I would like to try and use to export data from my spreadsheet to a text file so I can import it into Access.

Can someone help me put it in my spreadsheet and make it work?

The vb and the spreadsheet are attached. Any help would be greatly appreciated.

Thanks in advance.

 

[chergh]

Why not just import the spreadsheet directly into Access?

 

[CB_DFW]

I have about 70 users that will use this spreadsheet. I thought I would create a button for them to export the data as a text file. They would send me the text file and then I could combine the files and import all at once.

 

[chergh]

Still don't see the point in using a text file but if you must just tell them to change the file format in the save as dialog to text.

 

[CB_DFW]

I understand that I can have them save the file as txt. I would like to create a button that will save the file for them. Trust me, some of these folks will mess up even trying to save the file as text. I thought that I would have it export the file for them and then maybe have the file email directly to me.

 

[CB_DFW]

With the text files, I can combine them using this dos command.

copy c:\txt\*.txt c:\txt\combined.txt

That way I don't have to import each file individually.

 

[chergh]

Easiest way is just to record a macro of you saving a spreadsheet as a text file and then you have your code.

 

[CB_DFW]

I can do that but then they are in the file with it named CSV. They won't know how to get back to their original file. I'm not trying to be difficult. This has to be seemless (the best I can).

I have one button that combines all the tabs, deletes the blank rows and now I want it to spit out a Text file (preferably csv). Won't the code I attached do that?

I just need some help with it.

Thanks so much.

 

[chergh]

Reading out each cell into a text file doesn't seem a good way to do it. Try something like this.

dim path as string

application.displayalerts = false

path = thisworkbook.path & "\" thisworkbook.name

thisworkbook.save

'put your code to combine tabs and delete blank rows in here

thisworkbook.saveas filename:="C:\folder\BookName.csv", fileformat:=xlCSVMSDOS, createbackup:= false

workbooks.open path

application.displayalerts = true

workbooks("BookName.csv").close false

end sub

 

[CB_DFW]

I'm getting a syntex error on this line.

path = thisworkbook.path & "\" thisworkbook.name

 

[chergh]

It should be

path = thisworkbook.path & "\" & thisworkbook.name

 

[CB_DFW]

That works!! I have have an extra row at the top. Can we have it save the worksheet without the top row? Row 2 has the column headings.

 

[CB_DFW]

Is it possible to have this file automatically email via Outlook 2007?

 

[chergh]

You should be able to use the code you have that deletes blank rows delete the first row, or record a macro to see how it is done and just add the code in.

Yep you can have it automatically emailed via 2007 but I don't use it so your better off asking someone else for advice with that.

 

[CB_DFW]

This is working good. I would like for the file to save to the desktop of the current user. I have used this "C:\documents and settings\%username%\Desktop" before but it does not work.

Public Sub save()
    
Dim path As String
Application.DisplayAlerts = False
path = ThisWorkbook.path & "\" & ThisWorkbook.Name
ThisWorkbook.save
'put your code to combine tabs and delete blank rows in here
ThisWorkbook.SaveAs Filename:="C:\documents and settings\%username%\Desktop\BookName.csv", FileFormat:=xlCSVMSDOS, CreateBackup:=False
Workbooks.Open path
Application.DisplayAlerts = True
Workbooks("BookName.csv").Close False
End Sub

 

[chergh]

You can try this

 

[MSAccessRookie]

You can try this

Gotta love a great sense of humor

 

[CB_DFW]

I don't mind at all. I have done a lot of searching and I try what I find but most of the time I can't get it to work on my own. Not that I have completely failed.

It's normally something small that I miss because I am a newby.

That's why I started this thread with a sample of the code that I was trying to use.

This email thing looks to be a bit over my head. But I will do my best.

 

[CB_DFW]

I tried using this. It seemed to be as basic as it gets. But I get an error message that says.

The "sendusing" configuration value is invalid.

Public Sub email()
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = [EMAIL="cbdfw@yahoo.com"]cbdfw@yahoo.com[/EMAIL]
objMessage.To = "[EMAIL="cbdfw@yahoo.com"][COLOR=#0000ff]cbdfw@yahoo.com[/COLOR][/EMAIL]"
objMessage.TextBody = "This is some sample message text."
objMessage.Send
End Sub

 

[MSAccessRookie]

I tried using this. It seemed to be as basic as it gets. But I get an error message that says.

The "sendusing" configuration value is invalid.

Public Sub email()
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Example CDO Message"
objMessage.From = [EMAIL="cbdfw@yahoo.com"]cbdfw@yahoo.com[/EMAIL]
objMessage.To = "[EMAIL="cbdfw@yahoo.com"][COLOR=#0000ff]cbdfw@yahoo.com[/COLOR][/EMAIL]"
objMessage.TextBody = "This is some sample message text."
objMessage.Send
End Sub

Have you tried the DoCmd.SendObject method? Something like this works in my application:

Private Sub email_DblClick(Cancel As Integer)
On Error GoTo Err_Email_DblClick
    Dim stDocName As String
    Dim stDocSubj As String
    If Me.email <> "" Then
        stDocName = "Email"
        stDocSubj = "Email Being Sent to " & Me.ClientName
        DoCmd.SendObject acSendNoObject, stDocName, acFormatXLS, Me.email, , , stDocSubj, , True
    End If
 
Exit_Email_DblClick:
    Exit Sub
Err_Email_DblClick:
    MsgBox Err.Description
    Resume Exit_Email_DblClick
 
End Sub