Use a Piece of Data for File Name
 

Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet 1
into a new workbook. I want to name the new workbook using the two pieces of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1 have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Use a Piece of Data for File Name
 

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet
1
into a new workbook. I want to name the new workbook using the two pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1
have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Use a Piece of Data for File Name
 

Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time Error 6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet
1
into a new workbook. I want to name the new workbook using the two pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1
have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Use a Piece of Data for File Name
 

Which operating system and version of Excel and are you using?

Dan Dungan

On Sep 26, 11:42 am, AccessHelp
wrote:
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet 1
into a new workbook. I want to name the new workbook using the two pieces of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1 have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Use a Piece of Data for File Name
 

Is there a worksheet open when that code runs?

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time Error 6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the
worksheet
1
into a new workbook. I want to name the new workbook using the two
pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1 and
C1
have
ABC and 123 in Workbook A, respective. Then the new workbook name will
be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the
new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can
not
be accessed. It didn't save the file, and the new workbook is left
open.

Please help. Thanks.

Use a Piece of Data for File Name
 

HI.
Try:

Public Sub prova()
Dim newname As String
With Sheets(1)
newname = .Cells(1) & .Cells(3)
End With
MsgBox newname
End Sub

Regards
Eliano
"AccessHelp" wrote:

Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet 1
into a new workbook. I want to name the new workbook using the two pieces of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1 have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Use a Piece of Data for File Name
 

Good morning RBS,

Yes, Workbook A and the new workbook will be opened. Basically, I have a
worksheet with a command button in Workbook A. When the user clicks on the
button, it will bring up a workbook containing the macro/code and will
execute the code. Once the code is executed, the macro workbook will close.
However, Workbook A will be opened at all time. The new workbook will only
open during the execution (during the move) and save it as the name that we
are trying to accomplish.

Thanks.

"RB Smissaert" wrote:

Is there a worksheet open when that code runs?

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time Error 6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the
worksheet
1
into a new workbook. I want to name the new workbook using the two
pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1 and
C1
have
ABC and 123 in Workbook A, respective. Then the new workbook name will
be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the
new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can
not
be accessed. It didn't save the file, and the new workbook is left
open.

Please help. Thanks.

Use a Piece of Data for File Name
 

Good morning Dan,

I am using Windows XP and Office 2003. Thanks.

"dan dungan" wrote:

Which operating system and version of Excel and are you using?

Dan Dungan

On Sep 26, 11:42 am, AccessHelp
wrote:
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet 1
into a new workbook. I want to name the new workbook using the two pieces of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1 have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Use a Piece of Data for File Name
 

Yes, there is a workbook, but is there a sheet at the time that code runs?
Maybe you should post the relevant code.

RBS


"AccessHelp" wrote in message
...
Good morning RBS,

Yes, Workbook A and the new workbook will be opened. Basically, I have a
worksheet with a command button in Workbook A. When the user clicks on
the
button, it will bring up a workbook containing the macro/code and will
execute the code. Once the code is executed, the macro workbook will
close.
However, Workbook A will be opened at all time. The new workbook will
only
open during the execution (during the move) and save it as the name that
we
are trying to accomplish.

Thanks.

"RB Smissaert" wrote:

Is there a worksheet open when that code runs?

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time Error
6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the
worksheet
1
into a new workbook. I want to name the new workbook using the two
pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1
and
C1
have
ABC and 123 in Workbook A, respective. Then the new workbook name
will
be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name
the
new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls"
can
not
be accessed. It didn't save the file, and the new workbook is left
open.

Please help. Thanks.

Use a Piece of Data for File Name
 

Good morning eliano,

Thanks for the code.

When I tried your code, I got an error:

"'Test.xls' cannot be accessed. The file may be read-only, or you may be
trying to access a read-only location. Or, the server the document is
stored on may not be responding."

If you don't mind, please read my today's response to RB Smissaert above for
the process of my code.

Thanks.

"eliano" wrote:

HI.
Try:

Public Sub prova()
Dim newname As String
With Sheets(1)
newname = .Cells(1) & .Cells(3)
End With
MsgBox newname
End Sub

Regards
Eliano
"AccessHelp" wrote:

Hi,

I have a worksheet 1 in a workbook A. I have a code to move the worksheet 1
into a new workbook. I want to name the new workbook using the two pieces of
data from cells A1 and C1 from Workbook A. For example, cells A1 and C1 have
ABC and 123 in Workbook A, respective. Then the new workbook name will be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name the new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls" can not
be accessed. It didn't save the file, and the new workbook is left open.

Please help. Thanks.

Use a Piece of Data for File Name
 

I am sorry which worksheet are you referring to?

"RB Smissaert" wrote:

Yes, there is a workbook, but is there a sheet at the time that code runs?
Maybe you should post the relevant code.

RBS


"AccessHelp" wrote in message
...
Good morning RBS,

Yes, Workbook A and the new workbook will be opened. Basically, I have a
worksheet with a command button in Workbook A. When the user clicks on
the
button, it will bring up a workbook containing the macro/code and will
execute the code. Once the code is executed, the macro workbook will
close.
However, Workbook A will be opened at all time. The new workbook will
only
open during the execution (during the move) and save it as the name that
we
are trying to accomplish.

Thanks.

"RB Smissaert" wrote:

Is there a worksheet open when that code runs?

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time Error
6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the
worksheet
1
into a new workbook. I want to name the new workbook using the two
pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1
and
C1
have
ABC and 123 in Workbook A, respective. Then the new workbook name
will
be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to name
the
new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file "ABC123.xls"
can
not
be accessed. It didn't save the file, and the new workbook is left
open.

Please help. Thanks.

Use a Piece of Data for File Name
 

Well, you are doing: With Sheets(1)
Is there a Sheets(1) in the referred workbook when that code runs?

RBS


"AccessHelp" wrote in message
...
I am sorry which worksheet are you referring to?

"RB Smissaert" wrote:

Yes, there is a workbook, but is there a sheet at the time that code
runs?
Maybe you should post the relevant code.

RBS


"AccessHelp" wrote in message
...
Good morning RBS,

Yes, Workbook A and the new workbook will be opened. Basically, I have
a
worksheet with a command button in Workbook A. When the user clicks on
the
button, it will bring up a workbook containing the macro/code and will
execute the code. Once the code is executed, the macro workbook will
close.
However, Workbook A will be opened at all time. The new workbook will
only
open during the execution (during the move) and save it as the name
that
we
are trying to accomplish.

Thanks.

"RB Smissaert" wrote:

Is there a worksheet open when that code runs?

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time
Error
6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in
message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the
worksheet
1
into a new workbook. I want to name the new workbook using the
two
pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1
and
C1
have
ABC and 123 in Workbook A, respective. Then the new workbook
name
will
be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to
name
the
new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file
"ABC123.xls"
can
not
be accessed. It didn't save the file, and the new workbook is
left
open.

Please help. Thanks.

Use a Piece of Data for File Name
 

Hi RBS,

Thank you very much for your patience. Yes, I do have the Sheets(1) opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are different from the
names that I posted. In my code, FName1 and FName2 are CRYear and FName,
respectively. What I need help with is located right below "Dim" statements.

If you have any suggestions or recommendation on my code, please feel free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the CSV files are saved
Dim CSVFName As String 'Original Name of CSV file
Dim CSVAFName As String 'Additional Name for CSV File, if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to create additional
CSV files
Dim i As Integer 'Use to create additional CSV files
Dim j As Integer 'Use to create CSV sheet
Dim myFile As String 'Use to test for file existence to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file before creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new worksheet for CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" & Right(Left(nName.Name, 7), 6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And Right(Left(nName.Name, 5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2)) & "-" & Val(Left(Right(nName.Name, 16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value = Left(Right(nName.Name, 14), 2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value = Left(Right(nName.Name, 12), 2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
End If
NewSheet.Cells(j, 10).Value = "'" & Left(Right(nName.Name, 6), 2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit

'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the existing file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" & CSVFName &
"'" & _
" already created for this cost report." & _
" Would you like to overwrite the existing CSV
file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV Macro")
'If 'Yes', overwrite it.
If PROMPT1 = vbYes Then
Sheets("CSV").Select
Sheets("CSV").Move
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , ,
True, _
False, xlNoChange
Application.DisplayAlerts = True
ActiveWindow.Close False
'If 'No', ask the user whether to create a new file with a new
name.
ElseIf PROMPT1 = vbNo Then
PROMPT2 = MsgBox(Prompt:="Would you like to create an
addition CSV file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV
Macro")
'If 'Yes', additional CSV files are created automatically.
If PROMPT2 = vbYes Then
InitName = CSVDir & CSVFName
CSVAFName = InitName
Do
myFile = Dir(CSVAFName)
If myFile < "" Then
i = i + 1
CSVAFName = Left(InitName, Len(InitName) -
4) & i & ".csv"
End If

If i 2 Then
MsgBox "Sorry! There are already " & i & "
files created " & _
"in the directory C:\'. " & Chr(13)
& "No " & _
"additional file is created.",
vbInformation, _
"CSV Macro"
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
Workbooks("MACRO to Create CSV.xls").Close
False
End
End If
Loop While myFile < ""
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVAFName, xlCSVWindows, , , _
True, False, xlNoChange
ActiveWindow.Close False
MsgBox "An additional CSV file '" & Right(CSVAFName,
Len(CSVAFName) - Len(CSVDir)) & _
"' has created in " & _
"the directory 'C:\'.", _
vbInformation, "CSV Macro"

'If 'No', delete the CSV sheet that created in the input
file.
Else
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
MsgBox "No additional CSV file is created.",
vbInformation, _
"CSV Macro"
End If
End If
'If the CSV file does not exist, create one.
Else
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , , False,
xlNoChange
ActiveWindow.Close False
MsgBox "A CSV file '" & CSVFName & "' has created in the directory "
& _
"'C:\'.", vbInformation, _
"CSV Macro"
End If

'Close the Macro file after saving the CSV file
Workbooks("MACRO to Create CSV.xls").Close False
End Sub




"RB Smissaert" wrote:

Well, you are doing: With Sheets(1)
Is there a Sheets(1) in the referred workbook when that code runs?

RBS


"AccessHelp" wrote in message
...
I am sorry which worksheet are you referring to?

"RB Smissaert" wrote:

Yes, there is a workbook, but is there a sheet at the time that code
runs?
Maybe you should post the relevant code.

RBS


"AccessHelp" wrote in message
...
Good morning RBS,

Yes, Workbook A and the new workbook will be opened. Basically, I have
a
worksheet with a command button in Workbook A. When the user clicks on
the
button, it will bring up a workbook containing the macro/code and will
execute the code. Once the code is executed, the macro workbook will
close.
However, Workbook A will be opened at all time. The new workbook will
only
open during the execution (during the move) and save it as the name
that
we
are trying to accomplish.

Thanks.

"RB Smissaert" wrote:

Is there a worksheet open when that code runs?

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time
Error
6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in
message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move the
worksheet
1
into a new workbook. I want to name the new workbook using the
two
pieces
of
data from cells A1 and C1 from Workbook A. For example, cells A1
and
C1
have
ABC and 123 in Workbook A, respective. Then the new workbook
name
will
be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to
name
the
new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file
"ABC123.xls"
can
not
be accessed. It didn't save the file, and the new workbook is
left
open.

Please help. Thanks.

Use a Piece of Data for File Name
 

Ah, that makes it more clear.
For starters change all the As Integer into As Long and try again.

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have the Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are different from the
names that I posted. In my code, FName1 and FName2 are CRYear and FName,
respectively. What I need help with is located right below "Dim"
statements.

If you have any suggestions or recommendation on my code, please feel free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the CSV files are
saved
Dim CSVFName As String 'Original Name of CSV file
Dim CSVAFName As String 'Additional Name for CSV File, if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to create additional
CSV files
Dim i As Integer 'Use to create additional CSV files
Dim j As Integer 'Use to create CSV sheet
Dim myFile As String 'Use to test for file existence to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new worksheet for CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" & Right(Left(nName.Name, 7), 6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And Right(Left(nName.Name,
5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2)) & "-" & Val(Left(Right(nName.Name,
16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value = Left(Right(nName.Name, 14), 2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value = Left(Right(nName.Name, 12), 2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
End If
NewSheet.Cells(j, 10).Value = "'" & Left(Right(nName.Name, 6), 2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit

'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the existing file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" & CSVFName &
"'" & _
" already created for this cost report." & _
" Would you like to overwrite the existing CSV
file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV Macro")
'If 'Yes', overwrite it.
If PROMPT1 = vbYes Then
Sheets("CSV").Select
Sheets("CSV").Move
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , ,
True, _
False, xlNoChange
Application.DisplayAlerts = True
ActiveWindow.Close False
'If 'No', ask the user whether to create a new file with a new
name.
ElseIf PROMPT1 = vbNo Then
PROMPT2 = MsgBox(Prompt:="Would you like to create an
addition CSV file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV
Macro")
'If 'Yes', additional CSV files are created
automatically.
If PROMPT2 = vbYes Then
InitName = CSVDir & CSVFName
CSVAFName = InitName
Do
myFile = Dir(CSVAFName)
If myFile < "" Then
i = i + 1
CSVAFName = Left(InitName, Len(InitName) -
4) & i & ".csv"
End If

If i 2 Then
MsgBox "Sorry! There are already " & i & "
files created " & _
"in the directory C:\'. " &
Chr(13)
& "No " & _
"additional file is created.",
vbInformation, _
"CSV Macro"
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
Workbooks("MACRO to Create CSV.xls").Close
False
End
End If
Loop While myFile < ""
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVAFName, xlCSVWindows, , ,
_
True, False, xlNoChange
ActiveWindow.Close False
MsgBox "An additional CSV file '" &
Right(CSVAFName,
Len(CSVAFName) - Len(CSVDir)) & _
"' has created in " & _
"the directory 'C:\'.", _
vbInformation, "CSV Macro"

'If 'No', delete the CSV sheet that created in the
input
file.
Else
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
MsgBox "No additional CSV file is created.",
vbInformation, _
"CSV Macro"
End If
End If
'If the CSV file does not exist, create one.
Else
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , , False,
xlNoChange
ActiveWindow.Close False
MsgBox "A CSV file '" & CSVFName & "' has created in the directory
"
& _
"'C:\'.", vbInformation, _
"CSV Macro"
End If

'Close the Macro file after saving the CSV file
Workbooks("MACRO to Create CSV.xls").Close False
End Sub




"RB Smissaert" wrote:

Well, you are doing: With Sheets(1)
Is there a Sheets(1) in the referred workbook when that code runs?

RBS


"AccessHelp" wrote in message
...
I am sorry which worksheet are you referring to?

"RB Smissaert" wrote:

Yes, there is a workbook, but is there a sheet at the time that code
runs?
Maybe you should post the relevant code.

RBS


"AccessHelp" wrote in message
...
Good morning RBS,

Yes, Workbook A and the new workbook will be opened. Basically, I
have
a
worksheet with a command button in Workbook A. When the user clicks
on
the
button, it will bring up a workbook containing the macro/code and
will
execute the code. Once the code is executed, the macro workbook
will
close.
However, Workbook A will be opened at all time. The new workbook
will
only
open during the execution (during the move) and save it as the name
that
we
are trying to accomplish.

Thanks.

"RB Smissaert" wrote:

Is there a worksheet open when that code runs?

RBS


"AccessHelp" wrote in
message
...
Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time
Error
6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in
message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move
the
worksheet
1
into a new workbook. I want to name the new workbook using
the
two
pieces
of
data from cells A1 and C1 from Workbook A. For example, cells
A1
and
C1
have
ABC and 123 in Workbook A, respective. Then the new workbook
name
will
be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to
name
the
new
file?

I tried using:

FName1 = Worksheets("1").Range("A1")
FName2 = Worksheets("1").Range("C1")

When I run the code, I got an error message that file
"ABC123.xls"
can
not
be accessed. It didn't save the file, and the new workbook is
left
open.

Please help. Thanks.

Use a Piece of Data for File Name
 

I just changed the CRYear to Long and tried again. I am still getting errors:

"'Test2007.xls' cannot be accessed. The file may be read-only, or you may be
trying to access a read-only location. Or, the server the document is
stored on may not be responding."

Then:

"Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed"

Thanks.

"RB Smissaert" wrote:

Ah, that makes it more clear.
For starters change all the As Integer into As Long and try again.

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have the Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are different from the
names that I posted. In my code, FName1 and FName2 are CRYear and FName,
respectively. What I need help with is located right below "Dim"
statements.

If you have any suggestions or recommendation on my code, please feel free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the CSV files are
saved
Dim CSVFName As String 'Original Name of CSV file
Dim CSVAFName As String 'Additional Name for CSV File, if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to create additional
CSV files
Dim i As Integer 'Use to create additional CSV files
Dim j As Integer 'Use to create CSV sheet
Dim myFile As String 'Use to test for file existence to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new worksheet for CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" & Right(Left(nName.Name, 7), 6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And Right(Left(nName.Name,
5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2)) & "-" & Val(Left(Right(nName.Name,
16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value = Left(Right(nName.Name, 14), 2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value = Left(Right(nName.Name, 12), 2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
End If
NewSheet.Cells(j, 10).Value = "'" & Left(Right(nName.Name, 6), 2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit

'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the existing file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" & CSVFName &
"'" & _
" already created for this cost report." & _
" Would you like to overwrite the existing CSV
file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV Macro")
'If 'Yes', overwrite it.
If PROMPT1 = vbYes Then
Sheets("CSV").Select
Sheets("CSV").Move
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , ,
True, _
False, xlNoChange
Application.DisplayAlerts = True
ActiveWindow.Close False
'If 'No', ask the user whether to create a new file with a new
name.
ElseIf PROMPT1 = vbNo Then
PROMPT2 = MsgBox(Prompt:="Would you like to create an
addition CSV file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV
Macro")
'If 'Yes', additional CSV files are created
automatically.
If PROMPT2 = vbYes Then
InitName = CSVDir & CSVFName
CSVAFName = InitName
Do
myFile = Dir(CSVAFName)
If myFile < "" Then
i = i + 1
CSVAFName = Left(InitName, Len(InitName) -
4) & i & ".csv"
End If

If i 2 Then
MsgBox "Sorry! There are already " & i & "
files created " & _
"in the directory C:\'. " &
Chr(13)
& "No " & _
"additional file is created.",
vbInformation, _
"CSV Macro"
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
Workbooks("MACRO to Create CSV.xls").Close
False
End
End If
Loop While myFile < ""
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVAFName, xlCSVWindows, , ,
_
True, False, xlNoChange
ActiveWindow.Close False
MsgBox "An additional CSV file '" &
Right(CSVAFName,
Len(CSVAFName) - Len(CSVDir)) & _
"' has created in " & _
"the directory 'C:\'.", _
vbInformation, "CSV Macro"

'If 'No', delete the CSV sheet that created in the
input
file.
Else
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
MsgBox "No additional CSV file is created.",
vbInformation, _
"CSV Macro"
End If
End If
'If the CSV file does not exist, create one.
Else
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , , False,
xlNoChange
ActiveWindow.Close False
MsgBox "A CSV file '" & CSVFName & "' has created in the directory
"
& _
"'C:\'.", vbInformation, _
"CSV Macro"
End If

'Close the Macro file after saving the CSV file
Workbooks("MACRO to Create CSV.xls").Close False
End Sub




"RB Smissaert" wrote:

Well, you are doing: With Sheets(1)
Is there a Sheets(1) in the referred workbook when that code runs?

RBS


"AccessHelp" wrote in message
...
I am sorry which worksheet are you referring to?

"RB Smissaert" wrote:

Yes, there is a workbook, but is there a sheet at the time that code
runs?
Maybe you should post the relevant code.

RBS


"AccessHelp" wrote in message
...
Good morning RBS,

Yes, Workbook A and the new workbook will be opened. Basically, I
have
a
worksheet with a command button in Workbook A. When the user clicks
on
the
button, it will bring up a workbook containing the macro/code and
will
execute the code. Once the code is executed, the macro workbook
will
close.
However, Workbook A will be opened at all time. The new workbook
will
only
open during the execution (during the move) and save it as the name
that
we
are trying to accomplish.

Thanks.

"RB Smissaert" wrote:

Is there a worksheet open when that code runs?

RBS


"AccessHelp" wrote in
message
...
Hi RBS,

Thank you for the code. When I try it, I got an error "Run-time
Error
6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in
message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to move
the
worksheet
1
into a new workbook. I want to name the new workbook using
the
two
pieces
of
data from cells A1 and C1 from Workbook A. For example, cells
A1
and
C1
have
ABC and 123 in Workbook A, respective. Then the new workbook
name
will
be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order to
name
the
new

Use a Piece of Data for File Name
 

OK, so you made some progress.
Now try this:

First download and install MZ-Tools:
http://www.mztools.com/index.aspx
This is very useful (and free) in any case.

Then at the top of that procedure (maybe after the Dim declarations) put
this:
On Error GoTo ERROROUT

Then at the very end of that procedure put this:

Exit Sub
ERROROUT:

Msgbox Err.Description,, "error at line " & Erl

Then right-click in the procedure and do:
MZ-Tools, Add line numbers

Now run again and see what shows and take it from there.


RBS


"AccessHelp" wrote in message
...
I just changed the CRYear to Long and tried again. I am still getting
errors:

"'Test2007.xls' cannot be accessed. The file may be read-only, or you may
be
trying to access a read-only location. Or, the server the document is
stored on may not be responding."

Then:

"Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed"

Thanks.

"RB Smissaert" wrote:

Ah, that makes it more clear.
For starters change all the As Integer into As Long and try again.

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have the Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are different from
the
names that I posted. In my code, FName1 and FName2 are CRYear and
FName,
respectively. What I need help with is located right below "Dim"
statements.

If you have any suggestions or recommendation on my code, please feel
free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the CSV files are
saved
Dim CSVFName As String 'Original Name of CSV file
Dim CSVAFName As String 'Additional Name for CSV File,
if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to create
additional
CSV files
Dim i As Integer 'Use to create additional CSV
files
Dim j As Integer 'Use to create CSV sheet
Dim myFile As String 'Use to test for file existence
to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new worksheet for CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" & Right(Left(nName.Name, 7), 6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And Right(Left(nName.Name,
5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2)) & "-" & Val(Left(Right(nName.Name,
16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And
Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value = Left(Right(nName.Name, 14), 2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value = Left(Right(nName.Name, 12), 2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
End If
NewSheet.Cells(j, 10).Value = "'" & Left(Right(nName.Name, 6),
2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit

'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the existing file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" & CSVFName
&
"'" & _
" already created for this cost report." & _
" Would you like to overwrite the existing CSV
file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV
Macro")
'If 'Yes', overwrite it.
If PROMPT1 = vbYes Then
Sheets("CSV").Select
Sheets("CSV").Move
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, ,
,
True, _
False, xlNoChange
Application.DisplayAlerts = True
ActiveWindow.Close False
'If 'No', ask the user whether to create a new file with a
new
name.
ElseIf PROMPT1 = vbNo Then
PROMPT2 = MsgBox(Prompt:="Would you like to create an
addition CSV file?", _
Buttons:=vbYesNo + vbQuestion,
Title:="CSV
Macro")
'If 'Yes', additional CSV files are created
automatically.
If PROMPT2 = vbYes Then
InitName = CSVDir & CSVFName
CSVAFName = InitName
Do
myFile = Dir(CSVAFName)
If myFile < "" Then
i = i + 1
CSVAFName = Left(InitName,
Len(InitName) -
4) & i & ".csv"
End If

If i 2 Then
MsgBox "Sorry! There are already " & i &
"
files created " & _
"in the directory C:\'. " &
Chr(13)
& "No " & _
"additional file is created.",
vbInformation, _
"CSV Macro"
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
Workbooks("MACRO to Create
CSV.xls").Close
False
End
End If
Loop While myFile < ""
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVAFName, xlCSVWindows, ,
,
_
True, False, xlNoChange
ActiveWindow.Close False
MsgBox "An additional CSV file '" &
Right(CSVAFName,
Len(CSVAFName) - Len(CSVDir)) & _
"' has created in " & _
"the directory 'C:\'.", _
vbInformation, "CSV Macro"

'If 'No', delete the CSV sheet that created in the
input
file.
Else
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
MsgBox "No additional CSV file is created.",
vbInformation, _
"CSV Macro"
End If
End If
'If the CSV file does not exist, create one.
Else
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , ,
False,
xlNoChange
ActiveWindow.Close False
MsgBox "A CSV file '" & CSVFName & "' has created in the
directory
"
& _
"'C:\'.", vbInformation, _
"CSV Macro"
End If

'Close the Macro file after saving the CSV file
Workbooks("MACRO to Create CSV.xls").Close False
End Sub




"RB Smissaert" wrote:

Well, you are doing: With Sheets(1)
Is there a Sheets(1) in the referred workbook when that code runs?

RBS


"AccessHelp" wrote in message
...
I am sorry which worksheet are you referring to?

"RB Smissaert" wrote:

Yes, there is a workbook, but is there a sheet at the time that
code
runs?
Maybe you should post the relevant code.

RBS


"AccessHelp" wrote in
message
...
Good morning RBS,

Yes, Workbook A and the new workbook will be opened. Basically,
I
have
a
worksheet with a command button in Workbook A. When the user
clicks
on
the
button, it will bring up a workbook containing the macro/code and
will
execute the code. Once the code is executed, the macro workbook
will
close.
However, Workbook A will be opened at all time. The new workbook
will
only
open during the execution (during the move) and save it as the
name
that
we
are trying to accomplish.

Thanks.

"RB Smissaert" wrote:

Is there a worksheet open when that code runs?

RBS


"AccessHelp" wrote in
message
...
Hi RBS,

Thank you for the code. When I try it, I got an error
"Run-time
Error
6:
overflow".

Thanks.

"RB Smissaert" wrote:

Try this:

With Sheets(1)
FName1 = .Cells(1)
FName2 = .Cells(3)
End With


RBS



"AccessHelp" wrote in
message
...
Hi,

I have a worksheet 1 in a workbook A. I have a code to
move
the
worksheet
1
into a new workbook. I want to name the new workbook using
the
two
pieces
of
data from cells A1 and C1 from Workbook A. For example,
cells
A1
and
C1
have
ABC and 123 in Workbook A, respective. Then the new
workbook
name
will
be
"ABC123.xls".

How should I reference Cells A1 and C1 in my code in order
to
name
the
new

Use a Piece of Data for File Name
 

Per your instructions, I downloaded MZ-Tools 3.0 for VBA and followed all the
way to right-click in the procedures. When I right-click anywhere in the
procedures, am I supposed to see MZ-Tools as an option and clicked on it? I
do not see it as an option.

Thanks.

"RB Smissaert" wrote:

OK, so you made some progress.
Now try this:

First download and install MZ-Tools:
http://www.mztools.com/index.aspx
This is very useful (and free) in any case.

Then at the top of that procedure (maybe after the Dim declarations) put
this:
On Error GoTo ERROROUT

Then at the very end of that procedure put this:

Exit Sub
ERROROUT:

Msgbox Err.Description,, "error at line " & Erl

Then right-click in the procedure and do:
MZ-Tools, Add line numbers

Now run again and see what shows and take it from there.


RBS


"AccessHelp" wrote in message
...
I just changed the CRYear to Long and tried again. I am still getting
errors:

"'Test2007.xls' cannot be accessed. The file may be read-only, or you may
be
trying to access a read-only location. Or, the server the document is
stored on may not be responding."

Then:

"Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed"

Thanks.

"RB Smissaert" wrote:

Ah, that makes it more clear.
For starters change all the As Integer into As Long and try again.

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have the Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are different from
the
names that I posted. In my code, FName1 and FName2 are CRYear and
FName,
respectively. What I need help with is located right below "Dim"
statements.

If you have any suggestions or recommendation on my code, please feel
free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the CSV files are
saved
Dim CSVFName As String 'Original Name of CSV file
Dim CSVAFName As String 'Additional Name for CSV File,
if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to create
additional
CSV files
Dim i As Integer 'Use to create additional CSV
files
Dim j As Integer 'Use to create CSV sheet
Dim myFile As String 'Use to test for file existence
to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new worksheet for CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" & Right(Left(nName.Name, 7), 6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And Right(Left(nName.Name,
5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2)) & "-" & Val(Left(Right(nName.Name,
16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And
Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value = Left(Right(nName.Name, 14), 2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value = Left(Right(nName.Name, 12), 2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
End If
NewSheet.Cells(j, 10).Value = "'" & Left(Right(nName.Name, 6),
2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit

'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the existing file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" & CSVFName
&
"'" & _
" already created for this cost report." & _
" Would you like to overwrite the existing CSV
file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV
Macro")
'If 'Yes', overwrite it.
If PROMPT1 = vbYes Then
Sheets("CSV").Select
Sheets("CSV").Move
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, ,
,
True, _
False, xlNoChange
Application.DisplayAlerts = True
ActiveWindow.Close False
'If 'No', ask the user whether to create a new file with a
new
name.
ElseIf PROMPT1 = vbNo Then
PROMPT2 = MsgBox(Prompt:="Would you like to create an
addition CSV file?", _
Buttons:=vbYesNo + vbQuestion,
Title:="CSV
Macro")
'If 'Yes', additional CSV files are created
automatically.
If PROMPT2 = vbYes Then
InitName = CSVDir & CSVFName
CSVAFName = InitName
Do
myFile = Dir(CSVAFName)
If myFile < "" Then
i = i + 1
CSVAFName = Left(InitName,
Len(InitName) -
4) & i & ".csv"
End If

If i 2 Then
MsgBox "Sorry! There are already " & i &
"
files created " & _
"in the directory C:\'. " &
Chr(13)
& "No " & _
"additional file is created.",
vbInformation, _
"CSV Macro"
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
Workbooks("MACRO to Create
CSV.xls").Close
False
End
End If
Loop While myFile < ""
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVAFName, xlCSVWindows, ,
,
_
True, False, xlNoChange
ActiveWindow.Close False
MsgBox "An additional CSV file '" &
Right(CSVAFName,
Len(CSVAFName) - Len(CSVDir)) & _
"' has created in " & _
"the directory 'C:\'.", _
vbInformation, "CSV Macro"

'If 'No', delete the CSV sheet that created in the
input
file.
Else
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
MsgBox "No additional CSV file is created.",
vbInformation, _
"CSV Macro"
End If
End If
'If the CSV file does not exist, create one.
Else
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , ,
False,
xlNoChange
ActiveWindow.Close False
MsgBox "A CSV file '" & CSVFName & "' has created in the
directory
"
& _
"'C:\'.", vbInformation, _
"CSV Macro"
End If

'Close the Macro file after saving the CSV file
Workbooks("MACRO to Create CSV.xls").Close False
End Sub




"RB Smissaert" wrote:

Well, you are doing: With Sheets(1)
Is there a Sheets(1) in the referred workbook when that code runs?

RBS


"AccessHelp" wrote in message
...
I am sorry which worksheet are you referring to?

"RB Smissaert" wrote:

Yes, there is a workbook, but is there a sheet at the time that
code
runs?
Maybe you should post the relevant code.

RBS


"AccessHelp" wrote in
message
...
Good morning RBS,

Yes, Workbook A and the new workbook will be opened. Basically,
I
have
a
worksheet with a command button in Workbook A. When the user
clicks
on
the
button, it will bring up a workbook containing the macro/code and
will
execute the code. Once the code is executed, the macro workbook
will
close.
However, Workbook A will be opened at all time. The new workbook
will
only

Use a Piece of Data for File Name
 

Try close and re-start Excel.

RBS

"AccessHelp" wrote in message
...
Per your instructions, I downloaded MZ-Tools 3.0 for VBA and followed all
the
way to right-click in the procedures. When I right-click anywhere in the
procedures, am I supposed to see MZ-Tools as an option and clicked on it?
I
do not see it as an option.

Thanks.

"RB Smissaert" wrote:

OK, so you made some progress.
Now try this:

First download and install MZ-Tools:
http://www.mztools.com/index.aspx
This is very useful (and free) in any case.

Then at the top of that procedure (maybe after the Dim declarations) put
this:
On Error GoTo ERROROUT

Then at the very end of that procedure put this:

Exit Sub
ERROROUT:

Msgbox Err.Description,, "error at line " & Erl

Then right-click in the procedure and do:
MZ-Tools, Add line numbers

Now run again and see what shows and take it from there.


RBS


"AccessHelp" wrote in message
...
I just changed the CRYear to Long and tried again. I am still getting
errors:

"'Test2007.xls' cannot be accessed. The file may be read-only, or you
may
be
trying to access a read-only location. Or, the server the document is
stored on may not be responding."

Then:

"Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed"

Thanks.

"RB Smissaert" wrote:

Ah, that makes it more clear.
For starters change all the As Integer into As Long and try again.

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have the Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are different
from
the
names that I posted. In my code, FName1 and FName2 are CRYear and
FName,
respectively. What I need help with is located right below "Dim"
statements.

If you have any suggestions or recommendation on my code, please
feel
free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the CSV files
are
saved
Dim CSVFName As String 'Original Name of CSV file
Dim CSVAFName As String 'Additional Name for CSV
File,
if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to create
additional
CSV files
Dim i As Integer 'Use to create additional CSV
files
Dim j As Integer 'Use to create CSV sheet
Dim myFile As String 'Use to test for file
existence
to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new worksheet for
CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" & Right(Left(nName.Name, 7),
6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And
Right(Left(nName.Name,
5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2)) & "-" &
Val(Left(Right(nName.Name,
16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And
Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value = Left(Right(nName.Name, 14),
2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value = Left(Right(nName.Name, 12),
2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
End If
NewSheet.Cells(j, 10).Value = "'" & Left(Right(nName.Name,
6),
2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit

'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the existing file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" &
CSVFName
&
"'" & _
" already created for this cost report." & _
" Would you like to overwrite the existing
CSV
file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV
Macro")
'If 'Yes', overwrite it.
If PROMPT1 = vbYes Then
Sheets("CSV").Select
Sheets("CSV").Move
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs CSVDir & CSVFName,
xlCSVWindows, ,
,
True, _
False, xlNoChange
Application.DisplayAlerts = True
ActiveWindow.Close False
'If 'No', ask the user whether to create a new file with
a
new
name.
ElseIf PROMPT1 = vbNo Then
PROMPT2 = MsgBox(Prompt:="Would you like to create an
addition CSV file?", _
Buttons:=vbYesNo + vbQuestion,
Title:="CSV
Macro")
'If 'Yes', additional CSV files are created
automatically.
If PROMPT2 = vbYes Then
InitName = CSVDir & CSVFName
CSVAFName = InitName
Do
myFile = Dir(CSVAFName)
If myFile < "" Then
i = i + 1
CSVAFName = Left(InitName,
Len(InitName) -
4) & i & ".csv"
End If

If i 2 Then
MsgBox "Sorry! There are already " &
i &
"
files created " & _
"in the directory C:\'. " &
Chr(13)
& "No " & _
"additional file is
created.",
vbInformation, _
"CSV Macro"
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
Workbooks("MACRO to Create
CSV.xls").Close
False
End
End If
Loop While myFile < ""
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVAFName,
xlCSVWindows, ,
,
_
True, False, xlNoChange
ActiveWindow.Close False
MsgBox "An additional CSV file '" &
Right(CSVAFName,
Len(CSVAFName) - Len(CSVDir)) & _
"' has created in " & _
"the directory 'C:\'.", _
vbInformation, "CSV Macro"

'If 'No', delete the CSV sheet that created in
the
input
file.
Else
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
MsgBox "No additional CSV file is created.",
vbInformation, _
"CSV Macro"
End If
End If
'If the CSV file does not exist, create one.
Else
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , ,
False,
xlNoChange
ActiveWindow.Close False
MsgBox "A CSV file '" & CSVFName & "' has created in the
directory
"
& _
"'C:\'.", vbInformation, _
"CSV Macro"
End If

'Close the Macro file after saving the CSV file
Workbooks("MACRO to Create CSV.xls").Close False
End Sub




"RB Smissaert" wrote:

Well, you are doing: With Sheets(1)
Is there a Sheets(1) in the referred workbook when that code runs?

RBS


"AccessHelp" wrote in
message
...
I am sorry which worksheet are you referring to?

"RB Smissaert" wrote:

Yes, there is a workbook, but is there a sheet at the time that
code
runs?
Maybe you should post the relevant code.

RBS


"AccessHelp" wrote in
message
...
Good morning RBS,

Yes, Workbook A and the new workbook will be opened.
Basically,
I
have
a
worksheet with a command button in Workbook A. When the user
clicks
on
the
button, it will bring up a workbook containing the macro/code
and
will
execute the code. Once the code is executed, the macro
workbook
will
close.
However, Workbook A will be opened at all time. The new
workbook
will
only

Use a Piece of Data for File Name
 

It indicates the error is on Line 790. If I understand you correctly, I
should put the following code before "End Sub"?

ERROROUT:
Msgbox Err.Description,, "error at line " & Erl

Thanks.


"RB Smissaert" wrote:

Try close and re-start Excel.

RBS

"AccessHelp" wrote in message
...
Per your instructions, I downloaded MZ-Tools 3.0 for VBA and followed all
the
way to right-click in the procedures. When I right-click anywhere in the
procedures, am I supposed to see MZ-Tools as an option and clicked on it?
I
do not see it as an option.

Thanks.

"RB Smissaert" wrote:

OK, so you made some progress.
Now try this:

First download and install MZ-Tools:
http://www.mztools.com/index.aspx
This is very useful (and free) in any case.

Then at the top of that procedure (maybe after the Dim declarations) put
this:
On Error GoTo ERROROUT

Then at the very end of that procedure put this:

Exit Sub
ERROROUT:

Msgbox Err.Description,, "error at line " & Erl

Then right-click in the procedure and do:
MZ-Tools, Add line numbers

Now run again and see what shows and take it from there.


RBS


"AccessHelp" wrote in message
...
I just changed the CRYear to Long and tried again. I am still getting
errors:

"'Test2007.xls' cannot be accessed. The file may be read-only, or you
may
be
trying to access a read-only location. Or, the server the document is
stored on may not be responding."

Then:

"Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed"

Thanks.

"RB Smissaert" wrote:

Ah, that makes it more clear.
For starters change all the As Integer into As Long and try again.

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have the Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are different
from
the
names that I posted. In my code, FName1 and FName2 are CRYear and
FName,
respectively. What I need help with is located right below "Dim"
statements.

If you have any suggestions or recommendation on my code, please
feel
free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the CSV files
are
saved
Dim CSVFName As String 'Original Name of CSV file
Dim CSVAFName As String 'Additional Name for CSV
File,
if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to create
additional
CSV files
Dim i As Integer 'Use to create additional CSV
files
Dim j As Integer 'Use to create CSV sheet
Dim myFile As String 'Use to test for file
existence
to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new worksheet for
CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" & Right(Left(nName.Name, 7),
6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And
Right(Left(nName.Name,
5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2)) & "-" &
Val(Left(Right(nName.Name,
16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And
Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value = Left(Right(nName.Name, 14),
2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value = Left(Right(nName.Name, 12),
2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
End If
NewSheet.Cells(j, 10).Value = "'" & Left(Right(nName.Name,
6),
2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit

'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the existing file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" &
CSVFName
&
"'" & _
" already created for this cost report." & _
" Would you like to overwrite the existing
CSV
file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV
Macro")
'If 'Yes', overwrite it.
If PROMPT1 = vbYes Then
Sheets("CSV").Select
Sheets("CSV").Move
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs CSVDir & CSVFName,
xlCSVWindows, ,
,
True, _
False, xlNoChange
Application.DisplayAlerts = True
ActiveWindow.Close False
'If 'No', ask the user whether to create a new file with
a
new
name.
ElseIf PROMPT1 = vbNo Then
PROMPT2 = MsgBox(Prompt:="Would you like to create an
addition CSV file?", _
Buttons:=vbYesNo + vbQuestion,
Title:="CSV
Macro")
'If 'Yes', additional CSV files are created
automatically.
If PROMPT2 = vbYes Then
InitName = CSVDir & CSVFName
CSVAFName = InitName
Do
myFile = Dir(CSVAFName)
If myFile < "" Then
i = i + 1
CSVAFName = Left(InitName,
Len(InitName) -
4) & i & ".csv"
End If

If i 2 Then
MsgBox "Sorry! There are already " &
i &
"
files created " & _
"in the directory C:\'. " &
Chr(13)
& "No " & _
"additional file is
created.",
vbInformation, _
"CSV Macro"
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
Workbooks("MACRO to Create
CSV.xls").Close
False
End
End If
Loop While myFile < ""
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVAFName,
xlCSVWindows, ,
,
_
True, False, xlNoChange
ActiveWindow.Close False
MsgBox "An additional CSV file '" &
Right(CSVAFName,
Len(CSVAFName) - Len(CSVDir)) & _
"' has created in " & _
"the directory 'C:\'.", _
vbInformation, "CSV Macro"

'If 'No', delete the CSV sheet that created in
the
input
file.
Else
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
MsgBox "No additional CSV file is created.",
vbInformation, _
"CSV Macro"
End If
End If
'If the CSV file does not exist, create one.
Else
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , ,
False,
xlNoChange
ActiveWindow.Close False
MsgBox "A CSV file '" & CSVFName & "' has created in the
directory
"
& _
"'C:\'.", vbInformation, _
"CSV Macro"
End If

'Close the Macro file after saving the CSV file
Workbooks("MACRO to Create CSV.xls").Close False
End Sub




"RB Smissaert" wrote:

Well, you are doing: With Sheets(1)
Is there a Sheets(1) in the referred workbook when that code runs?

Use a Piece of Data for File Name
 

If I understand you correctly, I should put the following code before "End
Sub"?

Yes, that is it.

Now what do you have at line 790 and what are the values of the variables in
that line?

RBS

"AccessHelp" wrote in message
...
It indicates the error is on Line 790. If I understand you correctly, I
should put the following code before "End Sub"?

ERROROUT:
Msgbox Err.Description,, "error at line " & Erl

Thanks.


"RB Smissaert" wrote:

Try close and re-start Excel.

RBS

"AccessHelp" wrote in message
...
Per your instructions, I downloaded MZ-Tools 3.0 for VBA and followed
all
the
way to right-click in the procedures. When I right-click anywhere in
the
procedures, am I supposed to see MZ-Tools as an option and clicked on
it?
I
do not see it as an option.

Thanks.

"RB Smissaert" wrote:

OK, so you made some progress.
Now try this:

First download and install MZ-Tools:
http://www.mztools.com/index.aspx
This is very useful (and free) in any case.

Then at the top of that procedure (maybe after the Dim declarations)
put
this:
On Error GoTo ERROROUT

Then at the very end of that procedure put this:

Exit Sub
ERROROUT:

Msgbox Err.Description,, "error at line " & Erl

Then right-click in the procedure and do:
MZ-Tools, Add line numbers

Now run again and see what shows and take it from there.


RBS


"AccessHelp" wrote in message
...
I just changed the CRYear to Long and tried again. I am still
getting
errors:

"'Test2007.xls' cannot be accessed. The file may be read-only, or
you
may
be
trying to access a read-only location. Or, the server the document
is
stored on may not be responding."

Then:

"Run-time error '1004': Method 'SaveAs' of object '_Workbook'
failed"

Thanks.

"RB Smissaert" wrote:

Ah, that makes it more clear.
For starters change all the As Integer into As Long and try again.

RBS


"AccessHelp" wrote in
message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have the
Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are different
from
the
names that I posted. In my code, FName1 and FName2 are CRYear
and
FName,
respectively. What I need help with is located right below "Dim"
statements.

If you have any suggestions or recommendation on my code, please
feel
free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the CSV
files
are
saved
Dim CSVFName As String 'Original Name of CSV file
Dim CSVAFName As String 'Additional Name for CSV
File,
if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to create
additional
CSV files
Dim i As Integer 'Use to create additional
CSV
files
Dim j As Integer 'Use to create CSV sheet
Dim myFile As String 'Use to test for file
existence
to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file
before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new worksheet
for
CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" & Right(Left(nName.Name,
7),
6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And
Right(Left(nName.Name,
5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-"
&
Val(Left(Right(nName.Name, 18), 2)) & "-" &
Val(Left(Right(nName.Name,
16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And
Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-"
&
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value = Left(Right(nName.Name,
14),
2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value = Left(Right(nName.Name,
12),
2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
End If
NewSheet.Cells(j, 10).Value = "'" & Left(Right(nName.Name,
6),
2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit

'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the existing
file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" &
CSVFName
&
"'" & _
" already created for this cost report." &
_
" Would you like to overwrite the
existing
CSV
file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV
Macro")
'If 'Yes', overwrite it.
If PROMPT1 = vbYes Then
Sheets("CSV").Select
Sheets("CSV").Move
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs CSVDir & CSVFName,
xlCSVWindows, ,
,
True, _
False, xlNoChange
Application.DisplayAlerts = True
ActiveWindow.Close False
'If 'No', ask the user whether to create a new file
with
a
new
name.
ElseIf PROMPT1 = vbNo Then
PROMPT2 = MsgBox(Prompt:="Would you like to create
an
addition CSV file?", _
Buttons:=vbYesNo + vbQuestion,
Title:="CSV
Macro")
'If 'Yes', additional CSV files are created
automatically.
If PROMPT2 = vbYes Then
InitName = CSVDir & CSVFName
CSVAFName = InitName
Do
myFile = Dir(CSVAFName)
If myFile < "" Then
i = i + 1
CSVAFName = Left(InitName,
Len(InitName) -
4) & i & ".csv"
End If

If i 2 Then
MsgBox "Sorry! There are already "
&
i &
"
files created " & _
"in the directory C:\'. "
&
Chr(13)
& "No " & _
"additional file is
created.",
vbInformation, _
"CSV Macro"
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
Workbooks("MACRO to Create
CSV.xls").Close
False
End
End If
Loop While myFile < ""
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVAFName,
xlCSVWindows, ,
,
_
True, False, xlNoChange
ActiveWindow.Close False
MsgBox "An additional CSV file '" &
Right(CSVAFName,
Len(CSVAFName) - Len(CSVDir)) & _
"' has created in " & _
"the directory 'C:\'.", _
vbInformation, "CSV Macro"

'If 'No', delete the CSV sheet that created in
the
input
file.
Else
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
MsgBox "No additional CSV file is
created.",
vbInformation, _
"CSV Macro"
End If
End If
'If the CSV file does not exist, create one.
Else
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , ,
,
False,
xlNoChange
ActiveWindow.Close False
MsgBox "A CSV file '" & CSVFName & "' has created in the
directory
"
& _
"'C:\'.", vbInformation, _
"CSV Macro"
End If

'Close the Macro file after saving the CSV file
Workbooks("MACRO to Create CSV.xls").Close False
End Sub




"RB Smissaert" wrote:

Well, you are doing: With Sheets(1)
Is there a Sheets(1) in the referred workbook when that code
runs?

Use a Piece of Data for File Name
 

Hi RBS,

Thank you again very much for your patience.

First of all, Line 79 is "ActiveWorkbook.SaveAs CSVDir & CSVFName,
xlCSVWindows, , , , False, xlNoChange".

Secondly, I find where the problem is, and I do not know how to solve it.
The problem is on the "CRYear".

In addition to using "CRYear" in "CSVFName", I also use it in "CSVDir".
CSVDir is the directory where the CSV file will be saved, and CSVFName is the
file name of a CSV file. The code that I posted has the CSVDir as "C:\".
Actually, it should be ""C:\" & CRYear". Due to business reason, we have CSV
files for different years. The CSV files are named combination of customer
name and the year for the CSV file (e.g. ABC2007.csv) and are saved in the
appropriate folder for the CSV year. The customer name and CSV year should
come from the input file (Workbook A). Apparently, the VBA does not like the
fact that CRYear is being used for various purpose.

I have tried to creating a new variable (DIRYear) and referencing the
DIRYear to a different cell. The code still does not work. I am still
getting the same error.

Is there a way around it? Thanks.

"RB Smissaert" wrote:

If I understand you correctly, I should put the following code before "End
Sub"?

Yes, that is it.

Now what do you have at line 790 and what are the values of the variables in
that line?

RBS

"AccessHelp" wrote in message
...
It indicates the error is on Line 790. If I understand you correctly, I
should put the following code before "End Sub"?

ERROROUT:
Msgbox Err.Description,, "error at line " & Erl

Thanks.


"RB Smissaert" wrote:

Try close and re-start Excel.

RBS

"AccessHelp" wrote in message
...
Per your instructions, I downloaded MZ-Tools 3.0 for VBA and followed
all
the
way to right-click in the procedures. When I right-click anywhere in
the
procedures, am I supposed to see MZ-Tools as an option and clicked on
it?
I
do not see it as an option.

Thanks.

"RB Smissaert" wrote:

OK, so you made some progress.
Now try this:

First download and install MZ-Tools:
http://www.mztools.com/index.aspx
This is very useful (and free) in any case.

Then at the top of that procedure (maybe after the Dim declarations)
put
this:
On Error GoTo ERROROUT

Then at the very end of that procedure put this:

Exit Sub
ERROROUT:

Msgbox Err.Description,, "error at line " & Erl

Then right-click in the procedure and do:
MZ-Tools, Add line numbers

Now run again and see what shows and take it from there.


RBS


"AccessHelp" wrote in message
...
I just changed the CRYear to Long and tried again. I am still
getting
errors:

"'Test2007.xls' cannot be accessed. The file may be read-only, or
you
may
be
trying to access a read-only location. Or, the server the document
is
stored on may not be responding."

Then:

"Run-time error '1004': Method 'SaveAs' of object '_Workbook'
failed"

Thanks.

"RB Smissaert" wrote:

Ah, that makes it more clear.
For starters change all the As Integer into As Long and try again.

RBS


"AccessHelp" wrote in
message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have the
Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are different
from
the
names that I posted. In my code, FName1 and FName2 are CRYear
and
FName,
respectively. What I need help with is located right below "Dim"
statements.

If you have any suggestions or recommendation on my code, please
feel
free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the CSV
files
are
saved
Dim CSVFName As String 'Original Name of CSV file
Dim CSVAFName As String 'Additional Name for CSV
File,
if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to create
additional
CSV files
Dim i As Integer 'Use to create additional
CSV
files
Dim j As Integer 'Use to create CSV sheet
Dim myFile As String 'Use to test for file
existence
to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file
before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new worksheet
for
CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" & Right(Left(nName.Name,
7),
6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And
Right(Left(nName.Name,
5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-"
&
Val(Left(Right(nName.Name, 18), 2)) & "-" &
Val(Left(Right(nName.Name,
16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And
Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-"
&
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value = Left(Right(nName.Name,
14),
2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value = Left(Right(nName.Name,
12),
2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
End If
NewSheet.Cells(j, 10).Value = "'" & Left(Right(nName.Name,
6),
2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit

'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the existing
file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" &
CSVFName
&
"'" & _
" already created for this cost report." &
_
" Would you like to overwrite the
existing
CSV
file?", _
Buttons:=vbYesNo + vbQuestion, Title:="CSV
Macro")
'If 'Yes', overwrite it.
If PROMPT1 = vbYes Then
Sheets("CSV").Select
Sheets("CSV").Move
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs CSVDir & CSVFName,
xlCSVWindows, ,
,
True, _
False, xlNoChange
Application.DisplayAlerts = True
ActiveWindow.Close False
'If 'No', ask the user whether to create a new file
with
a
new
name.
ElseIf PROMPT1 = vbNo Then
PROMPT2 = MsgBox(Prompt:="Would you like to create
an
addition CSV file?", _
Buttons:=vbYesNo + vbQuestion,
Title:="CSV
Macro")
'If 'Yes', additional CSV files are created
automatically.
If PROMPT2 = vbYes Then
InitName = CSVDir & CSVFName
CSVAFName = InitName
Do
myFile = Dir(CSVAFName)
If myFile < "" Then
i = i + 1
CSVAFName = Left(InitName,
Len(InitName) -
4) & i & ".csv"
End If

If i 2 Then
MsgBox "Sorry! There are already "
&
i &
"
files created " & _
"in the directory C:\'. "
&
Chr(13)
& "No " & _
"additional file is
created.",
vbInformation, _
"CSV Macro"
Application.DisplayAlerts = False
Sheets("CSV").Delete
Application.DisplayAlerts = True
Workbooks("MACRO to Create
CSV.xls").Close
False
End
End If
Loop While myFile < ""
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVAFName,
xlCSVWindows, ,
,
_

Use a Piece of Data for File Name
 

Sorry, you lost me a bit now.
You will have to get to the essential part of the problem.

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you again very much for your patience.

First of all, Line 79 is "ActiveWorkbook.SaveAs CSVDir & CSVFName,
xlCSVWindows, , , , False, xlNoChange".

Secondly, I find where the problem is, and I do not know how to solve it.
The problem is on the "CRYear".

In addition to using "CRYear" in "CSVFName", I also use it in "CSVDir".
CSVDir is the directory where the CSV file will be saved, and CSVFName is
the
file name of a CSV file. The code that I posted has the CSVDir as "C:\".
Actually, it should be ""C:\" & CRYear". Due to business reason, we have
CSV
files for different years. The CSV files are named combination of
customer
name and the year for the CSV file (e.g. ABC2007.csv) and are saved in the
appropriate folder for the CSV year. The customer name and CSV year
should
come from the input file (Workbook A). Apparently, the VBA does not like
the
fact that CRYear is being used for various purpose.

I have tried to creating a new variable (DIRYear) and referencing the
DIRYear to a different cell. The code still does not work. I am still
getting the same error.

Is there a way around it? Thanks.

"RB Smissaert" wrote:

If I understand you correctly, I should put the following code before
"End
Sub"?

Yes, that is it.

Now what do you have at line 790 and what are the values of the variables
in
that line?

RBS

"AccessHelp" wrote in message
...
It indicates the error is on Line 790. If I understand you correctly,
I
should put the following code before "End Sub"?

ERROROUT:
Msgbox Err.Description,, "error at line " & Erl

Thanks.


"RB Smissaert" wrote:

Try close and re-start Excel.

RBS

"AccessHelp" wrote in message
...
Per your instructions, I downloaded MZ-Tools 3.0 for VBA and
followed
all
the
way to right-click in the procedures. When I right-click anywhere
in
the
procedures, am I supposed to see MZ-Tools as an option and clicked
on
it?
I
do not see it as an option.

Thanks.

"RB Smissaert" wrote:

OK, so you made some progress.
Now try this:

First download and install MZ-Tools:
http://www.mztools.com/index.aspx
This is very useful (and free) in any case.

Then at the top of that procedure (maybe after the Dim
declarations)
put
this:
On Error GoTo ERROROUT

Then at the very end of that procedure put this:

Exit Sub
ERROROUT:

Msgbox Err.Description,, "error at line " & Erl

Then right-click in the procedure and do:
MZ-Tools, Add line numbers

Now run again and see what shows and take it from there.


RBS


"AccessHelp" wrote in
message
...
I just changed the CRYear to Long and tried again. I am still
getting
errors:

"'Test2007.xls' cannot be accessed. The file may be read-only,
or
you
may
be
trying to access a read-only location. Or, the server the
document
is
stored on may not be responding."

Then:

"Run-time error '1004': Method 'SaveAs' of object '_Workbook'
failed"

Thanks.

"RB Smissaert" wrote:

Ah, that makes it more clear.
For starters change all the As Integer into As Long and try
again.

RBS


"AccessHelp" wrote in
message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have the
Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are
different
from
the
names that I posted. In my code, FName1 and FName2 are CRYear
and
FName,
respectively. What I need help with is located right below
"Dim"
statements.

If you have any suggestions or recommendation on my code,
please
feel
free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the CSV
files
are
saved
Dim CSVFName As String 'Original Name of CSV
file
Dim CSVAFName As String 'Additional Name for
CSV
File,
if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to create
additional
CSV files
Dim i As Integer 'Use to create
additional
CSV
files
Dim j As Integer 'Use to create CSV
sheet
Dim myFile As String 'Use to test for file
existence
to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file
before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new worksheet
for
CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" &
Right(Left(nName.Name,
7),
6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And
Right(Left(nName.Name,
5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) &
"-"
&
Val(Left(Right(nName.Name, 18), 2)) & "-" &
Val(Left(Right(nName.Name,
16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And
Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) &
"-"
&
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value = Left(Right(nName.Name,
14),
2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value = Left(Right(nName.Name,
12),
2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0",
"")
End If
NewSheet.Cells(j, 10).Value = "'" &
Left(Right(nName.Name,
6),
2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit

'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the existing
file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" &
CSVFName
&
"'" & _
" already created for this cost
report." &
_
" Would you like to overwrite the
existing
CSV
file?", _
Buttons:=vbYesNo + vbQuestion,
Title:="CSV
Macro")
'If 'Yes', overwrite it.
If PROMPT1 = vbYes Then
Sheets("CSV").Select
Sheets("CSV").Move
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs CSVDir & CSVFName,
xlCSVWindows, ,
,
True, _
False, xlNoChange
Application.DisplayAlerts = True
ActiveWindow.Close False
'If 'No', ask the user whether to create a new file
with
a
new
name.
ElseIf PROMPT1 = vbNo Then
PROMPT2 = MsgBox(Prompt:="Would you like to
create
an
addition CSV file?", _
Buttons:=vbYesNo + vbQuestion,
Title:="CSV
Macro")
'If 'Yes', additional CSV files are created
automatically.
If PROMPT2 = vbYes Then
InitName = CSVDir & CSVFName
CSVAFName = InitName
Do
myFile = Dir(CSVAFName)
If myFile < "" Then
i = i + 1
CSVAFName = Left(InitName,
Len(InitName) -
4) & i & ".csv"
End If

If i 2 Then
MsgBox "Sorry! There are
already "
&
i &
"
files created " & _
"in the directory C:\'.
"
&
Chr(13)
& "No " & _
"additional file is
created.",
vbInformation, _
"CSV Macro"
Application.DisplayAlerts =
False
Sheets("CSV").Delete
Application.DisplayAlerts =
True
Workbooks("MACRO to Create
CSV.xls").Close
False
End
End If
Loop While myFile < ""
Sheets("CSV").Select
Sheets("CSV").Move
ActiveWorkbook.SaveAs CSVAFName,
xlCSVWindows, ,
,
_

Use a Piece of Data for File Name
 

Basically, the CSV file is named using the FName and CRYear (e.g.
"ABC2007.csv"). Both FName and CRYear are referenced to the cells in the
input file (Workbook A). In addition to using the CRYear as a portion of
file name, I also use it for the directory (CSVDir = "C:\" & CRYear & "\") so
that the CSV files will be saved to the proper directory/year.

The problem is, for some reason, the code does not like the fact that CRYear
is being used for 2 purposes. If I change the code for CSVDir to "C:\2007\"
from '"C:\" & CRYear & "\"', it would work and I would not get errors.
However, I can not hard coded to 2007 because not all CSV files will be for
2007.

I got the errors on Line 790:

ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , , False, xlNoChange

On Line 790, "CSVDir" (based on the original code) is: "C:\" & CRYear & "\".

On Line 790, "CSVFName" (based on the original code) is: FName & CRYear &
".csv".

Again, thank you very much for your patience. I think we are almost there.
The problem is essential part of my code because we will produce 300 CSV
files and they need to be located properly. Please ask me if you need more
clarification.


"RB Smissaert" wrote:

Sorry, you lost me a bit now.
You will have to get to the essential part of the problem.

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you again very much for your patience.

First of all, Line 79 is "ActiveWorkbook.SaveAs CSVDir & CSVFName,
xlCSVWindows, , , , False, xlNoChange".

Secondly, I find where the problem is, and I do not know how to solve it.
The problem is on the "CRYear".

In addition to using "CRYear" in "CSVFName", I also use it in "CSVDir".
CSVDir is the directory where the CSV file will be saved, and CSVFName is
the
file name of a CSV file. The code that I posted has the CSVDir as "C:\".
Actually, it should be ""C:\" & CRYear". Due to business reason, we have
CSV
files for different years. The CSV files are named combination of
customer
name and the year for the CSV file (e.g. ABC2007.csv) and are saved in the
appropriate folder for the CSV year. The customer name and CSV year
should
come from the input file (Workbook A). Apparently, the VBA does not like
the
fact that CRYear is being used for various purpose.

I have tried to creating a new variable (DIRYear) and referencing the
DIRYear to a different cell. The code still does not work. I am still
getting the same error.

Is there a way around it? Thanks.

"RB Smissaert" wrote:

If I understand you correctly, I should put the following code before
"End
Sub"?

Yes, that is it.

Now what do you have at line 790 and what are the values of the variables
in
that line?

RBS

"AccessHelp" wrote in message
...
It indicates the error is on Line 790. If I understand you correctly,
I
should put the following code before "End Sub"?

ERROROUT:
Msgbox Err.Description,, "error at line " & Erl

Thanks.


"RB Smissaert" wrote:

Try close and re-start Excel.

RBS

"AccessHelp" wrote in message
...
Per your instructions, I downloaded MZ-Tools 3.0 for VBA and
followed
all
the
way to right-click in the procedures. When I right-click anywhere
in
the
procedures, am I supposed to see MZ-Tools as an option and clicked
on
it?
I
do not see it as an option.

Thanks.

"RB Smissaert" wrote:

OK, so you made some progress.
Now try this:

First download and install MZ-Tools:
http://www.mztools.com/index.aspx
This is very useful (and free) in any case.

Then at the top of that procedure (maybe after the Dim
declarations)
put
this:
On Error GoTo ERROROUT

Then at the very end of that procedure put this:

Exit Sub
ERROROUT:

Msgbox Err.Description,, "error at line " & Erl

Then right-click in the procedure and do:
MZ-Tools, Add line numbers

Now run again and see what shows and take it from there.


RBS


"AccessHelp" wrote in
message
...
I just changed the CRYear to Long and tried again. I am still
getting
errors:

"'Test2007.xls' cannot be accessed. The file may be read-only,
or
you
may
be
trying to access a read-only location. Or, the server the
document
is
stored on may not be responding."

Then:

"Run-time error '1004': Method 'SaveAs' of object '_Workbook'
failed"

Thanks.

"RB Smissaert" wrote:

Ah, that makes it more clear.
For starters change all the As Integer into As Long and try
again.

RBS


"AccessHelp" wrote in
message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have the
Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are
different
from
the
names that I posted. In my code, FName1 and FName2 are CRYear
and
FName,
respectively. What I need help with is located right below
"Dim"
statements.

If you have any suggestions or recommendation on my code,
please
feel
free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the CSV
files
are
saved
Dim CSVFName As String 'Original Name of CSV
file
Dim CSVAFName As String 'Additional Name for
CSV
File,
if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to create
additional
CSV files
Dim i As Integer 'Use to create
additional
CSV
files
Dim j As Integer 'Use to create CSV
sheet
Dim myFile As String 'Use to test for file
existence
to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file
before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new worksheet
for
CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" &
Right(Left(nName.Name,
7),
6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And
Right(Left(nName.Name,
5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) &
"-"
&
Val(Left(Right(nName.Name, 18), 2)) & "-" &
Val(Left(Right(nName.Name,
16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And
Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) &
"-"
&
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value = Left(Right(nName.Name,
14),
2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value = Left(Right(nName.Name,
12),
2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0",
"")
End If
NewSheet.Cells(j, 10).Value = "'" &
Left(Right(nName.Name,
6),
2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit

'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the existing
file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" &
CSVFName
&
"'" & _
" already created for this cost
report." &
_
" Would you like to overwrite the
existing
CSV
file?", _
Buttons:=vbYesNo + vbQuestion,
Title:="CSV
Macro")
'If 'Yes', overwrite it.

Use a Piece of Data for File Name
 

The problem is, for some reason, the code does not like the fact that
CRYear
is being used for 2 purposes

That shouldn't be any problem.

The essential question is what file or folder string is produced when you
get an error?
So, what you do is this:
In the VBE do View, Immediate window

Then on a line just before your error line put:
Debug.Print "C:\" & CRYear & "\"

Come to look at it now you have 2 double quotes before C and after \
This should be only one. That might be it.

RBS


"AccessHelp" wrote in message
...
Basically, the CSV file is named using the FName and CRYear (e.g.
"ABC2007.csv"). Both FName and CRYear are referenced to the cells in the
input file (Workbook A). In addition to using the CRYear as a portion of
file name, I also use it for the directory (CSVDir = "C:\" & CRYear & "\")
so
that the CSV files will be saved to the proper directory/year.

The problem is, for some reason, the code does not like the fact that
CRYear
is being used for 2 purposes. If I change the code for CSVDir to
"C:\2007\"
from '"C:\" & CRYear & "\"', it would work and I would not get errors.
However, I can not hard coded to 2007 because not all CSV files will be
for
2007.

I got the errors on Line 790:

ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , , False,
xlNoChange

On Line 790, "CSVDir" (based on the original code) is: "C:\" & CRYear &
"\".

On Line 790, "CSVFName" (based on the original code) is: FName & CRYear &
".csv".

Again, thank you very much for your patience. I think we are almost
there.
The problem is essential part of my code because we will produce 300 CSV
files and they need to be located properly. Please ask me if you need
more
clarification.


"RB Smissaert" wrote:

Sorry, you lost me a bit now.
You will have to get to the essential part of the problem.

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you again very much for your patience.

First of all, Line 79 is "ActiveWorkbook.SaveAs CSVDir & CSVFName,
xlCSVWindows, , , , False, xlNoChange".

Secondly, I find where the problem is, and I do not know how to solve
it.
The problem is on the "CRYear".

In addition to using "CRYear" in "CSVFName", I also use it in "CSVDir".
CSVDir is the directory where the CSV file will be saved, and CSVFName
is
the
file name of a CSV file. The code that I posted has the CSVDir as
"C:\".
Actually, it should be ""C:\" & CRYear". Due to business reason, we
have
CSV
files for different years. The CSV files are named combination of
customer
name and the year for the CSV file (e.g. ABC2007.csv) and are saved in
the
appropriate folder for the CSV year. The customer name and CSV year
should
come from the input file (Workbook A). Apparently, the VBA does not
like
the
fact that CRYear is being used for various purpose.

I have tried to creating a new variable (DIRYear) and referencing the
DIRYear to a different cell. The code still does not work. I am still
getting the same error.

Is there a way around it? Thanks.

"RB Smissaert" wrote:

If I understand you correctly, I should put the following code
before
"End
Sub"?

Yes, that is it.

Now what do you have at line 790 and what are the values of the
variables
in
that line?

RBS

"AccessHelp" wrote in message
...
It indicates the error is on Line 790. If I understand you
correctly,
I
should put the following code before "End Sub"?

ERROROUT:
Msgbox Err.Description,, "error at line " & Erl

Thanks.


"RB Smissaert" wrote:

Try close and re-start Excel.

RBS

"AccessHelp" wrote in
message
...
Per your instructions, I downloaded MZ-Tools 3.0 for VBA and
followed
all
the
way to right-click in the procedures. When I right-click
anywhere
in
the
procedures, am I supposed to see MZ-Tools as an option and
clicked
on
it?
I
do not see it as an option.

Thanks.

"RB Smissaert" wrote:

OK, so you made some progress.
Now try this:

First download and install MZ-Tools:
http://www.mztools.com/index.aspx
This is very useful (and free) in any case.

Then at the top of that procedure (maybe after the Dim
declarations)
put
this:
On Error GoTo ERROROUT

Then at the very end of that procedure put this:

Exit Sub
ERROROUT:

Msgbox Err.Description,, "error at line " & Erl

Then right-click in the procedure and do:
MZ-Tools, Add line numbers

Now run again and see what shows and take it from there.


RBS


"AccessHelp" wrote in
message
...
I just changed the CRYear to Long and tried again. I am still
getting
errors:

"'Test2007.xls' cannot be accessed. The file may be
read-only,
or
you
may
be
trying to access a read-only location. Or, the server the
document
is
stored on may not be responding."

Then:

"Run-time error '1004': Method 'SaveAs' of object '_Workbook'
failed"

Thanks.

"RB Smissaert" wrote:

Ah, that makes it more clear.
For starters change all the As Integer into As Long and try
again.

RBS


"AccessHelp" wrote in
message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have the
Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are
different
from
the
names that I posted. In my code, FName1 and FName2 are
CRYear
and
FName,
respectively. What I need help with is located right below
"Dim"
statements.

If you have any suggestions or recommendation on my code,
please
feel
free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the
CSV
files
are
saved
Dim CSVFName As String 'Original Name of
CSV
file
Dim CSVAFName As String 'Additional Name for
CSV
File,
if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to
create
additional
CSV files
Dim i As Integer 'Use to create
additional
CSV
files
Dim j As Integer 'Use to create CSV
sheet
Dim myFile As String 'Use to test for
file
existence
to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file
before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new
worksheet
for
CSV
NewSheet.Name = "CSV"
j = 1
'Create a CSV sheet
For Each nName In ActiveWorkbook.Names
NewSheet.Cells(j, 1).Value = "'" &
Right(Left(nName.Name,
7),
6)
'Data
NewSheet.Cells(j, 3).Value = nName.RefersTo
If Right(Left(nName.Name, 7), 2) < 0 And
Right(Left(nName.Name,
5),
2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
&
"-"
&
Val(Left(Right(nName.Name, 18), 2)) & "-" &
Val(Left(Right(nName.Name,
16),
2))
ElseIf Right(Left(nName.Name, 7), 2) = 0 And
Right(Left(nName.Name,
5), 2) < 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
&
"-"
&
Val(Left(Right(nName.Name, 18), 2))
ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
End If
If Left(Right(nName.Name, 14), 2) < 0 Then
NewSheet.Cells(j, 7).Value =
Left(Right(nName.Name,
14),
2)
End If
If Left(Right(nName.Name, 12), 2) < 0 Then
NewSheet.Cells(j, 8).Value =
Left(Right(nName.Name,
12),
2)
End If
If Left(Right(nName.Name, 10), 4) < 0 Then
NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0",
"")
End If
NewSheet.Cells(j, 10).Value = "'" &
Left(Right(nName.Name,
6),
2) &
"." & Left(Right(nName.Name, 4), 2)
NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
j = j + 1
Next
NewSheet.Columns("A:K").AutoFit

'CREATE A CSV FILE
'Check to see the CSV file is already exist.
'If exist, ask the user whether to overwrite the
existing
file.
If Len(Dir(CSVDir & CSVFName)) 0 Then
PROMPT1 = MsgBox(Prompt:="There is a CSV file " &
"'" &
CSVFName
&
"'" & _
" already created for this cost
report." &
_
" Would you like to overwrite the
existing
CSV
file?", _
Buttons:=vbYesNo + vbQuestion,
Title:="CSV
Macro")
'If 'Yes', overwrite it.

Use a Piece of Data for File Name
 

I don't think the problem is due to the double quotation because there are 3
pieces to the CSVDir ("C:\", CRYear and "\"). If it is a problem, which one
should I remove?

I did per your instructions, and I don't know what I should look for in the
Immediate window. Below is what I got from Immediate when I did the "Run to
Cursor" on the Debug.Print "C:\" & CRYear & "\" line.

[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs]
[PickPlatform] <
[PickPlatform]
[VerifyOpen] <
[VerifyOpen] 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs]
[auto_open] True

True

Then I got an error "Subscript Out of Range, and it indicated to line where
I have

CRYear = Year(.cells(2))

Thanks.

"RB Smissaert" wrote:

The problem is, for some reason, the code does not like the fact that
CRYear
is being used for 2 purposes

That shouldn't be any problem.

The essential question is what file or folder string is produced when you
get an error?
So, what you do is this:
In the VBE do View, Immediate window

Then on a line just before your error line put:
Debug.Print "C:\" & CRYear & "\"

Come to look at it now you have 2 double quotes before C and after \
This should be only one. That might be it.

RBS


"AccessHelp" wrote in message
...
Basically, the CSV file is named using the FName and CRYear (e.g.
"ABC2007.csv"). Both FName and CRYear are referenced to the cells in the
input file (Workbook A). In addition to using the CRYear as a portion of
file name, I also use it for the directory (CSVDir = "C:\" & CRYear & "\")
so
that the CSV files will be saved to the proper directory/year.

The problem is, for some reason, the code does not like the fact that
CRYear
is being used for 2 purposes. If I change the code for CSVDir to
"C:\2007\"
from '"C:\" & CRYear & "\"', it would work and I would not get errors.
However, I can not hard coded to 2007 because not all CSV files will be
for
2007.

I got the errors on Line 790:

ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , , False,
xlNoChange

On Line 790, "CSVDir" (based on the original code) is: "C:\" & CRYear &
"\".

On Line 790, "CSVFName" (based on the original code) is: FName & CRYear &
".csv".

Again, thank you very much for your patience. I think we are almost
there.
The problem is essential part of my code because we will produce 300 CSV
files and they need to be located properly. Please ask me if you need
more
clarification.


"RB Smissaert" wrote:

Sorry, you lost me a bit now.
You will have to get to the essential part of the problem.

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you again very much for your patience.

First of all, Line 79 is "ActiveWorkbook.SaveAs CSVDir & CSVFName,
xlCSVWindows, , , , False, xlNoChange".

Secondly, I find where the problem is, and I do not know how to solve
it.
The problem is on the "CRYear".

In addition to using "CRYear" in "CSVFName", I also use it in "CSVDir".
CSVDir is the directory where the CSV file will be saved, and CSVFName
is
the
file name of a CSV file. The code that I posted has the CSVDir as
"C:\".
Actually, it should be ""C:\" & CRYear". Due to business reason, we
have
CSV
files for different years. The CSV files are named combination of
customer
name and the year for the CSV file (e.g. ABC2007.csv) and are saved in
the
appropriate folder for the CSV year. The customer name and CSV year
should
come from the input file (Workbook A). Apparently, the VBA does not
like
the
fact that CRYear is being used for various purpose.

I have tried to creating a new variable (DIRYear) and referencing the
DIRYear to a different cell. The code still does not work. I am still
getting the same error.

Is there a way around it? Thanks.

"RB Smissaert" wrote:

If I understand you correctly, I should put the following code
before
"End
Sub"?

Yes, that is it.

Now what do you have at line 790 and what are the values of the
variables
in
that line?

RBS

"AccessHelp" wrote in message
...
It indicates the error is on Line 790. If I understand you
correctly,
I
should put the following code before "End Sub"?

ERROROUT:
Msgbox Err.Description,, "error at line " & Erl

Thanks.


"RB Smissaert" wrote:

Try close and re-start Excel.

RBS

"AccessHelp" wrote in
message
...
Per your instructions, I downloaded MZ-Tools 3.0 for VBA and
followed
all
the
way to right-click in the procedures. When I right-click
anywhere
in
the
procedures, am I supposed to see MZ-Tools as an option and
clicked
on
it?
I
do not see it as an option.

Thanks.

"RB Smissaert" wrote:

OK, so you made some progress.
Now try this:

First download and install MZ-Tools:
http://www.mztools.com/index.aspx
This is very useful (and free) in any case.

Then at the top of that procedure (maybe after the Dim
declarations)
put
this:
On Error GoTo ERROROUT

Then at the very end of that procedure put this:

Exit Sub
ERROROUT:

Msgbox Err.Description,, "error at line " & Erl

Then right-click in the procedure and do:
MZ-Tools, Add line numbers

Now run again and see what shows and take it from there.


RBS


"AccessHelp" wrote in
message
...
I just changed the CRYear to Long and tried again. I am still
getting
errors:

"'Test2007.xls' cannot be accessed. The file may be
read-only,
or
you
may
be
trying to access a read-only location. Or, the server the
document
is
stored on may not be responding."

Then:

"Run-time error '1004': Method 'SaveAs' of object '_Workbook'
failed"

Thanks.

"RB Smissaert" wrote:

Ah, that makes it more clear.
For starters change all the As Integer into As Long and try
again.

RBS


"AccessHelp" wrote in
message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have the
Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are
different
from
the
names that I posted. In my code, FName1 and FName2 are
CRYear
and
FName,
respectively. What I need help with is located right below
"Dim"
statements.

If you have any suggestions or recommendation on my code,
please
feel
free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the
CSV
files
are
saved
Dim CSVFName As String 'Original Name of
CSV
file
Dim CSVAFName As String 'Additional Name for
CSV
File,
if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to
create
additional
CSV files
Dim i As Integer 'Use to create
additional
CSV
files
Dim j As Integer 'Use to create CSV
sheet
Dim myFile As String 'Use to test for
file
existence
to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input file
before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new
worksheet

Use a Piece of Data for File Name
 

If it is a problem, which one should I remove?

Remove the very first one and the very last one.
You are building a string to make a folder path. This path has 3 bits:
"C:\"
your variable: CRYear
"\"
So you get "C:\" & CRYear & "\"
Why do you want an extra double quote before "C:\" or after "\" ?

"Run to Cursor" on the Debug.Print "C:\" & CRYear & "\" line.

Not sure what you are doing here.
Debug.Print should dump the result of "C:\" & CRYear & "\"
and I don't see that.


RBS


"AccessHelp" wrote in message
...
I don't think the problem is due to the double quotation because there are
3
pieces to the CSVDir ("C:\", CRYear and "\"). If it is a problem, which
one
should I remove?

I did per your instructions, and I don't know what I should look for in
the
Immediate window. Below is what I got from Immediate when I did the "Run
to
Cursor" on the Debug.Print "C:\" & CRYear & "\" line.

[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs]
[PickPlatform] <
[PickPlatform]
[VerifyOpen] <
[VerifyOpen] 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs]
[auto_open] True

True

Then I got an error "Subscript Out of Range, and it indicated to line
where
I have

CRYear = Year(.cells(2))

Thanks.

"RB Smissaert" wrote:

The problem is, for some reason, the code does not like the fact that
CRYear
is being used for 2 purposes

That shouldn't be any problem.

The essential question is what file or folder string is produced when you
get an error?
So, what you do is this:
In the VBE do View, Immediate window

Then on a line just before your error line put:
Debug.Print "C:\" & CRYear & "\"

Come to look at it now you have 2 double quotes before C and after \
This should be only one. That might be it.

RBS


"AccessHelp" wrote in message
...
Basically, the CSV file is named using the FName and CRYear (e.g.
"ABC2007.csv"). Both FName and CRYear are referenced to the cells in
the
input file (Workbook A). In addition to using the CRYear as a portion
of
file name, I also use it for the directory (CSVDir = "C:\" & CRYear &
"\")
so
that the CSV files will be saved to the proper directory/year.

The problem is, for some reason, the code does not like the fact that
CRYear
is being used for 2 purposes. If I change the code for CSVDir to
"C:\2007\"
from '"C:\" & CRYear & "\"', it would work and I would not get errors.
However, I can not hard coded to 2007 because not all CSV files will be
for
2007.

I got the errors on Line 790:

ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , , False,
xlNoChange

On Line 790, "CSVDir" (based on the original code) is: "C:\" & CRYear &
"\".

On Line 790, "CSVFName" (based on the original code) is: FName & CRYear
&
".csv".

Again, thank you very much for your patience. I think we are almost
there.
The problem is essential part of my code because we will produce 300
CSV
files and they need to be located properly. Please ask me if you need
more
clarification.


"RB Smissaert" wrote:

Sorry, you lost me a bit now.
You will have to get to the essential part of the problem.

RBS


"AccessHelp" wrote in message
...
Hi RBS,

Thank you again very much for your patience.

First of all, Line 79 is "ActiveWorkbook.SaveAs CSVDir & CSVFName,
xlCSVWindows, , , , False, xlNoChange".

Secondly, I find where the problem is, and I do not know how to
solve
it.
The problem is on the "CRYear".

In addition to using "CRYear" in "CSVFName", I also use it in
"CSVDir".
CSVDir is the directory where the CSV file will be saved, and
CSVFName
is
the
file name of a CSV file. The code that I posted has the CSVDir as
"C:\".
Actually, it should be ""C:\" & CRYear". Due to business reason, we
have
CSV
files for different years. The CSV files are named combination of
customer
name and the year for the CSV file (e.g. ABC2007.csv) and are saved
in
the
appropriate folder for the CSV year. The customer name and CSV year
should
come from the input file (Workbook A). Apparently, the VBA does not
like
the
fact that CRYear is being used for various purpose.

I have tried to creating a new variable (DIRYear) and referencing
the
DIRYear to a different cell. The code still does not work. I am
still
getting the same error.

Is there a way around it? Thanks.

"RB Smissaert" wrote:

If I understand you correctly, I should put the following code
before
"End
Sub"?

Yes, that is it.

Now what do you have at line 790 and what are the values of the
variables
in
that line?

RBS

"AccessHelp" wrote in
message
...
It indicates the error is on Line 790. If I understand you
correctly,
I
should put the following code before "End Sub"?

ERROROUT:
Msgbox Err.Description,, "error at line " & Erl

Thanks.


"RB Smissaert" wrote:

Try close and re-start Excel.

RBS

"AccessHelp" wrote in
message
...
Per your instructions, I downloaded MZ-Tools 3.0 for VBA and
followed
all
the
way to right-click in the procedures. When I right-click
anywhere
in
the
procedures, am I supposed to see MZ-Tools as an option and
clicked
on
it?
I
do not see it as an option.

Thanks.

"RB Smissaert" wrote:

OK, so you made some progress.
Now try this:

First download and install MZ-Tools:
http://www.mztools.com/index.aspx
This is very useful (and free) in any case.

Then at the top of that procedure (maybe after the Dim
declarations)
put
this:
On Error GoTo ERROROUT

Then at the very end of that procedure put this:

Exit Sub
ERROROUT:

Msgbox Err.Description,, "error at line " & Erl

Then right-click in the procedure and do:
MZ-Tools, Add line numbers

Now run again and see what shows and take it from there.


RBS


"AccessHelp" wrote in
message
...
I just changed the CRYear to Long and tried again. I am
still
getting
errors:

"'Test2007.xls' cannot be accessed. The file may be
read-only,
or
you
may
be
trying to access a read-only location. Or, the server the
document
is
stored on may not be responding."

Then:

"Run-time error '1004': Method 'SaveAs' of object
'_Workbook'
failed"

Thanks.

"RB Smissaert" wrote:

Ah, that makes it more clear.
For starters change all the As Integer into As Long and
try
again.

RBS


"AccessHelp" wrote
in
message
...
Hi RBS,

Thank you very much for your patience. Yes, I do have
the
Sheets(1)
opened.
In fact, it is part of Workbook A.

Below is my code. Please note the names in my code are
different
from
the
names that I posted. In my code, FName1 and FName2 are
CRYear
and
FName,
respectively. What I need help with is located right
below
"Dim"
statements.

If you have any suggestions or recommendation on my
code,
please
feel
free
to share with me. Thanks.

Sub CreateCSV()
Dim CSVDir As String 'Directory where the
CSV
files
are
saved
Dim CSVFName As String 'Original Name of
CSV
file
Dim CSVAFName As String 'Additional Name
for
CSV
File,
if
one exists
Dim CRYear As Integer 'Year
Dim FName As String €˜Portion of CSV File Name
Dim InitName As String 'Placeholder to
create
additional
CSV files
Dim i As Integer 'Use to create
additional
CSV
files
Dim j As Integer 'Use to create
CSV
sheet
Dim myFile As String 'Use to test for
file
existence
to
create additional CSV files
With Sheets(€œSheets3€�)
CRYear = Year(.Cells(2))
End With
With Sheets(€œSheets2€�)
FName=.cells(2)
End With
CSVFName = FName & CRYear & ".csv"
CSVDir = "C:\"

ActiveWorkbook.Save 'Save the input
file
before
creating
a CSV file.

'CREATE A CSV SHEET
Set NewSheet = Worksheets.Add 'Create a new
worksheet