Help with VBA code to open files and convert file type?

[derickc]

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

 

[derickc]

ok, can't use text where it's expecting an integer. What I have is a reference problem. How do I reference a cell with the "cells" command on a different worksheet?

This doesn't work:

& Summary.xls!Cells(4, filecolumnlocation) &

Thanks again.

-dc

 

[derickc]

figured it out. I have to use workbooks.worksheets.cells to get the exact reference.

-dc