How to change a Macro based on content of a cell

Hi,

I need to import a text file into a spreadsheet. I can do this manually by 
doing File > Open > then select a text file. Then Excel goes through the 
Text Import Wizard

I can automate this by recording a Macro in Excel. The macro looks like this 
(Excell wrote that for me, I did not):

Sub Macro1()
    Workbooks.OpenText Filename:="C:\DrillData\" + "MOR701.pck", 
Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), 
Array(12 _
        , 1), Array(26, 1), Array(37, 1)), TrailingMinusNumbers:=True
End Sub

How can I change this macro to look at the content of cell Logs!A8 (Cell A8 
in the tab called "Logs")

I guess it would look something like this:

Sub Macro1()
    Workbooks.OpenText Filename:="C:\DrillData\" + _

content_cell (Logs!A8)&".pck", _

Origin:=xlMSDOS, _
        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), 
Array(12 _
        , 1), Array(26, 1), Array(37, 1)), TrailingMinusNumbers:=True
End Sub



Thanks for your help

Alex. 


0
no-one (25)
5/15/2006 1:32:48 AM
excel 39879 articles. 2 followers. Follow

3 Replies
384 Views

Similar Articles

[PageSpeed] 33

Hi all,

I guess I was not very clear in my request.

My real challendge is that I have 100's of text files each containing data 
formated in the same way. I need to open a given text file to use its data 
based on the content of one of the cells of my spreadsheet. While I can do 
the process manually (look at cell Logs!A8, import corresponding text file I 
want to automate this process, I don't know how to automate this.

I found out how to automate the process if I always import the same text 
file (see below), I found a way in theory how to automate it for any text 
file (by placing the name of the file in cell Logs!A8), I just don't know 
how to use the content of that cell to modify the Macro below.

Thank for your help

Alex

"Alex" <no-one@nowhere.com> wrote in message 
news:%237sZO97dGHA.1856@TK2MSFTNGP03.phx.gbl...
> Hi,
>
> I need to import a text file into a spreadsheet. I can do this manually by 
> doing File > Open > then select a text file. Then Excel goes through the 
> Text Import Wizard
>
> I can automate this by recording a Macro in Excel. The macro looks like 
> this (Excell wrote that for me, I did not):
>
> Sub Macro1()
>    Workbooks.OpenText Filename:="C:\DrillData\" + "MOR701.pck", 
> Origin:=xlMSDOS, _
>        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), 
> Array(12 _
>        , 1), Array(26, 1), Array(37, 1)), TrailingMinusNumbers:=True
> End Sub
>
> How can I change this macro to look at the content of cell Logs!A8 (Cell 
> A8 in the tab called "Logs")
>
> I guess it would look something like this:
>
> Sub Macro1()
>    Workbooks.OpenText Filename:="C:\DrillData\" + _
>
> content_cell (Logs!A8)&".pck", _
>
> Origin:=xlMSDOS, _
>        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), 
> Array(12 _
>        , 1), Array(26, 1), Array(37, 1)), TrailingMinusNumbers:=True
> End Sub
>
>
>
> Thanks for your help
>
> Alex.
> 


0
no-one (25)
5/15/2006 1:53:51 AM
Hi Alex,

Try:

   Dim sStr As String

   sStr = ThisWorkbook.Sheets("Logs").Range("A8").Value & ".pck"

   Workbooks.OpenText Filename:="C:\DrillData\" & sStr, ...



---
Regards,
Norman



