change folder
 

John,

As a rule I don't view attachments. So I'm responding without having viewed
your macro. I'm guessing that you either have a PivotTable or QueryTable in
your Excel file.

The directory location of a source file is contained in two places
(Connnection & CommandText). In order to change the source location of the
text file you need to modify these two properties of the PT/QT.

1) PivotCaches(1).Connection = "the_new_connection_string"
2) PivotCaches(1).CommandText = "the_new_query_string"

-OR-

1) QueryTables(1).Connection = "the_new_connection_string"
2) QueryTables(1).CommandText = "the_new_query_string"

You will need to reference the PT/QT appropriately for your situation. Let
me know if you need help here.

Troy


"John Garate" wrote in message
...
Excel 2002, Win XP, SP-2

I created the attached macro to open a tab delimited file located in the
folder C:/EMP3. It works fine.

However, I've changed the folder name on the C drive to EMP4.

I've edited the macro to replace the reference EMP3 to EMP4, but it does
not
run. It seems to hang up at the Refresh BackgroundQuert: = False statement
(at least what's the line the debugger lighlights).

What is the correct way to edit the macro when I change the file location?

change folder
 

Troy,
I'm not sure what you are telling me. I really just starting to learn to use
visual basic. I don't see the querytables commands you refer to. I've pasted
a copy of the macro into this email.



Sub ImportChurchSchedule()
'
' ImportVisitSchedule Macro
' Macro recorded 2/11/2005 by Jag
'

'
Range("B3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\EMP3\Tab files for Excel\EM Church Schedule.tab",
Destination:=Range( _
"B3"))
.Name = "EM Visit Schedule"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub
Sub FormateDateColumn()
'
' FormateDateColumn Macro
' Macro recorded 2/11/2005 by Jag
'

'
Columns("B:B").Select
Selection.NumberFormat = "mm-dd"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.NumberFormat = "mmm-dd"
Range("F14").Select
End Sub
Sub InsertData()
'
' InsertData Macro
' Macro recorded 2/11/2005 by Jag
'

'
Application.Run "ImportVisitSchedule"
Application.Run "SetColumnWidths"
Application.Run "FormateDateColumn"
End Sub

Public Sub Auto_Open()
Application.WindowState = xlMaximized
End Sub



"TroyW" wrote in message
...
John,

As a rule I don't view attachments. So I'm responding without having
viewed your macro. I'm guessing that you either have a PivotTable or
QueryTable in your Excel file.

The directory location of a source file is contained in two places
(Connnection & CommandText). In order to change the source location of the
text file you need to modify these two properties of the PT/QT.

1) PivotCaches(1).Connection = "the_new_connection_string"
2) PivotCaches(1).CommandText = "the_new_query_string"

-OR-

1) QueryTables(1).Connection = "the_new_connection_string"
2) QueryTables(1).CommandText = "the_new_query_string"

You will need to reference the PT/QT appropriately for your situation. Let
me know if you need help here.

Troy


"John Garate" wrote in message
...
Excel 2002, Win XP, SP-2

I created the attached macro to open a tab delimited file located in the
folder C:/EMP3. It works fine.

However, I've changed the folder name on the C drive to EMP4.

