Prev: Access Version being used
Next: Remove Duplicate Numbers in One Cell
From: Gordon Rainsford on 16 Jan 2010 05:07
I have some code that includes the line:

Set shRanks = Workbooks("Ranks.csv").Sheets("Ranks")

The problem is that the file I need to set as shRanks may have an
integer in its name - eg "Ranks17.csv"

How best can I do this?
From: joel on 16 Jan 2010 06:50

A CSV file is only one sheet since it is really just a text file. The
sheet name is always the same as the workbookname. the best solution is
just to use the first tab.

from
Set shRanks = Workbooks("Ranks.csv").Sheets("Ranks")


to

Set shRanks = Workbooks("Ranks.csv").Sheets(1)



I don't think this is going to work since the workbook name is also
wrong. I would need to see the statement you use to read the CSV file.
If you are using workbook open then try this


fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If fileToOpen = False Then
MsgBox("Cannot Open file - Exiting Macro")
exit sub
End If

Set bk = workbooks.open(filename:=fileToOpen)

Set shRanks = bk.Sheets(1)


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=170681

[url="http://www.thecodecage.com"]Microsoft Office Help[/url]

From: Gary''s Student on 16 Jan 2010 07:04
Sub demo()
Dim shRanks As Worksheet
Dim n As Integer
n = 17
Set shRanks = Workbooks("Ranks" & n & ".csv").Sheets("Ranks")
End Sub

--
Gary''s Student - gsnu200909


"Gordon Rainsford" wrote:

> I have some code that includes the line:
>
> Set shRanks = Workbooks("Ranks.csv").Sheets("Ranks")
>
> The problem is that the file I need to set as shRanks may have an
> integer in its name - eg "Ranks17.csv"
>
> How best can I do this?
> .
>
From: Gordon Rainsford on 16 Jan 2010 07:21
I must have been unclear.

There will be a worksheet open, whose name is "Ranks**.csv" where ** are
wildcards. I want to set that worksheet as shRanks, whatever the value
of **,

Thanks,

Gordon

Gary''s Student <GarysStudent(a)discussions.microsoft.com> wrote:

> Sub demo()
> Dim shRanks As Worksheet
> Dim n As Integer
> n = 17
> Set shRanks = Workbooks("Ranks" & n & ".csv").Sheets("Ranks")
> End Sub
From: Dave Peterson on 16 Jan 2010 09:23
Maybe you can set the variable right after you open the .csv file.

Dim scvwks as worksheet
dim CSVWkbk as workbook

set csvwkbk = workbooks.open(filename:="c:\ranks.csv")
set csvwks = activesheet



Gordon Rainsford wrote:
>
> I have some code that includes the line:
>
> Set shRanks = Workbooks("Ranks.csv").Sheets("Ranks")
>
> The problem is that the file I need to set as shRanks may have an
> integer in its name - eg "Ranks17.csv"
>
> How best can I do this?

--

Dave Peterson
 |  Next  |  Last
Pages: 1 2 3
Prev: Access Version being used
Next: Remove Duplicate Numbers in One Cell