"Alex" <no-one@nowhere.com> wrote in message 
news:u4h9BJ8dGHA.3484@TK2MSFTNGP04.phx.gbl...
> Hi all,
>
> I guess I was not very clear in my request.
>
> My real challendge is that I have 100's of text files each containing data 
> formated in the same way. I need to open a given text file to use its data 
> based on the content of one of the cells of my spreadsheet. While I can do 
> the process manually (look at cell Logs!A8, import corresponding text file 
> I want to automate this process, I don't know how to automate this.
>
> I found out how to automate the process if I always import the same text 
> file (see below), I found a way in theory how to automate it for any text 
> file (by placing the name of the file in cell Logs!A8), I just don't know 
> how to use the content of that cell to modify the Macro below.
>
> Thank for your help
>
> Alex
>
> "Alex" <no-one@nowhere.com> wrote in message 
> news:%237sZO97dGHA.1856@TK2MSFTNGP03.phx.gbl...
>> Hi,
>>
>> I need to import a text file into a spreadsheet. I can do this manually 
>> by doing File > Open > then select a text file. Then Excel goes through 
>> the Text Import Wizard
>>
>> I can automate this by recording a Macro in Excel. The macro looks like 
>> this (Excell wrote that for me, I did not):
>>
>> Sub Macro1()
>>    Workbooks.OpenText Filename:="C:\DrillData\" + "MOR701.pck", 
>> Origin:=xlMSDOS, _
>>        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), 
>> Array(12 _
>>        , 1), Array(26, 1), Array(37, 1)), TrailingMinusNumbers:=True
>> End Sub
>>
>> How can I change this macro to look at the content of cell Logs!A8 (Cell 
>> A8 in the tab called "Logs")
>>
>> I guess it would look something like this:
>>
>> Sub Macro1()
>>    Workbooks.OpenText Filename:="C:\DrillData\" + _
>>
>> content_cell (Logs!A8)&".pck", _
>>
>> Origin:=xlMSDOS, _
>>        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), 
>> Array(12 _
>>        , 1), Array(26, 1), Array(37, 1)), TrailingMinusNumbers:=True
>> End Sub
>>
>>
>>
>> Thanks for your help
>>
>> Alex.
>>
>
> 


0
normanjones (1047)
5/15/2006 2:42:22 AM
Norman

Thank you for your help. It worked perfectly

Alex

"Norman Jones" <normanjones@whereforartthou.com> wrote in message 
news:Om4Bxk8dGHA.3632@TK2MSFTNGP02.phx.gbl...
> Hi Alex,
>
> Try:
>
>   Dim sStr As String
>
>   sStr = ThisWorkbook.Sheets("Logs").Range("A8").Value & ".pck"
>
>   Workbooks.OpenText Filename:="C:\DrillData\" & sStr, ...
>
>
>
> ---
> Regards,
> Norman
>
>
>
> "Alex" <no-one@nowhere.com> wrote in message 
> news:u4h9BJ8dGHA.3484@TK2MSFTNGP04.phx.gbl...
>> Hi all,
>>
>> I guess I was not very clear in my request.
>>
>> My real challendge is that I have 100's of text files each containing 
>> data formated in the same way. I need to open a given text file to use 
>> its data based on the content of one of the cells of my spreadsheet. 
>> While I can do the process manually (look at cell Logs!A8, import 
>> corresponding text file I want to automate this process, I don't know how 
>> to automate this.
>>
>> I found out how to automate the process if I always import the same text 
>> file (see below), I found a way in theory how to automate it for any text 
>> file (by placing the name of the file in cell Logs!A8), I just don't know 
>> how to use the content of that cell to modify the Macro below.
>>
>> Thank for your help
>>
>> Alex
>>
>> "Alex" <no-one@nowhere.com> wrote in message 
>> news:%237sZO97dGHA.1856@TK2MSFTNGP03.phx.gbl...
>>> Hi,
>>>
>>> I need to import a text file into a spreadsheet. I can do this manually 
>>> by doing File > Open > then select a text file. Then Excel goes through 
>>> the Text Import Wizard
>>>
>>> I can automate this by recording a Macro in Excel. The macro looks like 
>>> this (Excell wrote that for me, I did not):
>>>
>>> Sub Macro1()
>>>    Workbooks.OpenText Filename:="C:\DrillData\" + "MOR701.pck", 
>>> Origin:=xlMSDOS, _
>>>        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 
>>> 1), Array(12 _
>>>        , 1), Array(26, 1), Array(37, 1)), TrailingMinusNumbers:=True
>>> End Sub
>>>
>>> How can I change this macro to look at the content of cell Logs!A8 (Cell 
>>> A8 in the tab called "Logs")
>>>
>>> I guess it would look something like this:
>>>
>>> Sub Macro1()
>>>    Workbooks.OpenText Filename:="C:\DrillData\" + _
>>>
>>> content_cell (Logs!A8)&".pck", _
>>>
>>> Origin:=xlMSDOS, _
>>>        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 
>>> 1), Array(12 _
>>>        , 1), Array(26, 1), Array(37, 1)), TrailingMinusNumbers:=True
>>> End Sub
>>>
>>>
>>>
>>> Thanks for your help
>>>
>>> Alex.
>>>
>>
>>
>
> 


