Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Multiple files open in Excel (Excel xp, Win2000)

    I have an excel file, main.xls, that i'm writing a macro for. I have it opening a comma delimited file called text.cda
    Workbooks.OpenText Filename:="text.cda"...

    I then want to save the cda file as an excel file but don't know how to make the cda "active" or "selected". The activeworkbook is main.xls and i can't get the focus to switch to the text.cda that just opened in excel. Sorry if i'm not making sense. Please help. thanks

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multiple files open in Excel (Excel xp, Win2000)

    If your macro opens the text file, that file becomes the activeworkbook unless your code immediately re-activates Main.xls (which would still be ThisWorkbook to the code), so as soon as you import tthe text file you could execute <pre>ActiveWorkbook.SaveAs FileName:="FileName.xls", FileFormat:=xlNormal</pre>

    If you wish or need to you could use Workbooks("text.cda").SaveAs in place of ActiveWorkbook. And don't forget to include tyhe path !

    Hope that helps.

    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple files open in Excel (Excel xp, Win2000)

    After the OpenText method executes, the text.cda file should be the active workbook. I just ran the code below, and the message box identified "text.cda".

    <pre>Dim oWB As Workbook
    Workbooks.OpenText "C:Worktext.cda"
    Set oWB = ActiveWorkbook
    MsgBox oWB.Name
    </pre>

    Legare Coleman

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple files open in Excel (Excel xp, Win2000)

    wow! thanks so much for the quick help. i was right in the middle of working on this.

    I want the excel filename to be the same as the cda filename. text.cda to save as text.xls so when i use

    ActiveWorkbook.SaveAs Filename:="text.xls"

    i don't have to type the "text.xls" in quote marks, i can use a variable. How do i extract just the "text" part of the filename to put it in a variable?

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multiple files open in Excel (Excel xp, Win2000)

    Assuming that your imported file has a name followed by one file extension, and it is the active workbook, the following should work <pre> With ActiveWorkbook
    .SaveAs FileName:=Left(.Name, InStr(1, .Name, ".")) & "xls"
    End With</pre>

    You may wish to incorporate a path name, if so set the FileName:= Path & Left(etc...), where path represents the drive and folder path wher you want to save.

    Andrew C

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple files open in Excel (Excel xp, Win2000)

    perfecto! thank you

Posting Permissions

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