 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / April 2008Save 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.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 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 > Hello all, > I'm looking to save a file as a text delimted file. I have a bit of code [quoted text clipped - 10 lines] > > Thanks Erorr: Variable not defined Should MyPath be somthing like = Dim Mypath as a string and MyFileName too? > Hi > [quoted text clipped - 21 lines] > > > > Thanks Hi Yes both variables should be defined as strings. Regards, Per > Erorr: Variable not defined > [quoted text clipped - 28 lines] >> > >> > Thanks 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 > Hi > [quoted text clipped - 35 lines] > >> > > >> > Thanks 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. > Hi > [quoted text clipped - 35 lines] > >> > > >> > Thanks 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 > Sub Copy_Paste() > ' [quoted text clipped - 304 lines] >> >> > >> >> > Thanks 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 > Hi > [quoted text clipped - 293 lines] > > MyPath = "S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\" > > MyFileName = "d" & Sheets("INPUT_A").Range("A1").Value & "MR" ActiveWorkbook.SaveAs Filename:= _ "S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\d" & range("A1").value & "mr.txt", FileFormat:=xlText, _ CreateBackup:=False > Hello all, > I'm looking to save a file as a text delimted file. I have a bit of code [quoted text clipped - 10 lines] > > Thanks A1 is on another tab. Thanks. > ActiveWorkbook.SaveAs Filename:= _ > "S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\d" & range("A1").value [quoted text clipped - 15 lines] > > > > Thanks 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 > Hello all, > I'm looking to save a file as a text delimted file. I have a bit of code [quoted text clipped - 10 lines] > > Thanks 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 > You can probably build the filename using something like: > [quoted text clipped - 25 lines] > > > > Thanks |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.