I've edited the macro to replace the reference EMP3 to EMP4, but it does
not
run. It seems to hang up at the Refresh BackgroundQuert: = False
statement
(at least what's the line the debugger lighlights).

What is the correct way to edit the macro when I change the file
location?

change folder
 

John,

Disregard the previous information that I gave. It looks like you used the
MacroRecorder to record importing a file using the "Data | Import External
Data | Import Data..." command from the menubar.

You will need to modify the following line of code in
"ImportChurchSchedule":

From: "TEXT;C:\EMP3\Tab files for Excel\EM Church Schedule.tab",

To: "TEXT;C:\EMP4\Tab files for Excel\EM Church Schedule.tab",

Or you could repeat using the MacroRecorder to record importing the file
from it's new location

Troy

"John Garate" wrote in message
...
Troy,
I'm not sure what you are telling me. I really just starting to learn to
use
visual basic. I don't see the querytables commands you refer to. I've
pasted
a copy of the macro into this email.



Sub ImportChurchSchedule()
'
' ImportVisitSchedule Macro
' Macro recorded 2/11/2005 by Jag
'

'
Range("B3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\EMP3\Tab files for Excel\EM Church Schedule.tab",
Destination:=Range( _
"B3"))
.Name = "EM Visit Schedule"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub
Sub FormateDateColumn()
'
' FormateDateColumn Macro
' Macro recorded 2/11/2005 by Jag
'

'
Columns("B:B").Select
Selection.NumberFormat = "mm-dd"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.NumberFormat = "mmm-dd"
Range("F14").Select
End Sub
Sub InsertData()
'
' InsertData Macro
' Macro recorded 2/11/2005 by Jag
'

'
Application.Run "ImportVisitSchedule"
Application.Run "SetColumnWidths"
Application.Run "FormateDateColumn"
End Sub

Public Sub Auto_Open()
Application.WindowState = xlMaximized
End Sub

change folder
 

Troy,
I'ts when I change the line of code to "TEXT;C:\EMP4\Tab files for Excel\EM
Church Schedule.tab",
that the macro hangs. The only thing Ive done is change EMP3 to EMP4.
I know I could repeat using the MacroRecorder, but this is a diddicult
process because of the query. I was hoping to just to edit the code.

John



"TroyW" wrote in message
...
John,

Disregard the previous information that I gave. It looks like you used the
MacroRecorder to record importing a file using the "Data | Import External
Data | Import Data..." command from the menubar.

You will need to modify the following line of code in
"ImportChurchSchedule":

From: "TEXT;C:\EMP3\Tab files for Excel\EM Church Schedule.tab",

To: "TEXT;C:\EMP4\Tab files for Excel\EM Church Schedule.tab",

Or you could repeat using the MacroRecorder to record importing the file
from it's new location

Troy

"John Garate" wrote in message
...
Troy,
I'm not sure what you are telling me. I really just starting to learn to
use
visual basic. I don't see the querytables commands you refer to. I've
pasted
a copy of the macro into this email.



Sub ImportChurchSchedule()
'
' ImportVisitSchedule Macro
' Macro recorded 2/11/2005 by Jag
'

'
Range("B3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\EMP3\Tab files for Excel\EM Church Schedule.tab",
Destination:=Range( _
"B3"))
.Name = "EM Visit Schedule"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub
Sub FormateDateColumn()
'
' FormateDateColumn Macro
' Macro recorded 2/11/2005 by Jag
'

'
Columns("B:B").Select
Selection.NumberFormat = "mm-dd"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Selection.NumberFormat = "mmm-dd"
Range("F14").Select
End Sub
Sub InsertData()
'
' InsertData Macro
' Macro recorded 2/11/2005 by Jag
'

'
Application.Run "ImportVisitSchedule"
Application.Run "SetColumnWidths"
Application.Run "FormateDateColumn"
End Sub

Public Sub Auto_Open()
Application.WindowState = xlMaximized
End Sub

change folder
 

John,

I've tested changing: "TEXT;C:\EMP4\" and it works for me. What is the exact
error message that you are receiving?

Troy

"John Garate" wrote in message
...
Troy,
I'ts when I change the line of code to "TEXT;C:\EMP4\Tab files for
Excel\EM Church Schedule.tab",
that the macro hangs. The only thing Ive done is change EMP3 to EMP4.
I know I could repeat using the MacroRecorder, but this is a diddicult
process because of the query. I was hoping to just to edit the code.

John

change folder
 

TroyW,
I finally got it working. The problem was that the macro is in a template
and I was changing the macro in a worksheet created by the template. I
should have been changing the macro in the template. I thought the changes
you make to a macro in a worksheet updates the macro in the template.

Thanks for all your help


John
"TroyW" wrote in message
...
John,

I've tested changing: "TEXT;C:\EMP4\" and it works for me. What is the
exact error message that you are receiving?

Troy

"John Garate" wrote in message
...
Troy,
I'ts when I change the line of code to "TEXT;C:\EMP4\Tab files for
Excel\EM Church Schedule.tab",
that the macro hangs. The only thing Ive done is change EMP3 to EMP4.
I know I could repeat using the MacroRecorder, but this is a diddicult
process because of the query. I was hoping to just to edit the code.

John

change folder
 

John,

Glad you figured it out. Thanks for clearing up the mystery. Cheers.

Troy

"John Garate" wrote in message
...
TroyW,
I finally got it working. The problem was that the macro is in a template
and I was changing the macro in a worksheet created by the template. I
should have been changing the macro in the template. I thought the changes
you make to a macro in a worksheet updates the macro in the template.

Thanks for all your help


John
"TroyW" wrote in message
...
John,

I've tested changing: "TEXT;C:\EMP4\" and it works for me. What is the
exact error message that you are receiving?

Troy

"John Garate" wrote in message
...
Troy,
I'ts when I change the line of code to "TEXT;C:\EMP4\Tab files for
Excel\EM Church Schedule.tab",
that the macro hangs. The only thing Ive done is change EMP3 to EMP4.
I know I could repeat using the MacroRecorder, but this is a diddicult
process because of the query. I was hoping to just to edit the code.

John