Problem with macro for importing data to template

[Jay_B]

Hi Everyone,

I'm relatively new to using macro's. I have a lot of text files that are
created by a data logger that take an age to work through. I have created a
master template that contains everything that needs doing to a dataset, and I
have recorded a macro that opens the template and then imports a data file to
a set range in the template.

My problem is that the macro does not allow me to select the file/folder
name of the data I want to import. I've included my code so far below - can
anyone help me please?

Many thanks in advance for your help!

Jay_B


Sub Data_Import()
'
' Data Import Macro
' Macro recorded by Me
'

'
Workbooks.Open Filename:="E:\conv\transposed master.xls"
ActiveWindow.SmallScroll Down:=-156
Range("A1").Select
With
ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\conv\06060621\ASAB2.1" _
, Destination:=Range("A1"))
.Name = "ASAB2.1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("transposed data").Select
ActiveWindow.SmallScroll Down:=-21
Sheets("Pressure Trace").Select
ActiveWindow.Zoom = True
End Sub

[[email protected]]

something like this


Dim FileToOpen
FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
Set curbook = ActiveWorkbook
If FileToOpen <> False Then
Workbooks.OpenText FileName:=FileToOpen, 'etc
else
msgbox "Please select a file first
exit sub
end if



Jay_B wrote:
> Hi Everyone,
>
> I'm relatively new to using macro's. I have a lot of text files that are
> created by a data logger that take an age to work through. I have created a
> master template that contains everything that needs doing to a dataset, and I
> have recorded a macro that opens the template and then imports a data file to
> a set range in the template.
>
> My problem is that the macro does not allow me to select the file/folder
> name of the data I want to import. I've included my code so far below - can
> anyone help me please?
>
> Many thanks in advance for your help!
>
> Jay_B
>
>
> Sub Data_Import()
> '
> ' Data Import Macro
> ' Macro recorded by Me
> '
>
> '
> Workbooks.Open Filename:="E:\conv\transposed master.xls"
> ActiveWindow.SmallScroll Down:=-156
> Range("A1").Select
> With
> ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\conv\06060621\ASAB2.1" _
> , Destination:=Range("A1"))
> .Name = "ASAB2.1"
> .FieldNames = True
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = True
> .RefreshOnFileOpen = False
> .RefreshStyle = xlInsertDeleteCells
> .SavePassword = False
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .TextFilePromptOnRefresh = False
> .TextFilePlatform = 850
> .TextFileStartRow = 1
> .TextFileParseType = xlDelimited
> .TextFileTextQualifier = xlTextQualifierDoubleQuote
> .TextFileConsecutiveDelimiter = False
> .TextFileTabDelimiter = True
> .TextFileSemicolonDelimiter = False
> .TextFileCommaDelimiter = True
> .TextFileSpaceDelimiter = False
> .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
> .TextFileTrailingMinusNumbers = True
> .Refresh BackgroundQuery:=False
> End With
> Sheets("transposed data").Select
> ActiveWindow.SmallScroll Down:=-21
> Sheets("Pressure Trace").Select
> ActiveWindow.Zoom = True
> End Sub

[Jay_B]

Thanks for the prompt reply!

I think that I might be trying to run before I can walk with this, but do I
replace the "E:\conv\06060621\ASAB2.1" with "FileToOpen", and then add the
rest of the code in a new sub routine?
I apologise for being any good at this
Many thanks in advance

Jay_B

"[email protected]" wrote:

> something like this
>
>
> Dim FileToOpen
> FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
> Set curbook = ActiveWorkbook
> If FileToOpen <> False Then
> Workbooks.OpenText FileName:=FileToOpen, 'etc
> else
> msgbox "Please select a file first
> exit sub
> end if
>
>
>
> Jay_B wrote:
> > Hi Everyone,
> >
> > I'm relatively new to using macro's. I have a lot of text files that are
> > created by a data logger that take an age to work through. I have created a
> > master template that contains everything that needs doing to a dataset, and I
> > have recorded a macro that opens the template and then imports a data file to
> > a set range in the template.
> >
> > My problem is that the macro does not allow me to select the file/folder
> > name of the data I want to import. I've included my code so far below - can
> > anyone help me please?
> >
> > Many thanks in advance for your help!
> >
> > Jay_B
> >
> >
> > Sub Data_Import()
> > '
> > ' Data Import Macro
> > ' Macro recorded by Me
> > '
> >
> > '
> > Workbooks.Open Filename:="E:\conv\transposed master.xls"
> > ActiveWindow.SmallScroll Down:=-156
> > Range("A1").Select
> > With
> > ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\conv\06060621\ASAB2.1" _
> > , Destination:=Range("A1"))
> > .Name = "ASAB2.1"
> > .FieldNames = True
> > .RowNumbers = False
> > .FillAdjacentFormulas = False
> > .PreserveFormatting = True
> > .RefreshOnFileOpen = False
> > .RefreshStyle = xlInsertDeleteCells
> > .SavePassword = False
> > .SaveData = True
> > .AdjustColumnWidth = True
> > .RefreshPeriod = 0
> > .TextFilePromptOnRefresh = False
> > .TextFilePlatform = 850
> > .TextFileStartRow = 1
> > .TextFileParseType = xlDelimited
> > .TextFileTextQualifier = xlTextQualifierDoubleQuote
> > .TextFileConsecutiveDelimiter = False
> > .TextFileTabDelimiter = True
> > .TextFileSemicolonDelimiter = False
> > .TextFileCommaDelimiter = True
> > .TextFileSpaceDelimiter = False
> > .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
> > .TextFileTrailingMinusNumbers = True
> > .Refresh BackgroundQuery:=False
> > End With
> > Sheets("transposed data").Select
> > ActiveWindow.SmallScroll Down:=-21
> > Sheets("Pressure Trace").Select
> > ActiveWindow.Zoom = True
> > End Sub
>
>

