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

derickc

New Member
Joined
May 28, 2002
Messages
23
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

derickc

New Member
Joined
May 28, 2002
Messages
23
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

New Member
Joined
May 28, 2002
Messages
23
figured it out. I have to use workbooks.worksheets.cells to get the exact reference.

-dc
 

Forum statistics

Threads
1,144,510
Messages
5,724,782
Members
422,578
Latest member
annsalinas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top