Workbook Variable

Neiluk66 · Jul 20th 2005

Hi,

Two questions really:

I am trying to assign a variable to represent a number of possible workbook's(full paths and name).

However im not too sure about the variable type that i should be using,

Code

Dim SapF As String

Where SapF is the workbook or

Code

Dim SapF As workbook

and is determined by a userform.

Then i want to open this workbook but not sure about the syntax for the variable.
Any help much appreciated.

My code is as follows:

Code

Public Sub CopySapData()
Dim SapF As String
user = sapform.UserBox.Text


If user = Neil Then
SapF = "G:\5762x-Reconciliation\57621-LON Reconciliation\Brokerage\SAP Files 2005\SAP DAILY-UPLOAD - Neil.xls"
    End If


If user = Nick Then
SapF = "G:\5762x-Reconciliation\57621-LON Reconciliation\Brokerage\SAP Files 2005\SAP DAILY-UPLOAD - Nick.xls"
    End If


If user = Sani Then
SapF = "G:\5762x-Reconciliation\57621-LON Reconciliation\Brokerage\SAP Files 2005\SAP DAILY-UPLOAD - SANI.xls"
    End If


If user = JA Then
SapF = "G:\5762x-Reconciliation\57621-LON Reconciliation\Brokerage\SAP Files 2005\SAP DAILY-UPLOAD - JA.xls"
    End If


If user = Jane Then
SapF = "G:\5762x-Reconciliation\57621-LON Reconciliation\Brokerage\SAP Files 2005\SAP DAILY-UPLOAD - Jane N.xls"
    End If


Workbooks.Open (SapF)


End Sub

Display More

lasw10 · Jul 20th 2005

Re: Workbook Variable

OK I would define as string

I would also use a SELECT CASE rather than lots of IFs (more efficient)

also as all files nearly identical you could create a mainstay variable and then have a minor exception

eg

Code

Public Sub CopySapData() 


    Dim SapF As String 
    user = sapform.UserBox.Text 
sapF = "G:\5762x-Reconciliation\57621-LON Reconciliation\Brokerage\SAP Files 2005\SAP DAILY-UPLOAD - "


Select Case UCase(user)
Case "JANE"
    SapF = Sapf & " Jane N.xls"
Case Else
    Sapf = Sapf & user & ".xls"
End Select


Workbooks.Open(sapf)
End Sub

Display More

Neiluk66 · Jul 20th 2005

Re: Workbook Variable

brilliant, some good pointers there.

Not going to be able to try it out yet but thanks v much neil

**norie** · Jul 20th 2005

Re: Workbook Variable

Neil

Why don't you have quotes around the user names eg Neil etc?

If you don't then VBA will regard them as variables.

Also why do you need if/select?

Code

user = sapform.UserBox.Text 
SapF = "G:\5762x-Reconciliation\57621-LON Reconciliation\Brokerage\SAP Files 2005\SAP DAILY-UPLOAD - " & User & ".xls"

lasw10 · Jul 20th 2005

Re: Workbook Variable

because Jane is not the file name - it is Jane N whereas for the other users you could just use the user variable.

**norie** · Jul 20th 2005

Re: Workbook Variable

Never saw that.

What I would do in that case would add a column to the combobox that contains the filename.

Properties could be set for the combobox to show the 'friendly' user name and return the filename.

See the attached.

Workbook Variable

Participate now!

Share