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

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


"cottage6" wrote:

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

Thanks Joel, the file is saving where it needs to go now.

"Joel" wrote:

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


"cottage6" wrote:

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