Thank you .. this is of use to me.
But, how can I blend this VBA code in with my SQL code. If a variable
(str) is defined in VBA, can I use (str) in my SQL code and continue to use
an absolute reference? I'm working of menu Data - Import External Data -
MS Query (in Excel 2003)
What I was thinking was to switch my SQL code to use a relative reference
(eg .\filename.xls), but first using VBA to establish the current directory
to that of the active workbook.
When I do this, I get an error message the first time the SQL runs after
moving the workbooks to a new folder. When I open and close the SQL code it
then works fine. I wouldn't want the users of my spreadsheet to have to do
this everytime they move the spreadsheets to a new folder.
Please assist some more.... many thanks.
"SteveM" wrote:
> Correction:
>
> Sub ShowPath()
> Dim str As String
> str = ActiveWorkbook.Path
> str = str & "\" & ActiveWorkbook.Name
> MsgBox str
>
> End Sub
>
>
> On Oct 20, 10:19 pm, SteveM <sbm...@vzavenue.net> wrote:
> > Use the Object Path and Name properties. E.g.,
> >
> > Dim str as String
> > str = ActiveWorkbook.Path
> > str = str & AcvtiveWorkbook.Name
> >
> > That will give you both the path and file name of the workbook. Then
> > replace any absolute references with the String (str) value using the
> > "&" to properly concatenate.
> >
> > SteveM
> >
> > On Oct 19, 6:10 pm, RiverGully <RiverGu...@discussions.microsoft.com>
> > wrote:
> >
> > > Hi,
> >
> > > I have several spreadsheets in the same folder that are consolidated into
> > > one spreadsheet using the MS Query feature in Excel. I have reproduced a
> > > part of the query below. The 'FROM' line states an absolute reference:
> > > J:\Budget\filename.xls; is there a way to tell SQL that the file it is
> > > looking for is in the base (current) folder so I can simply state the file
> > > name without the path.
> >
> > > SELECT tblOwnTax.CODE, LEFT(Description,18), MID(DESCRIPTION,30,30),
> > > tblOwnTax.PYR3, tblOwnTax.PYR2, tblOwnTax.PYR1, tblOwnTax.CYR,
> > > tblOwnTax.CYR1, tblOwnTax.CYR2, tblOwnTax.CYR3, tblOwnTax.CYR4,
> > > tblOwnTax.CYR5 FROM `J:\Budget\GOR_Revenue`.tblOwnTax tblOwnTax
> > > WHERE (tblOwnTax.CODE Like '%-GO%')
> >
> > > Union SELECT tblPIPS.CODE, LEFT(Description,18),
> > > MID(DESCRIPTION,30,30), tblPIPS.PYR3, tblPIPS.PYR2, tblPIPS.PYR1,
> > > tblPIPS.CYR, tblPIPS.CYR1, tblPIPS.CYR2, tblPIPS.CYR3, tblPIPS.CYR4,
> > > tblPIPS.CYR5 FROM `J:\Budget\GOR_Revenue`.tblPIPS tblPIPS WHERE
> > > (tblPIPS.CODE Like '%-GO%')
> >
> > > Many thanks in advance.
>
>
>
>
>
|
Similar Threads
