Use a Piece of Data for File Name

[Guest]

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.

 

[RB Smissaert]

Try this:

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


RBS

 

[Guest]

Hi RBS,

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

Thanks.

 

[dan dungan]

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

Dan Dungan

 

[RB Smissaert]

Is there a worksheet open when that code runs?

RBS

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

Good morning Dan,

I am using Windows XP and Office 2003. Thanks.

 

[RB Smissaert]

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

RBS

 

[Guest]

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.

 

[Guest]

I am sorry which worksheet are you referring to?

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

RBS

 

[RB Smissaert]

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

RBS

 

[Guest]

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]

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

RBS

 

[Guest]

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]

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

 

[Guest]

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]

Try close and re-start Excel.

RBS

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.

 

[Guest]

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]

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