How to change a Macro based on content of a cell

[Alex]

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.

[Alex]

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" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>

[Norman Jones]

Hi Alex,

Try:

Dim sStr As String

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

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



---
Regards,
Norman



"Alex" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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.
>>
>
>

[Alex]

Norman

Thank you for your help. It worked perfectly

Alex

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Alex,
>
> Try:
>
> Dim sStr As String
>
> sStr = ThisWorkbook.Sheets("Logs").Range("A8").Value & ".pck"
>
> Workbooks.OpenText Filename:="C:\DrillData\" & sStr, ...
>
>
>
> ---
> Regards,
> Norman
>
>
>
> "Alex" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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.
>>>
>>
>>
>
>