0
no-one (25)
5/15/2006 4:31:35 AM
Reply:

Similar Artilces:

Recorded Macro
can you create a recorded macro for any application? Office Applications -- Gary''s Student gsnu200705 "want to know" wrote: > can you create a recorded macro for any application? In MS Office Suite of Applications...............yes. For other applications you could do a search on the 'net for "macro recorder" and get third-party utilities that will add macros to just about anything. Gord Dibben MS Excel MVP On Tue, 13 Feb 2007 11:06:01 -0800, want to know <wanttoknow@discussions.microsoft.com> wrote: >can you create a recorded macro for ...

Date changes back 4 years when copying
When we copy a date from an excel 2000 worksheet and paste it in an excel XP worksheet the year will move back four years. Any ideas? Go Tools > Options > Calculation and make sure both files have '1904 Date System' either checked or unchecked, ie both the same, Regards "Dave" <anonymous@discussions.microsoft.com> wrote in message news:328401c4a4e0$a7dacb20$a301280a@phx.gbl... > When we copy a date from an excel 2000 worksheet and paste > it in an excel XP worksheet the year will move back four > years. Any ideas? > > In article <328401...

See if cell is in Range
Thanks for taking the time to read my question. I'm passing a string that is a cell reference to a function. In that function I want to determine if that cell reference is within a predetermined range. Not sure how to do that. Right now I have: Function CheckRange(TheSheet As String, TheCell As Range) As Boolean If TheSheet = "Sheet1" Then if TheCell In Range("B4:B30") then 'This line is red as it is incorrect CheckRange = True End If What do I use instead of "In"? Thanks, Brad Brad Use something like: If In...

Editing in a cell
I am having a problem with a newly created workbook. When I go to a cell that has data in it and try to add more data to the existing string I lose all the previous information. The work sheet that I am working in is protected and I have "edit directly in cell" checked in the options section. It is almost like my double click (as well as F2) are being treated as a single click. Thank you I figured it out. For some reason "Hide" is selected for the unlocked cells. "John English" wrote: > I am having a problem with a newly created workbook. > > Wh...

Autofilter macro
Hello, I'm very new with macro. I need some help to create a code with Autofilter the "X" value in the column G. Thanks, JT You can record a macro when you do it manually. You should have code that works. JT wrote: > > Hello, > I'm very new with macro. I need some help to create a code with Autofilter > the "X" value in the column G. > Thanks, > JT -- Dave Peterson Turn on the macro recorder while doing the steps. Gord Dibben MS Excel MVP On Tue, 25 May 2010 09:01:01 -0700, JT <JT@discussions.microsoft.com&g...

Money signs appear in my Cell and I don't want them there
Im trying to enter the numbers 2.9 in cell like AD and it turns it into $2.90. That is not what I need, how do I stop that? Format as General instead of currency -- Regards, Peo Sjoblom http://nwexcelsolutions.com "kate" <kate@discussions.microsoft.com> wrote in message news:E9EEA936-437C-486F-A2D3-2385EF6BD2D0@microsoft.com... > Im trying to enter the numbers 2.9 in cell like AD and it turns it into > $2.90. That is not what I need, how do I stop that? ...

