Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to make macro work even a sheet is hidden. | Excel Programming | |||
Please help make this Archiving Macro work... | Excel Programming | |||
Can't make loop macro work - help? | Excel Programming | |||
how do I debug my Excel macro & make it actually WORK? | Excel Programming | |||
How do I make a macro work in one worksheet only | Excel Programming |