Help saving file; exporting as .csv

C

cottage6

Hello & help!!!
I'm all goofed up here, tried many things myself, and have failed miserably
(Excel 2000). Anyway, I need to export a range named EXPORTRANGE as .csv
format to C:\Lotus\Work\123, with the file name bake.csv. The range is
getting saved to the current directory (instead of C:\Lotus\Work\123), with
the name bakeadj.csv. I did see something about DIR returning the first found
file name in the directory, but couldn't figure out what to do about it;
bakeadj.csv is the first file. In addition to that, the exported range does
not put any commas in the file, just blank cells. Thanks for any help you
can give me. Totally messed up (and probably laughable) code follows:
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As String
Dim destpathname As String
Dim ColCount As Integer
Dim CurrCol As Integer
Dim filemask As String

destpathname = "C:\Lotus\Work\123\"
filemask = "bake.csv"
FName = Dir(destpathname & filemask, vbNormal)
ListSep = Application.International(xlListSeparator)

Range("EXPORTRANGE").Select
Set SrcRg = Selection
ColCount = SrcRg.Columns.Count
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrCol = 0
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrCol = CurrCol + 1
CurrTextStr = CurrTextStr & CurrCell.Value & IIf(CurrCol < _
ColCount, ListSep, "")

Next
Print #1, CurrTextStr
Next
Close #1

End Sub
 
J

Joel

Try this code. I didn't test the code, but it has only small number of
changes from your posted code. Dir function returns only the filename
without the path. i eliminated the DIR so the file gets saved in correct
directory.


Sub test()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As String
Dim destpathname As String
Dim ColCount As Integer
Dim CurrCol As Integer
Dim filemask As String

destpathname = "C:\Lotus\Work\123\"
filemask = "bake.csv"
FName = destpathname & filemask
ListSep = Application.International(xlListSeparator)

Range("EXPORTRANGE").Select
Set SrcRg = Selection
ColCount = SrcRg.Columns.Count
Open FName For Output As #1

For Each CurrRow In SrcRg.Rows
For Each CurrCell In CurrRow.Cells
If Len(CurrTextStr) = 0 Then
CurrTextStr = CurrCell.Value
Else
CurrTextStr = CurrTextStr & "," & _
CurrCell.Value
End If
Next CurrCell
Print #1, CurrTextStr
Next CurrRow
Close #1

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top