MrExcel.com - Your One Stop for Excel Tips & Solutions

InputBox Macro, Maybe?

Posted by Sam on June 21, 2001 8:49 AM

What I'm trying to do is get a macro that can copy and paste data from one file to another but the problem is the file that I will be copying from will change and I'm not sure how to correct for that. i thought maybe and inputbox but that hasn't worked out too well. Any help would be appreciated. Thanks,
Sam

Posted by Ben O. on June 21, 2001 8:58 AM

You could use a GetOpenFileName statement that lets the user browse for the file to copy the data from:

tWorkbook = Application.GetOpenFilename("Excel Spreadsheets (*.xls),*.xls", , "Specify File Containing List")

Let me know if you'd like any more help. I've got a long macro that does something very similar to what you're trying to do.

-Ben

Posted by Sam on June 21, 2001 9:13 AM

Thanks. That's a help. how I be able to add text files to the availiable list of files?

Also I'm running into trouble with my copying and pasting. What i need the macro to do is go through the 'Type' column identify a certain name and then go over to the input file and copy the needed information and go back to the original file and past in the right location. i hope that made sense. Do you think could give any tips on how to conquer that one?

Thanks for everything,
Sam

Posted by Sam on June 21, 2001 9:21 AM

NeverMind

Nevermind I figured out how to add text files. I was wondering how I could set it up so that after the user selects the file it gets open or activated, you know what i mean?

Thanks,
Sam

Posted by Ben O. on June 21, 2001 9:32 AM

Re: NeverMind

Yes, you can use:

Workbooks.OpenText FileName:=tWorkbook

Assuming tWorkbook is the variable you assigned your previous GetOpenFileName statement to. But when you open a text file, it starts the text import wizard, where you have to specify several other conditions. I would recommending recording a macro while opening a text file like the ones your users will be opening, then using those parameters in your macro.

If you wanted to open the text file with Notepad or another exteral application, I don't know how to do that.

-Ben