Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Save file as...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 304
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,101
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
Ken Ken is offline
external usenet poster
  
Posts: 207
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 304
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 304
Default 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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 304
Default 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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default 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



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 304
Default 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




Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 304
Default 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




Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default 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





Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 304
Default 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"

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
when i save xls file, debug script is running and canno't save fil Imtiaz Excel Discussion (Misc queries) 1 July 16th 05 03:47 PM
Excell2003 (SP-1) File > Save and File > Save As.. grayed out Joe Murphy Excel Discussion (Misc queries) 0 March 9th 05 10:00 PM
Excel marcos firing on file save as but not file save Andy Excel Programming 1 August 3rd 04 10:34 AM
Save File to Another Directory, but not change Users File Save location Mike Knight Excel Programming 1 May 28th 04 09:06 PM
Save As - Multiple Sheets fails to save as text file Ravee Srinivasan Excel Programming 2 November 10th 03 04:05 PM


All times are GMT +1. The time now is 02:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement