using current directory in the file path

  • Thread starter Thread starter Marles Mcdonald
  • Start date Start date
M

Marles Mcdonald

I need a macro to import data from an Access database into a
spreadsheet. The spreadsheet and database will be in the same directory
but the name of the directory will be different for each use. I have
recorded a macro which does this but the name of the directory is fixed.
I need to have the macro look in the current directory.

A snippet of the maco is :

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=c:\transfer\W5.mdb;DefaultDir=c:\transfer;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5" _
), Array(";")), Destination:=Range("I2"))
.CommandText = Array( _
"SELECT GlzSysQuery.ID, GlzSysQuery.Name, GlzSysQuery.`#
of~Layers`, GlzSysQuery.`Overall~Thickness`, GlzSysQuery.Uval,
GlzSysQuery.SHGC, GlzSysQuery.Tvis" & Chr(13) & "" & Chr(10) & "FROM
`c:\transfer\W5`.GlzSysQuery GlzSysQuery" _
, "" & Chr(13) & "" & Chr(10) & "ORDER BY GlzSysQuery.ID")
.Name = "Query from MS Access Database_36"

I expect that there is a lot of code here I don't need but what I need
to do is to have the DefaultDir = C:\"CURRENT DIRECTORY"

I'm not very knowledgable here any help would be appreciated.

I'm also a newbie any violations of the rules here you have my appology
in advance as well as my thanks in advance.

Marles
 
This macro that I use to backup to the current directory might give you the
idea you need. It makes a sub directory in the current directory if it
doesn't already exist. CurDir is your key word
From vba help

Returns a Variant (String) representing the current path.


Sub Backup() 'kept in personal.xls & assigned to toolbar button
On Error GoTo BackupFile
MkDir CurDir & "\Backup"
BackupFile:
With ActiveWorkbook
MyWB = .Path & "\BACKUP\" & .Name
.SaveCopyAs MyWB
.Save
End With
End Sub
 
Back
Top