How do I get a cell to remember the last entry and add to it?
I am using Excel 2000. I want my cell to remember the number in it and add an additional number each time I need to retotal. Jab Sounds like you want that cell to be an accumlator cell. Simple question with complex results. You can have a cumulative total in a cell if you have a separate source cell for adding a new total to the original. Use at your own risk. I am Posting this just to show you how it can be done, not as a good solution. You would be much better off to have another column so you can keep track of past entries. Goes like this: =IF(CELL("address")="$C$4&...

Change Quote.dot
Can someone please tell me how the make changes to the Quote.dot. There is some text on the last page I need to change and for some reason I can not get past the merge fields to do this. Hi Valerie, Can you please help me understand exactly what text you need to change? -- Manisha Powar (Madhusudanan) Program Manager Microsoft Dynamics - CRM This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use. � 2005 Microsoft Corporation. All rights reserved. "Valerie" <Valerie@discussions.microsoft.com> wrote in mess...

Changing the user password without the use of SA or DYNSA accounts
Is there any way to save a user password in GP v10 without the use of the SA or DYNSA login? I would like to grant security to one user to do this. Currently the save button is greyed out unless they log in as SA or DYNSA. Thank you You can do this by granting that user sysadmin rights in SQL management Studio. In SMS, expand the Security folder, then logins, then find that user, right click the user, go to properties, click server roles and check the box for sysadmin. "Junior De Alba" wrote: > Is there any way to save a user password in GP v10 without the use of the S...

Oops! can I change border now?
Well I've just spent months working on my first Publisher document. I've created a 34 page catalog which looks quite nice... I spent a lot of time making sure the master page was nice and even. I did a test print out and it looked fine...however... I forgot that the pages will be stapled...and that though the catalog is acceptable the way it is...I should really move the entire printed area over slightly to the right. Since all pages have used the same master page layout...is there some simple way to get all objects shifted over...on all pages? It would be way too time consuming to mo...

Bold report column based on month
I have a report with a column for each month. (Jan, Feb, Mar...) I'd like to bold the data in the column that the report is run for. I placed this code in the on Format event of the report to test, the code is executing ok but the columns end up all in normal weight. m = DatePart("m", dt) Select Case m Case 1 Me.Jan.FontWeight = vbBold Me.Feb.FontWeight = vbNormal Me.Mar.FontWeight = vbNormal Me.Apr.FontWeight = vbNormal Me.May.FontWeight = vbNormal Me.Jun.FontWeight = vbNormal Me.Jul.FontWeight = vbNormal Me.Aug.FontWeight = vbNormal Me...

looking for range of text in a single cell
I just started a new job and my company already had a file with abou 16,000 records in it (called Products). This file contains all of th parts numbers that they carry. In this file there is one column calle "description". This cell contains the dimensions, color name and som other information such as if the piece is flat or rounded. I hav another file that has about 400 records and this file tells me "colo name" as well as the "type" of stone that it is (called Stone_Type). What I am trying to do is add a field to the Products file that wil say stone type. ...

change tab colour of a worksheet
How do I change the colour of a worksheet name tab so that I can identify groups Sally, right click on the tab, but only if you have excel 2002 or above -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "Sally" <Sally@discussions.microsoft.com> wrote in message news:6FA9969D-F3C6-47EE-B02A-E8BCC3706F0E@microsoft.com... > How do I change the colour of a workshee...

To have a 'fixed cell' be equal to the last data entered cell in a column
Good Evening All, I have a worksheet, example below. I have frozen the panes to always show rows 1-3. I wish the cell A3 to be the same as the last 'non-blank' cell in Column A.(See explanation below). A B 1 2 Header Header 3__________________ 4 M1004 5 M1005 6 M1002 7 M1003 8 M1006 9 M1001 10 So in this case, at present, A3 would be M1001,(A9), but when I enter a new value in A10, (eg M1008), I would like A3 to automatically update to A10 ie M1008. I think that maybe INDIRECT or OFFSET maybe involved but am fairly unfamiliar with ...

dividing a text based on a delimiter(dynamic)
i want to create a formula that breaks a list of data (in my case emails) into sets of ten or twenty. each email is delimited with commas and all the emails are contained in one cell that references a column full of singular emails in singular cells. i am so totally lost. any ideas? Hi! Help me. What exactly does "all the emails are contained in one cell that references a column ful of singular emails in singular cells" mean? Al -- Message posted from http://www.ExcelForum.com Here is what happens, I have a cell with "email1@blah.org,email2@bleh.com,email3@bluh.ne...

Read only access based on drop down box....
Hello. I have a table Calls in our contacts database. The table contains the following fields: CallDateTime (txtbox) CallType (combobox) Initials (txtbox): I have two departments that want to use the same Contacts database. However, each department wants to enter Calls in the calls table and have read only access to the other departments data in the table based on a drop down value in one of the fields. Each department has their own CallTypes that they are responsible for. Is it possible to have them enter in the same subform but have write and read access to the call types they are s...

User accounts changing themselves to another users details..?
Hi, We're getting an intermittent problem with our CRM 1.2 installation. Randomly, users names and email addresses are changing to that of another user but their logon name is staying the same. This is meaning that when they logon to CRM, their name is showing wrongly and any records assigned to them are showing the wrong name. The name they change to is always the same and the user records changing varies. eg: Say we have 10 users, one of whom is "Joe Bloggs". Today, 3 other users records names had changed so that their first name showed "Joe" and their surn...

How do I have a cell update itself only if it's currently blank?
I need to track to retain the first value of a date field. Each time I load the worksheet the date field has the potential to change, I need to keep track of the initial date in addition to the current date that is in the field. I have date columns called ASSIGNED and 1st ASSIGNED. The initial value of both will be blank. At some point the ASSIGNED will be populated with a date. When it is populated for the first time I want to save this value in the 1st ASSIGNED column. I need something like: IF ( ISBLANK(ASSIGNED), "", ASSIGNED ) in my 1st Assigned cell, however, I do not ...

Macro Emailing an XLS via Outlook
Is there any way to shut off the Outlook notice that says "a program is trying to send email on your behalf - are you OK with this ?" Also - Is there anyway to automatocally update links without asking you when the file opens ? Thanks Hi 1) http://www.rondebruin.nl/copy3.htm#sheet 2) When you open a file with code or manual -- Regards Ron de Bruin http://www.rondebruin.nl "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:ACC6326D-4CD3-4019-BCC0-53E5D6E2E573@microsoft.com... > Is there any way to shut off the Outlook notice that says &qu...

