Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Odawg
 
Posts: n/a
Default Open to default directory in Excel

Hello All,

Thanks for all of the help, I really do appreciate it. I just have
one more question. The following macro below allows me to import a
text file into an existing excel spredsheet. Is it also possible for
it to default to a specific directory to look for the text file.

I am still fairly new to writing macros in excel and for that matter
VBA and I really do appreciate it.

'----------------------------------------------------------------------------------
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

'--------------------------------------------------------------------

Public Sub ImportProfileLst()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub
  #2   Report Post  
JMB
 
Posts: n/a
Default

You can make the FName argument optional and, if omitted, default to a
predetermined value. I think you will have to change the order of your
arguments so that the required argument precedes the optional argument (or
you can make Sep optional as well and not set a default value for it-then you
can leave the order alone).

On a related note - you can also use IsMissing as a test to see if the
optional argument was provided.

See VBA help for more info on optional and named arguments.


Public Sub ImportTextFile(Sep As String, Optional FName As String =
"C:\Excel")


"Odawg" wrote:

Hello All,

Thanks for all of the help, I really do appreciate it. I just have
one more question. The following macro below allows me to import a
text file into an existing excel spredsheet. Is it also possible for
it to default to a specific directory to look for the text file.

I am still fairly new to writing macros in excel and for that matter
VBA and I really do appreciate it.

'----------------------------------------------------------------------------------
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

'--------------------------------------------------------------------

Public Sub ImportProfileLst()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub

  #3   Report Post  
JMB
 
Posts: n/a
Default

I'm sorry. You said default to a specific folder, not a specific file.

In that case, precede your GetOpenFileName method with

ChDir "C:\Excel"

or whatever directory you want.


"Odawg" wrote:

Hello All,

Thanks for all of the help, I really do appreciate it. I just have
one more question. The following macro below allows me to import a
text file into an existing excel spredsheet. Is it also possible for
it to default to a specific directory to look for the text file.

I am still fairly new to writing macros in excel and for that matter
VBA and I really do appreciate it.

'----------------------------------------------------------------------------------
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

'--------------------------------------------------------------------

Public Sub ImportProfileLst()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub

  #4   Report Post  
Odawg
 
Posts: n/a
Default

Hey JB,

I tried that but it still opens to the following default directory:
My Documents

Just to make sure I understood your response you said the following:

ChDir = "\\Servername\sharepoint\ExcelTextData\"
FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")

I also thought that this would work but it didn't.


On Wed, 12 Oct 2005 18:52:02 -0700, JMB
wrote:

I'm sorry. You said default to a specific folder, not a specific file.

In that case, precede your GetOpenFileName method with

ChDir "C:\Excel"

or whatever directory you want.


"Odawg" wrote:

Hello All,

Thanks for all of the help, I really do appreciate it. I just have
one more question. The following macro below allows me to import a
text file into an existing excel spredsheet. Is it also possible for
it to default to a specific directory to look for the text file.

I am still fairly new to writing macros in excel and for that matter
VBA and I really do appreciate it.

'----------------------------------------------------------------------------------
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

'--------------------------------------------------------------------

Public Sub ImportProfileLst()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub


  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

The chdrive won't work on network drives.

But you can use an API call:

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub testme02()

Dim mySavedPath As String

mySavedPath = CurDir

ChDirNet "\\Servername\sharepoint\ExcelTextData"

'do your stuff

ChDirNet mySavedPath

End Sub

This actually works with mapped drives, too.


Odawg wrote:

Hey JB,

I tried that but it still opens to the following default directory:
My Documents

Just to make sure I understood your response you said the following:

ChDir = "\\Servername\sharepoint\ExcelTextData\"
FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")

I also thought that this would work but it didn't.

On Wed, 12 Oct 2005 18:52:02 -0700, JMB
wrote:

I'm sorry. You said default to a specific folder, not a specific file.

In that case, precede your GetOpenFileName method with

ChDir "C:\Excel"

or whatever directory you want.


"Odawg" wrote:

Hello All,

Thanks for all of the help, I really do appreciate it. I just have
one more question. The following macro below allows me to import a
text file into an existing excel spredsheet. Is it also possible for
it to default to a specific directory to look for the text file.

I am still fairly new to writing macros in excel and for that matter
VBA and I really do appreciate it.

'----------------------------------------------------------------------------------
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

'--------------------------------------------------------------------

Public Sub ImportProfileLst()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub


--

Dave Peterson


  #6   Report Post  
Odawg
 
Posts: n/a
Default

Hey Dave,

Thanks for all of the help. It works like a charm. Is there an Excel
book that you can recommend that gives me an some clear insight on VBA
and Macros in Excel.

Very Much Appreciated,


On Thu, 13 Oct 2005 07:39:11 -0500, Dave Peterson
wrote:

The chdrive won't work on network drives.

But you can use an API call:

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub testme02()