[[email protected]]

You've written Workbooks.Open Filename:="E:\conv\transposed master.xls"
in your code, which will open that file only - replace that with

Dim FileToOpen
FileToOpen = Application.GetOpenFilename("Excel Files (*.XLS), *.XLS")
'line above will display the file open dialog box and then store the
chosen file name
Set curbook = ActiveWorkbook
If FileToOpen <> False Then
'if filetoopen = false then user cancelled
Workbooks.Open FileName:=FileToOpen, 'etc
'this is the direct replacement for your code line and the rest of your
code goes here
else
msgbox "Please select a file first
exit sub
end if

Jay_B wrote:
> Thanks for the prompt reply!
>
> I think that I might be trying to run before I can walk with this, but do I
> replace the "E:\conv\06060621\ASAB2.1" with "FileToOpen", and then add the
> rest of the code in a new sub routine?
> I apologise for being any good at this
> Many thanks in advance
>
> Jay_B
>
> "[email protected]" wrote:
>
> > something like this
> >
> >
> > Dim FileToOpen
> > FileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
> > Set curbook = ActiveWorkbook
> > If FileToOpen <> False Then
> > Workbooks.OpenText FileName:=FileToOpen, 'etc
> > else
> > msgbox "Please select a file first
> > exit sub
> > end if
> >
> >
> >
> > Jay_B wrote:
> > > Hi Everyone,
> > >
> > > I'm relatively new to using macro's. I have a lot of text files that are
> > > created by a data logger that take an age to work through. I have created a
> > > master template that contains everything that needs doing to a dataset, and I
> > > have recorded a macro that opens the template and then imports a data file to
> > > a set range in the template.
> > >
> > > My problem is that the macro does not allow me to select the file/folder
> > > name of the data I want to import. I've included my code so far below - can
> > > anyone help me please?
> > >
> > > Many thanks in advance for your help!
> > >
> > > Jay_B
> > >
> > >
> > > Sub Data_Import()
> > > '
> > > ' Data Import Macro
> > > ' Macro recorded by Me
> > > '
> > >
> > > '
> > > Workbooks.Open Filename:="E:\conv\transposed master.xls"
> > > ActiveWindow.SmallScroll Down:=-156
> > > Range("A1").Select
> > > With
> > > ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\conv\06060621\ASAB2.1" _
> > > , Destination:=Range("A1"))
> > > .Name = "ASAB2.1"
> > > .FieldNames = True
> > > .RowNumbers = False
> > > .FillAdjacentFormulas = False
> > > .PreserveFormatting = True
> > > .RefreshOnFileOpen = False
> > > .RefreshStyle = xlInsertDeleteCells
> > > .SavePassword = False
> > > .SaveData = True
> > > .AdjustColumnWidth = True
> > > .RefreshPeriod = 0
> > > .TextFilePromptOnRefresh = False
> > > .TextFilePlatform = 850
> > > .TextFileStartRow = 1
> > > .TextFileParseType = xlDelimited
> > > .TextFileTextQualifier = xlTextQualifierDoubleQuote
> > > .TextFileConsecutiveDelimiter = False
> > > .TextFileTabDelimiter = True
> > > .TextFileSemicolonDelimiter = False
> > > .TextFileCommaDelimiter = True
> > > .TextFileSpaceDelimiter = False
> > > .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
> > > .TextFileTrailingMinusNumbers = True
> > > .Refresh BackgroundQuery:=False
> > > End With
> > > Sheets("transposed data").Select
> > > ActiveWindow.SmallScroll Down:=-21
> > > Sheets("Pressure Trace").Select
> > > ActiveWindow.Zoom = True
> > > End Sub
> >
> >