Ok, I've written this so far to open the text files and convert them to excel files. The code is supposed to pickup the name of the file based on a row as you can see in the code "Cells(4, filecolumnlocation)" The problem I'm having is that if I use this code it takes the name from a cell location on the newly opened worksheet, rather than the primary worksheet. I need it to reference the primary worksheet which I attempted with the following:
Dim filecolumnlocation As Long
For filecolumnlocation = 8 To 10
Dim textname As Long
textname = Cells(4, filecolumnlocation)
Workbooks.OpenText Filename:= _
"C:Program FilesTradeStationMyWorkIndividual Data" & textname & ".txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1))
ActiveWorkbook.SaveAs Filename:= _
"C:Program FilesTradeStationMyWorkIndividual Data" & textname & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close
Next filecolumnlocation
End Sub
The decoder is telling me I've got a "type mismatch" with my variable "textname." I assume because it is text? Any help? Thank you very much.
-dc
Dim filecolumnlocation As Long
For filecolumnlocation = 8 To 10
Dim textname As Long
textname = Cells(4, filecolumnlocation)
Workbooks.OpenText Filename:= _
"C:Program FilesTradeStationMyWorkIndividual Data" & textname & ".txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1))
ActiveWorkbook.SaveAs Filename:= _
"C:Program FilesTradeStationMyWorkIndividual Data" & textname & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWorkbook.Close
Next filecolumnlocation
End Sub
The decoder is telling me I've got a "type mismatch" with my variable "textname." I assume because it is text? Any help? Thank you very much.
-dc