Make Macro Work On A Different PC
 

Hello, I've got this macro:

Copy and paste values into text file

Const OUTPUTFILENAME = "C:\Users\user\Documents\JobsTaxQualificationsBusi ness\CP+\CPPOffendersText.txt"
Dim cell As Range, ro As Range, outP As String
fnum = FreeFile
Open OUTPUTFILENAME For Output As fnum
For Each ro In Range("A1:J" & Cells.End(xlDown).Row).Rows
outP = ""
allBlank = True
For Each cell In ro.Cells
If cell.Column < 1 Then outP = outP & vbTab
outP = outP & cell.Value
If Len(Trim$(cell.Value)) Then allBlank = False
Next
If allBlank Then Exit For
Print #fnum, outP
Next
Close fnum

which, as it says, c & p values into a text file.

I want to run it on a different PC where the filepath C:\Users\user\Documents\JobsTaxQualificationsBusin ess\CP+
doesn't exist.

What's the best way of getting this to work on any PC? Change the filepath? Change the macro so that it will work anywhere?

Make Macro Work On A Different PC
 

Hello, I've got this macro:

Copy and paste values into text file

Const OUTPUTFILENAME =
"C:\Users\user\Documents\JobsTaxQualificationsBusi ness\CP+\CPPOffendersText.txt"
Dim cell As Range, ro As Range, outP As String fnum =
FreeFile Open OUTPUTFILENAME For Output As fnum
For Each ro In Range("A1:J" & Cells.End(xlDown).Row).Rows
outP = ""
allBlank = True
For Each cell In ro.Cells
If cell.Column < 1 Then outP = outP & vbTab
outP = outP & cell.Value
If Len(Trim$(cell.Value)) Then allBlank = False
Next
If allBlank Then Exit For
Print #fnum, outP
Next
Close fnum

which, as it says, c & p values into a text file.

I want to run it on a different PC where the filepath
C:\Users\user\Documents\JobsTaxQualificationsBusin ess\CP+ doesn't
exist.

What's the best way of getting this to work on any PC? Change the
filepath? Change the macro so that it will work anywhere?

Yes.., impliment a browser dialog to return the file and its path! This
can be GetSaveAsFilename or GetOpenFilename.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Make Macro Work On A Different PC
 

On Monday, November 3, 2014 3:29:00 PM UTC-6, robzrob wrote:
Hello, I've got this macro:

Copy and paste values into text file

Const OUTPUTFILENAME = "C:\Users\user\Documents\JobsTaxQualificationsBusi ness\CP+\CPPOffendersText.txt"
Dim cell As Range, ro As Range, outP As String
fnum = FreeFile
Open OUTPUTFILENAME For Output As fnum
For Each ro In Range("A1:J" & Cells.End(xlDown).Row).Rows
outP = ""
allBlank = True
For Each cell In ro.Cells
If cell.Column < 1 Then outP = outP & vbTab
outP = outP & cell.Value
If Len(Trim$(cell.Value)) Then allBlank = False
Next
If allBlank Then Exit For
Print #fnum, outP
Next
Close fnum

which, as it says, c & p values into a text file.

I want to run it on a different PC where the filepath C:\Users\user\Documents\JobsTaxQualificationsBusin ess\CP+
doesn't exist.

What's the best way of getting this to work on any PC? Change the filepath? Change the macro so that it will work anywhere?


If you don't have to use that exact file path, you can use the current file path of the workbook, and the macro will work wherever it is.


OUTPUTFILENAME=Thisworkbook.path & "\CPPOffendersText.txt"

I don't know if this will help. I use macros that write to notepad files, and this is how I use it on other computers.

Make Macro Work On A Different PC
 

On Tuesday, 4 November 2014 05:35:27 UTC, GS wrote:
Hello, I've got this macro:

Copy and paste values into text file

Const OUTPUTFILENAME =
"C:\Users\user\Documents\JobsTaxQualificationsBusi ness\CP+\CPPOffendersText.txt"
Dim cell As Range, ro As Range, outP As String fnum =
FreeFile Open OUTPUTFILENAME For Output As fnum
For Each ro In Range("A1:J" & Cells.End(xlDown).Row).Rows
outP = ""
allBlank = True
For Each cell In ro.Cells
If cell.Column < 1 Then outP = outP & vbTab
outP = outP & cell.Value
If Len(Trim$(cell.Value)) Then allBlank = False
Next
If allBlank Then Exit For
Print #fnum, outP
Next
Close fnum

which, as it says, c & p values into a text file.

I want to run it on a different PC where the filepath
C:\Users\user\Documents\JobsTaxQualificationsBusin ess\CP+ doesn't
exist.

What's the best way of getting this to work on any PC? Change the
filepath? Change the macro so that it will work anywhere?

Yes.., impliment a browser dialog to return the file and its path! This
can be GetSaveAsFilename or GetOpenFilename.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks. Not sure how to use this, but will look around for examples.

Make Macro Work On A Different PC
 

On Sunday, 9 November 2014 01:38:36 UTC, Andrew wrote:
On Monday, November 3, 2014 3:29:00 PM UTC-6, robzrob wrote:
Hello, I've got this macro:

Copy and paste values into text file

Const OUTPUTFILENAME = "C:\Users\user\Documents\JobsTaxQualificationsBusi ness\CP+\CPPOffendersText.txt"
Dim cell As Range, ro As Range, outP As String
fnum = FreeFile
Open OUTPUTFILENAME For Output As fnum
For Each ro In Range("A1:J" & Cells.End(xlDown).Row).Rows
outP = ""
allBlank = True
For Each cell In ro.Cells
If cell.Column < 1 Then outP = outP & vbTab
outP = outP & cell.Value
If Len(Trim$(cell.Value)) Then allBlank = False
Next
If allBlank Then Exit For
Print #fnum, outP
Next
Close fnum

which, as it says, c & p values into a text file.

I want to run it on a different PC where the filepath C:\Users\user\Documents\JobsTaxQualificationsBusin ess\CP+
doesn't exist.

What's the best way of getting this to work on any PC? Change the filepath? Change the macro so that it will work anywhere?


If you don't have to use that exact file path, you can use the current file path of the workbook, and the macro will work wherever it is.


OUTPUTFILENAME=Thisworkbook.path & "\CPPOffendersText.txt"

I don't know if this will help. I use macros that write to notepad files, and this is how I use it on other computers.


Thanks. Not sure how to use this, but will have a go.

Make Macro Work On A Different PC
 

Have a look at...

GetOpenFilename
GetSaveAsFilename

...which are fully documented in the VBA help files.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion