|
Save file as...
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.t xt", 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 |
Save file as...
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 "pgarcia" skrev i meddelelsen ... 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.t xt", 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 |
Save file as...
ActiveWorkbook.SaveAs Filename:= _
"S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\d " & range("A1").value & "mr.txt", FileFormat:=xlText, _ CreateBackup:=False "pgarcia" wrote: 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.t xt", 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 |
Save file as...
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 On Apr 17, 4:44*pm, pgarcia wrote: 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.t xt", 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 |
Save file as...
A1 is on another tab. Thanks.
"Mike" wrote: ActiveWorkbook.SaveAs Filename:= _ "S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\d " & range("A1").value & "mr.txt", FileFormat:=xlText, _ CreateBackup:=False "pgarcia" wrote: 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.t xt", 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 |
Save file as...
Erorr: Variable not defined
Should MyPath be somthing like = Dim Mypath as a string and MyFileName too? "Per Jessen" wrote: 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 "pgarcia" skrev i meddelelsen ... 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.t xt", 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 |
Save file as...
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 "Ken" wrote: 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 On Apr 17, 4:44 pm, pgarcia wrote: 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.t xt", 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 |
Save file as...
Hi
Yes both variables should be defined as strings. Regards, Per pgarcia" skrev i meddelelsen ... Erorr: Variable not defined Should MyPath be somthing like = Dim Mypath as a string and MyFileName too? "Per Jessen" wrote: 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 "pgarcia" skrev i meddelelsen ... 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.t xt", 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 |
Save file as...
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 "Per Jessen" wrote: Hi Yes both variables should be defined as strings. Regards, Per pgarcia" skrev i meddelelsen ... Erorr: Variable not defined Should MyPath be somthing like = Dim Mypath as a string and MyFileName too? "Per Jessen" wrote: 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 "pgarcia" skrev i meddelelsen ... 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.t xt", 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 |
Save file as...
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. "Per Jessen" wrote: Hi Yes both variables should be defined as strings. Regards, Per pgarcia" skrev i meddelelsen ... Erorr: Variable not defined Should MyPath be somthing like = Dim Mypath as a string and MyFileName too? "Per Jessen" wrote: 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 "pgarcia" skrev i meddelelsen ... 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.t xt", 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 |
Save file as...
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 "pgarcia" skrev i meddelelsen ... 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. "Per Jessen" wrote: Hi Yes both variables should be defined as strings. Regards, Per pgarcia" skrev i meddelelsen ... Erorr: Variable not defined Should MyPath be somthing like = Dim Mypath as a string and MyFileName too? "Per Jessen" wrote: 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 "pgarcia" skrev i meddelelsen ... 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.t xt", 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 |
Save file as...
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 "Per Jessen" wrote: 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 "pgarcia" skrev i meddelelsen ... 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" |
| All times are GMT +1. The time now is 07:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com