Save file as...

P

pgarcia

Hello all,
I'm looking to save a file as a text delimted file. I have a bit of code
that reads like so:
ActiveWorkbook.SaveAs Filename:= _
"S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\dMR.txt",
FileFormat:=xlText, _
CreateBackup:=False

However, I need the file to read "d1525mr.txt. So, I need a "d" then get
1525 number from cell A1, tab INPUT_A and finaly at "MR".

Do able?

Thanks
 
P

Per Jessen

Hi

MyPath = "S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\"
MyFileName = "d" & Sheets("INPUT_A").Range("A1").Value & "MR"

ActiveWorkbook.SaveAs Filename:=MyPath & MyFileName, _
FileFormat:=xlText, CreateBackup:=False

Regards,
Per
 
M

Mike

ActiveWorkbook.SaveAs Filename:= _
"S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\d" & range("A1").value
& "mr.txt", FileFormat:=xlText, _
CreateBackup:=False
 
K

Ken

You can probably build the filename using something like:

dim fname as string

fname="S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\d" &
worksheets("INPUT_A').range("a1").value & "MR.txt", "

ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlText, _
CreateBackup:=False

Good luck.

Ken
Norfolk, Va
 
P

pgarcia

A1 is on another tab. Thanks.

Mike said:
ActiveWorkbook.SaveAs Filename:= _
"S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\d" & range("A1").value
& "mr.txt", FileFormat:=xlText, _
CreateBackup:=False
Click to expand...
 
P

pgarcia

Erorr: Variable not defined

Should MyPath be somthing like = Dim Mypath as a string
and MyFileName too?
 
P

pgarcia

It did not pick up the data in cell A1 sheet INPUT_A. It sent the file but
with file name as dMR.txt.

Aslo, I had to twick it a bit as I was getting erorrs.

Dim fname As String

fname = "S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\d" & _
Worksheets("INPUT_A").Range("a1").Value & "MR.txt"

ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlText, _
CreateBackup:=False
 
P

pgarcia

Great thanks.

Dim MyPath as String
Dim MyFileName as String

MyPath = "S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\"
MyFileName = "d" & Sheets("INPUT_A").Range("A1").Value & "MR"

ActiveWorkbook.SaveAs Filename:=MyPath & MyFileName, _
FileFormat:=xlText, CreateBackup:=False
 
P

pgarcia

Sub Copy_Paste()
'
' Copy_Paste Macro
' Macro recorded 4/8/2008 by autpbg1
'

'
Application.ScreenUpdating = False

Dim MyPath As String
Dim MyFileName As String

Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("S- Overnight Rates ").Select
Range("X4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("X4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("4900_B").Select
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Call Loop_Example
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S- Overnight Rates ").Select
Range("Y4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("Y4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("4900_B").Select
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Call Loop_Example
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S- Overnight Rates ").Select
Range("Z4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("Z4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("4900_B").Select
Range("K2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Call Loop_Example
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S- Overnight Rates ").Select
Range("AA4:AA5").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("AA4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("1900_D").Select
Range("c2:c101").Select
'Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
'ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S- Overnight Rates ").Select
Range("AB4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("AB4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S- Overnight Rates ").Select
Range("AC4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("AC4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S- Overnight Rates ").Select
Range("AD4:AD5").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("AD4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select

MyPath = "S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\"
MyFileName = "d" & Sheets("INPUT_A").Range("A1").Value & "MR"

ActiveWorkbook.SaveAs Filename:=MyPath & MyFileName, _
FileFormat:=xlText, CreateBackup:=False


End Sub
Ok, there is some thing strange happing here. After I got you reply, I
update the code, but did not save it. I wanted to test it first. It ran and
worked, now I'm trying to update the work sheet and save it, but it does not
work. Can you look at my code and mybe see where the problem is. I had to do
the workbook all over agian, but I guess that may remove the bug. There is no
error, it's just not picking up the data in cell A1 sheet INPUT_A.
 
P

Per Jessen

Hi

First, always test on a copy of your workbook.

Are you sure that cell A1 in sheet INPUT_A holds any data?

At the start of your macro you delete Column A of activesheet. I would
select the desired sheet by code, otherwise the wrong sheet may be active
when the macro is started...

Your macro is very complex and this very difficult to read.

Below is part of your code which reworked a bit.

Columns("A:A").Delete Shift:=xlToLeft
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

Sheets("S- Overnight Rates ").Select
Range("X4", Range("X4").End(xlDown)).Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast

Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").End(xlDown).Offset(1, 0).Select

Sheets("S - 2 Day Rates").Select
Range("X4").Select
Range("X4", Range("X4").End(xlDown)).Copy
Application.CutCopyMode = False
ActiveWindow.ScrollWorkbookTabs Position:=xlLast

Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select

Regards,
Per
 
P

pgarcia

What a rooky move. Thanks for ponting that out. I have corrected the code and
it runs great. Yes, I still a novis with VB, so some times my code is long
and messy. Thanks for the pointers.
How would I go about renaming the active worksheet? When the code runs, it
renames the tab.

Thanks
 

Ask a Question

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

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

Ask a Question

Similar Threads

Save Macro with date in name 1
Create Tab delimited file with no quotes in Currency fields. 0
SaveAs *.txt - Outputting with Text quantifiers 4
Simplify save code 11
Hiding an Excel file using VBA 1
45 seconds to save a file, why so long? 1
VBA SaveAs function generates blank CSV file 4
Error with currunc 3

Top