Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save As in a macro (XP)

    I am recording a macro that imports a .txt file, does a find/replace, and then saves it as a .xls file.

    Everything works except the save as part. The macro recorder does not write the code for doing as save as. Can someone tell me what code could be used to do a Save As, change the type to .xls, and specify a path. Thanks in advance...

    Kent

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As in a macro (XP)

    <!post=This,263029>This<!/post> thread covers the subject well, see <post#=264065>post 264065</post#> on how to specify that the file will be saved as an Excel Workbook.. Post back if you still need help.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As in a macro (XP)

    Where do you want to save the workbook to? Is this a one off, is anyone else going to use this?

    Dependant upon where you wish to save the book and if different folks are going to use it and if they are running different flavors of Windows, you may need to do an API call to determine the Win flavor, if, for example, you want to save to the desktop.

  4. #4
    New Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As in a macro (XP)

    It will be run only on one PC...It will import a file from a given directory, yet to be determined, and then save back to this same directory.

    Here is the scenerio.
    We have a spam rule program that will spit out a .txt file. We need to create reports from it. Our thought was to create a macro in Excel that imports the .txt file, then does a global find/replace, and then saves it as a .xls file. That file will be used by someone other than myself.

    I can't get the macro recorder to put the code in for Save As.

    Kent

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As in a macro (XP)

    It will only give you the

    ActiveWorkbook.SaveAs "...

    Method, which isn't very flexible. See the thread I referred you to, look at how GetSaveAsFilename code works.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    New Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As in a macro (XP)

    I can not even get it to give me ActiveWorkbookSaveAs...

    here is my code:
    ChDir "Cocuments and SettingskdecookDesktop"
    Workbooks.OpenText Filename:= _
    "Cocuments and SettingskdecookDesktopspamrule.txt", Origin:=437, _
    StartRow:=2, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=True, OtherChar:="<", FieldInfo:=Array(Array(1, 9 _
    ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
    Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
    , 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
    Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1)), TrailingMinusNumbers:=True
    Cells.Replace What:=">", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    End Sub

    I am not at all versed in the world of VB (obvious as a bear in a china store, I'm sure) I didn't follow the threads you referenced very well. My mind just does not work when looking at code <img src=/S/confused.gif border=0 alt=confused width=15 height=20> . Any help with what code to put where is GREATLY appreciated. Thanks,

    Kent

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As in a macro (XP)

    Before the End Sub, add this:

    Dim vFileN As Variant ' by convention this line should be at the beginning of your code
    vFileN = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls",_
    , "Please enter a filename")
    If TypeName(vFileN) = "Boolean" or vFileN = "" Then
    Beep
    MsgBox "File Not Saved", vbExclamation
    Exit Sub
    End If
    ThisWorkbook.SaveAs vFileN
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As in a macro (XP)

    I pasted it from the post. I get a syntax error at the _ in the 'vFileN = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls",_ , "Please enter a filename")' Then I tried putting in a filename where the _ was, and it got rid of the compiling error, but then when I run the macro, it:

    1. Runs as expected for the first part
    2. Prompts me for a file name and has the file type correct
    3. However, the file that got created is a blank file, not the .txt file as a .xls file

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As in a macro (XP)

    The " _" is a line wrap so the coder doesn't have to scroll to read the entire line: it must be preceded by a space, which may not be obvious from my Lounge post.

    I'm not having a problem in my testing, though I can't exactly reproduce your environment. Can you post the entire code as it now stands?
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    New Lounger
    Join Date
    Jan 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As in a macro (XP)

    Sub SpamRuleWithSaveAs()
    '
    ' SpamRuleWithSaveAs Macro
    ' Macro recorded 7/30/2003 by IT
    '

    '
    Dim vFileN As Variant
    ChDir "Cocuments and SettingskdecookDesktop"
    Workbooks.OpenText Filename:= _
    "Cocuments and SettingskdecookDesktopspamrule.txt", Origin:=437, _
    StartRow:=2, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=True, OtherChar:="<", FieldInfo:=Array(Array(1, 9 _
    ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
    Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15 _
    , 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), _
    Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1)), TrailingMinusNumbers:=True
    Cells.Replace What:=">", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    vFileN = Application.GetSaveAsFilename(, "Microsoft Excel Workbook (*.xls), *.xls", _
    , "Please enter a filename")

    If TypeName(vFileN) = "Boolean" Or vFileN = "" Then
    Beep
    MsgBox "File Not Saved", vbExclamation
    Exit Sub
    End If
    ThisWorkbook.SaveAs vFileN

    End Sub


    Thanks for your help...Kent

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As in a macro (XP)

    <P ID="edit" class=small>(Edited by JohnBF on 01-Aug-03 16:25. One code error fixed.)</P>Kent, I can't see what is wrong with your code. Here is my simplified test version from Excel 2000, with simple changes to make sure that the correct file is saved. Since I don't have your same file or Excel version, substitute your code in the blue section:

    Sub SpamTest()
    Dim vFileN As Variant
    Dim wbNewBook As Workbook

    <font color=blue> Workbooks.OpenText Filename:="U:XLSLIBImportText test.txt"</font color=blue>

    Set wbNewBook = Workbooks(Workbooks.Count)
    With wbNewBook
    .Worksheets(1).Cells.Replace What:=">", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False , SearchFormat:=False, ReplaceFormat:=False
    vFileN = Application.GetSaveAsFilename("", "Microsoft Excel Workbook (*.xls), *.xls", _
    , "Please enter a filename")
    If TypeName(vFileN) = "Boolean" Or vFileN = "" Then
    Beep
    MsgBox "File Not Saved", vbExclamation
    Exit Sub
    End If
    .SaveAs vFileN
    End With
    Set wbNewBook = Nothing
    End Sub

    The way the code works, the text is not saved in the Workbook that runs the macro, but in a separate Workbook, OK?
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •