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

 

[Norman Jones]

Hi Alex,

Try:

Dim sStr As String

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

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

 

[Alex]

Norman

Thank you for your help. It worked perfectly

Alex