Changing Font Colour if cell is certain word?
Can I make the font of a certain cell change it's font colour to Red if the word in the cell is, example, "Fail"? I want the change to apply from B2:F23. -- wuming79 ------------------------------------------------------------------------ wuming79's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36037 View this thread: http://www.excelforum.com/showthread.php?threadid=560169 Use "Conditional Formatting". It's on the Format menu -- MartinShor Software Tester &amp; Musicia -----------------------------------------------------...

Change Contact List Location
Is there a way to change the location of Live Mail's Contact List from its buried existence on my system drive (C:) to my data drive (D:)? I moved my storage folder over there a long time ago. Thank you. "Jack Gillis" <XXXXXXXXXXXXX@widomaker.com> wrote in message news:OwbnmhtmKHA.5840@TK2MSFTNGP05.phx.gbl... > Is there a way to change the location of Live Mail's Contact List from > its buried existence on my system drive (C:) to my data drive (D:)? I > moved my storage folder over there a long time ago. You didn't mention your OS,...

how do I change the starting page number in a Publisher newslette.
I changed the starting page number in a newsletter from 1 to 2. It was a mistake (I had my reasons at the time). Anyway, now, I cannot switch it back to 1. All the headers start on page 2. Help! How do I change it back to 1? Thanks, Ann The page numbers are usually on the Master Page, view, Master Page. In Publisher 2000 and below it will be called "Background." -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "Ann" <Ann@discussions.microsoft.com> wrote in message news:D7F2A954-271C-406D-9C20-80D0943F2A7...

How do I change the timing for just one slide?
I am trying to change the timing for june one or two slides. This is a pretty simple slideshow set to music. I have used rehearsed timings and I don't want to have to go through all that just to change one or two slides. bytownmary, See if this helps: http://sixtiesfan.multiply.com/journal/item/74/Rehearse_timings_redo_timing_of_just_one_slide_Try-out_voor_tijdsinstellingen_de_timing_van_slechts_een_dia_opnieuw_opnemen. -- Luc Sanders MVP - PowerPoint "bytownmary" <bytownmary@discussions.microsoft.com> schreef in bericht news:E719DD2B-43E0-4024-A62D-D...

Resource Poll Changes Effective When?
In Project Server, when is a change to a work resource rate table or say a revision to a work resource's availability going to be seen in an a Project? Is it the next time the Project file is opened, the next time it is saved, the next time it is published? Changes to an enterprise resource should be seen in the project the next time it is opened. Each time a project schedule is opened from the server, it will read the latest information from the enterprise resource pool for the resources that are on that project. Jonathan "PSU35" <PSU35@discussions.mic...

VB Macro Code to Save Excel Ranges in Access Tables
Hi, I tried searching for this solution in the entire forum but could not find relevant answer. I have excel sheets and I need to save certain ranges of this data into access tables..The idea is to automate it as much as possible using VB Macro code in Command buttons placed in Excel sheet. Can someone please provide code snippet to connect to database from excel and save the data into access? -- Thanks and Regards I'm not sure if excel can write in access tables but it is possible to create an excel sheet which is used as access table (from access use the link feature instead o...