Dim mySavedPath As String

mySavedPath = CurDir

ChDirNet "\\Servername\sharepoint\ExcelTextData"

'do your stuff

ChDirNet mySavedPath

End Sub

This actually works with mapped drives, too.


Odawg wrote:

Hey JB,

I tried that but it still opens to the following default directory:
My Documents

Just to make sure I understood your response you said the following:

ChDir = "\\Servername\sharepoint\ExcelTextData\"
FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")

I also thought that this would work but it didn't.

On Wed, 12 Oct 2005 18:52:02 -0700, JMB
wrote:

I'm sorry. You said default to a specific folder, not a specific file.

In that case, precede your GetOpenFileName method with

ChDir "C:\Excel"

or whatever directory you want.


"Odawg" wrote:

Hello All,

Thanks for all of the help, I really do appreciate it. I just have
one more question. The following macro below allows me to import a
text file into an existing excel spredsheet. Is it also possible for
it to default to a specific directory to look for the text file.

I am still fairly new to writing macros in excel and for that matter
VBA and I really do appreciate it.

'----------------------------------------------------------------------------------
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

'--------------------------------------------------------------------

Public Sub ImportProfileLst()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub


  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

Debra Dalgleish has a list at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. See if you can find them in your
local bookstore and you can choose what one you like best.

Odawg wrote:

Hey Dave,

Thanks for all of the help. It works like a charm. Is there an Excel
book that you can recommend that gives me an some clear insight on VBA
and Macros in Excel.

Very Much Appreciated,

On Thu, 13 Oct 2005 07:39:11 -0500, Dave Peterson
wrote:

The chdrive won't work on network drives.

But you can use an API call:

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub testme02()

Dim mySavedPath As String

mySavedPath = CurDir

ChDirNet "\\Servername\sharepoint\ExcelTextData"

'do your stuff

ChDirNet mySavedPath

End Sub

This actually works with mapped drives, too.


Odawg wrote:

Hey JB,

I tried that but it still opens to the following default directory:
My Documents

Just to make sure I understood your response you said the following:

ChDir = "\\Servername\sharepoint\ExcelTextData\"
FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")

I also thought that this would work but it didn't.

On Wed, 12 Oct 2005 18:52:02 -0700, JMB
wrote:

I'm sorry. You said default to a specific folder, not a specific file.

In that case, precede your GetOpenFileName method with

ChDir "C:\Excel"

or whatever directory you want.


"Odawg" wrote:

Hello All,

Thanks for all of the help, I really do appreciate it. I just have
one more question. The following macro below allows me to import a
text file into an existing excel spredsheet. Is it also possible for
it to default to a specific directory to look for the text file.

I am still fairly new to writing macros in excel and for that matter
VBA and I really do appreciate it.

'----------------------------------------------------------------------------------
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

'--------------------------------------------------------------------

Public Sub ImportProfileLst()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub


--

Dave Peterson
  #8   Report Post  
JMB
 
Posts: n/a
Default

Well, I don't think there should be an " = " sign in ChDir, but that's a moot
point since you are wanting to specify a different (network) drive.

Try the code that Dave as suggested. Thanks Dave!

"Odawg" wrote:

Hey JB,

I tried that but it still opens to the following default directory:
My Documents

Just to make sure I understood your response you said the following:

ChDir = "\\Servername\sharepoint\ExcelTextData\"
FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")

I also thought that this would work but it didn't.


On Wed, 12 Oct 2005 18:52:02 -0700, JMB
wrote:

I'm sorry. You said default to a specific folder, not a specific file.

In that case, precede your GetOpenFileName method with

ChDir "C:\Excel"

or whatever directory you want.


"Odawg" wrote:

Hello All,

Thanks for all of the help, I really do appreciate it. I just have
one more question. The following macro below allows me to import a
text file into an existing excel spredsheet. Is it also possible for
it to default to a specific directory to look for the text file.

I am still fairly new to writing macros in excel and for that matter
VBA and I really do appreciate it.

'----------------------------------------------------------------------------------
Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) < Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos = 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

'--------------------------------------------------------------------

Public Sub ImportProfileLst()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(FileFilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I open a Quattro Pro 7.0 file in Excel 2003 cpatrick83 Excel Discussion (Misc queries) 0 August 22nd 05 09:41 PM
How do I find a file/spreadsheet that Excel says is Already open but I can't find it? nwtrader8 Excel Discussion (Misc queries) 5 June 21st 05 02:16 PM
Excel startup switches Randy Excel Discussion (Misc queries) 9 June 14th 05 10:27 PM
Cannot open Excel attachment from e-mail when Excel is already ope John P. Excel Discussion (Misc queries) 1 March 1st 05 09:35 PM
How Do I open an excel file without Excel Viewer support CocoriteBallGiants Excel Discussion (Misc queries) 2 February 4th 05 11:50 PM


All times are GMT +1. The time now is 09:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"