Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for file name and paste contents of active sheet
I have a template that collates and summaries data from three data sheets
created by our database. Each file is saved as a job number followed by GL, L or J. For example, General Ledger data is exported to a file called 1234GL.xls, Labour data is exported to a file called 1234L.xls, etc. Each file has only one sheet with the same name as the file name without the xls extension. I have destination sheets in a Job Summary Template called "Ledger", "Labour" and 'JobCard". Macros are triggered when the destination sheet is updated. Currently users are taken via a macro to the file directory and prompted to select a data file. The entire contents of the ActiveSheet is copied to the sheet allocated to that macro (€śLedger€ť, €śLabour€ť etc). Unfortunately, the user must close the three open data sheets before continuing to the next job summary. Would it be possible for the user to be prompted for the job number, have this added to the €śGL, €śL or €śJ€ť and the file selected for them, and the contents of the ActiveSheet copied to the named target sheet and the data file closed. If the prompt is too difficult I would be happy with being able to close the data file after the data is copied to the named target sheet. -- Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for file name and paste contents of active sheet
I'm not sure from your description if you are just asking to close the 3
files, or also asking about a better way of opening the files. Everything you are asking is possible. I think upgrading your poresent macros rather than add ing new macros is the best way of going. It would be easier if you posted the old macros so they can be modified. "Jim G" wrote: I have a template that collates and summaries data from three data sheets created by our database. Each file is saved as a job number followed by GL, L or J. For example, General Ledger data is exported to a file called 1234GL.xls, Labour data is exported to a file called 1234L.xls, etc. Each file has only one sheet with the same name as the file name without the xls extension. I have destination sheets in a Job Summary Template called "Ledger", "Labour" and 'JobCard". Macros are triggered when the destination sheet is updated. Currently users are taken via a macro to the file directory and prompted to select a data file. The entire contents of the ActiveSheet is copied to the sheet allocated to that macro (€śLedger€ť, €śLabour€ť etc). Unfortunately, the user must close the three open data sheets before continuing to the next job summary. Would it be possible for the user to be prompted for the job number, have this added to the €śGL, €śL or €śJ€ť and the file selected for them, and the contents of the ActiveSheet copied to the named target sheet and the data file closed. If the prompt is too difficult I would be happy with being able to close the data file after the data is copied to the named target sheet. -- Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for file name and paste contents of active sheet
Thanks Joel, I was hoping to have the data files close after the data was
pasted. However, I was being a bit opportunistic asking if the user could be prompted for the file name (job No) to save them scolling through dozens in the list-nice but not essential. The following macro is activated on file open to select the first data file. For clarity I'll post the others separtely. They are activated via button. Sub OpenLedger() Dim Msg, Style, Title, Help, Ctxt, Response, MyString Msg = "Select ""YES"" to proceed to Open a Job Ledger Data File, ""NO"" to view Current File only" Style = vbYesNoCancel + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Open a New Ledger Data File " ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbYes Then Dim myFileName As Variant Dim wkbk As Workbook Dim MyPath As String MyPath = "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" MsgBox "Select a Job GL File to use" ChDrive "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" 'ChDir "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" ChDir MyPath '---didn't seem to work on it's own- best with ChDrive as well myFileName = Application.GetOpenFilename("Excel Files, *.xls") If myFileName = False Then Exit Sub 'user hit cancel End If Set wkbk = Workbooks.Open(Filename:=myFileName) Else Exit Sub End If ActiveSheet.Cells.Select Selection.Copy Application.DisplayAlerts = False 'wkbk.Close SaveChanges:=False '---attempts to make it work 'ActveWindow.Close '---Attempts to make it work Windows("JobCost Template V3.3.xls").Activate Sheets("Ledger").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Summary").Select Range("A16").Select Application.DisplayAlerts = True End Sub -- Jim "Joel" wrote: I'm not sure from your description if you are just asking to close the 3 files, or also asking about a better way of opening the files. Everything you are asking is possible. I think upgrading your poresent macros rather than add ing new macros is the best way of going. It would be easier if you posted the old macros so they can be modified. "Jim G" wrote: I have a template that collates and summaries data from three data sheets created by our database. Each file is saved as a job number followed by GL, L or J. For example, General Ledger data is exported to a file called 1234GL.xls, Labour data is exported to a file called 1234L.xls, etc. Each file has only one sheet with the same name as the file name without the xls extension. I have destination sheets in a Job Summary Template called "Ledger", "Labour" and 'JobCard". Macros are triggered when the destination sheet is updated. Currently users are taken via a macro to the file directory and prompted to select a data file. The entire contents of the ActiveSheet is copied to the sheet allocated to that macro (€śLedger€ť, €śLabour€ť etc). Unfortunately, the user must close the three open data sheets before continuing to the next job summary. Would it be possible for the user to be prompted for the job number, have this added to the €śGL, €śL or €śJ€ť and the file selected for them, and the contents of the ActiveSheet copied to the named target sheet and the data file closed. If the prompt is too difficult I would be happy with being able to close the data file after the data is copied to the named target sheet. -- Jim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for file name and paste contents of active sheet
Sorry Joel, I mentioned that I would post the other macros. They are
identical to this except for the message prompt and destination sheets which I should be able to sort out myself. Cheers -- Jim "Jim G" wrote: Thanks Joel, I was hoping to have the data files close after the data was pasted. However, I was being a bit opportunistic asking if the user could be prompted for the file name (job No) to save them scolling through dozens in the list-nice but not essential. The following macro is activated on file open to select the first data file. For clarity I'll post the others separtely. They are activated via button. Sub OpenLedger() Dim Msg, Style, Title, Help, Ctxt, Response, MyString Msg = "Select ""YES"" to proceed to Open a Job Ledger Data File, ""NO"" to view Current File only" Style = vbYesNoCancel + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Open a New Ledger Data File " ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbYes Then Dim myFileName As Variant Dim wkbk As Workbook Dim MyPath As String MyPath = "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" MsgBox "Select a Job GL File to use" ChDrive "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" 'ChDir "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" ChDir MyPath '---didn't seem to work on it's own- best with ChDrive as well myFileName = Application.GetOpenFilename("Excel Files, *.xls") If myFileName = False Then Exit Sub 'user hit cancel End If Set wkbk = Workbooks.Open(Filename:=myFileName) Else Exit Sub End If ActiveSheet.Cells.Select Selection.Copy Application.DisplayAlerts = False 'wkbk.Close SaveChanges:=False '---attempts to make it work 'ActveWindow.Close '---Attempts to make it work Windows("JobCost Template V3.3.xls").Activate Sheets("Ledger").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Summary").Select Range("A16").Select Application.DisplayAlerts = True End Sub -- Jim "Joel" wrote: I'm not sure from your description if you are just asking to close the 3 files, or also asking about a better way of opening the files. Everything you are asking is possible. I think upgrading your poresent macros rather than add ing new macros is the best way of going. It would be easier if you posted the old macros so they can be modified. "Jim G" wrote: I have a template that collates and summaries data from three data sheets created by our database. Each file is saved as a job number followed by GL, L or J. For example, General Ledger data is exported to a file called 1234GL.xls, Labour data is exported to a file called 1234L.xls, etc. Each file has only one sheet with the same name as the file name without the xls extension. I have destination sheets in a Job Summary Template called "Ledger", "Labour" and 'JobCard". Macros are triggered when the destination sheet is updated. Currently users are taken via a macro to the file directory and prompted to select a data file. The entire contents of the ActiveSheet is copied to the sheet allocated to that macro (€śLedger€ť, €śLabour€ť etc). Unfortunately, the user must close the three open data sheets before continuing to the next job summary. Would it be possible for the user to be prompted for the job number, have this added to the €śGL, €śL or €śJ€ť and the file selected for them, and the contents of the ActiveSheet copied to the named target sheet and the data file closed. If the prompt is too difficult I would be happy with being able to close the data file after the data is copied to the named target sheet. -- Jim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for file name and paste contents of active sheet
If you uncomment this line in the code it does close the workbook. If you
look at the VBA project window it is still listed but the book is really closed. wkbk.Close SaveChanges:=False ' this does work You can also close workbooks by name workbooks("book1.xls").close When you close a workbook by name youmust remove the path name and only include the filename with the extension. There are a few ways of doing this. I sometimes use this code Filename = "c:\temp\workbook1.xls" Do While InStr(Filename, "\") 0 Filename = Mid(Filename, InStr(Filename, "\") + 1) Loop "Jim G" wrote: Sorry Joel, I mentioned that I would post the other macros. They are identical to this except for the message prompt and destination sheets which I should be able to sort out myself. Cheers -- Jim "Jim G" wrote: Thanks Joel, I was hoping to have the data files close after the data was pasted. However, I was being a bit opportunistic asking if the user could be prompted for the file name (job No) to save them scolling through dozens in the list-nice but not essential. The following macro is activated on file open to select the first data file. For clarity I'll post the others separtely. They are activated via button. Sub OpenLedger() Dim Msg, Style, Title, Help, Ctxt, Response, MyString Msg = "Select ""YES"" to proceed to Open a Job Ledger Data File, ""NO"" to view Current File only" Style = vbYesNoCancel + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Open a New Ledger Data File " ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbYes Then Dim myFileName As Variant Dim wkbk As Workbook Dim MyPath As String MyPath = "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" MsgBox "Select a Job GL File to use" ChDrive "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" 'ChDir "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" ChDir MyPath '---didn't seem to work on it's own- best with ChDrive as well myFileName = Application.GetOpenFilename("Excel Files, *.xls") If myFileName = False Then Exit Sub 'user hit cancel End If Set wkbk = Workbooks.Open(Filename:=myFileName) Else Exit Sub End If ActiveSheet.Cells.Select Selection.Copy Application.DisplayAlerts = False 'wkbk.Close SaveChanges:=False '---attempts to make it work 'ActveWindow.Close '---Attempts to make it work Windows("JobCost Template V3.3.xls").Activate Sheets("Ledger").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Summary").Select Range("A16").Select Application.DisplayAlerts = True End Sub -- Jim "Joel" wrote: I'm not sure from your description if you are just asking to close the 3 files, or also asking about a better way of opening the files. Everything you are asking is possible. I think upgrading your poresent macros rather than add ing new macros is the best way of going. It would be easier if you posted the old macros so they can be modified. "Jim G" wrote: I have a template that collates and summaries data from three data sheets created by our database. Each file is saved as a job number followed by GL, L or J. For example, General Ledger data is exported to a file called 1234GL.xls, Labour data is exported to a file called 1234L.xls, etc. Each file has only one sheet with the same name as the file name without the xls extension. I have destination sheets in a Job Summary Template called "Ledger", "Labour" and 'JobCard". Macros are triggered when the destination sheet is updated. Currently users are taken via a macro to the file directory and prompted to select a data file. The entire contents of the ActiveSheet is copied to the sheet allocated to that macro (€śLedger€ť, €śLabour€ť etc). Unfortunately, the user must close the three open data sheets before continuing to the next job summary. Would it be possible for the user to be prompted for the job number, have this added to the €śGL, €śL or €śJ€ť and the file selected for them, and the contents of the ActiveSheet copied to the named target sheet and the data file closed. If the prompt is too difficult I would be happy with being able to close the data file after the data is copied to the named target sheet. -- Jim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for file name and paste contents of active sheet
Thanks again Joel,
I had tried this earlier but it fails at; "Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False" I assumed this to be due to the clipboard clearing before the paste instruction. I can see that if I had the name of the data file I could paste before closing. Unfortunately, every file name will be different. Maybe if the user passed the file name via a prompt at opening it could be used in the wbk.Close command. What do you think? -- Jim "Joel" wrote: If you uncomment this line in the code it does close the workbook. If you look at the VBA project window it is still listed but the book is really closed. wkbk.Close SaveChanges:=False ' this does work You can also close workbooks by name workbooks("book1.xls").close When you close a workbook by name youmust remove the path name and only include the filename with the extension. There are a few ways of doing this. I sometimes use this code Filename = "c:\temp\workbook1.xls" Do While InStr(Filename, "\") 0 Filename = Mid(Filename, InStr(Filename, "\") + 1) Loop "Jim G" wrote: Sorry Joel, I mentioned that I would post the other macros. They are identical to this except for the message prompt and destination sheets which I should be able to sort out myself. Cheers -- Jim "Jim G" wrote: Thanks Joel, I was hoping to have the data files close after the data was pasted. However, I was being a bit opportunistic asking if the user could be prompted for the file name (job No) to save them scolling through dozens in the list-nice but not essential. The following macro is activated on file open to select the first data file. For clarity I'll post the others separtely. They are activated via button. Sub OpenLedger() Dim Msg, Style, Title, Help, Ctxt, Response, MyString Msg = "Select ""YES"" to proceed to Open a Job Ledger Data File, ""NO"" to view Current File only" Style = vbYesNoCancel + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Open a New Ledger Data File " ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbYes Then Dim myFileName As Variant Dim wkbk As Workbook Dim MyPath As String MyPath = "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" MsgBox "Select a Job GL File to use" ChDrive "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" 'ChDir "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" ChDir MyPath '---didn't seem to work on it's own- best with ChDrive as well myFileName = Application.GetOpenFilename("Excel Files, *.xls") If myFileName = False Then Exit Sub 'user hit cancel End If Set wkbk = Workbooks.Open(Filename:=myFileName) Else Exit Sub End If ActiveSheet.Cells.Select Selection.Copy Application.DisplayAlerts = False 'wkbk.Close SaveChanges:=False '---attempts to make it work 'ActveWindow.Close '---Attempts to make it work Windows("JobCost Template V3.3.xls").Activate Sheets("Ledger").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Summary").Select Range("A16").Select Application.DisplayAlerts = True End Sub -- Jim "Joel" wrote: I'm not sure from your description if you are just asking to close the 3 files, or also asking about a better way of opening the files. Everything you are asking is possible. I think upgrading your poresent macros rather than add ing new macros is the best way of going. It would be easier if you posted the old macros so they can be modified. "Jim G" wrote: I have a template that collates and summaries data from three data sheets created by our database. Each file is saved as a job number followed by GL, L or J. For example, General Ledger data is exported to a file called 1234GL.xls, Labour data is exported to a file called 1234L.xls, etc. Each file has only one sheet with the same name as the file name without the xls extension. I have destination sheets in a Job Summary Template called "Ledger", "Labour" and 'JobCard". Macros are triggered when the destination sheet is updated. Currently users are taken via a macro to the file directory and prompted to select a data file. The entire contents of the ActiveSheet is copied to the sheet allocated to that macro (€śLedger€ť, €śLabour€ť etc). Unfortunately, the user must close the three open data sheets before continuing to the next job summary. Would it be possible for the user to be prompted for the job number, have this added to the €śGL, €śL or €śJ€ť and the file selected for them, and the contents of the ActiveSheet copied to the named target sheet and the data file closed. If the prompt is too difficult I would be happy with being able to close the data file after the data is copied to the named target sheet. -- Jim |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt for file name and paste contents of active sheet
I've changed the macro that runs on open to that below. It runs fine.
Since this runs first I have changed the other two Macros to pick up the Job Number from the Ledger Sheet (sFilename = Sheets("Ledger").Range("I17").Value). I can now run the three one after the other on open. Probably not as efficient as it could be but it works. The only thing to add now is some error handling when a data file is not found. As you can see, I'm no expert and rely on you guys a lot, so thanks (everyone) for all the good ideas.---It's almost idiot proofed! ----------- Sub OpenLedger() Dim Msg, Style, Title, Help, Ctxt, Response, MyString Msg = "Select ""YES"" to proceed to Open a Job Ledger Data File, ""NO"" to view Current File only" Style = vbYesNoCancel + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Open a New Ledger Data File " ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbYes Then Dim myFileName As Variant Dim wkbk As Workbook Dim MyPath As String Dim sFilename As String Dim fExitDo As Boolean Dim sFileType As String Dim sFileOpen As String MyPath = "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji\" ChDrive "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" ChDir MyPath '---didn't seem to work on it's own- best with ChDrive as well sFilename = InputBox("Please Provide the Job Number Only") sFileType = "GL" sFileOpen = MyPath & sFilename & sFileType & ".xls" fExitDo = False If sFilename = "" Then Exit Sub 'user hit cancel End If Set wkbk = Workbooks.Open(Filename:=sFileOpen) Else Exit Sub End If ActiveSheet.Cells.Select Selection.Copy Application.DisplayAlerts = False Windows("JobCost Template V3.3.xls").Activate Sheets("Ledger").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False wkbk.Close Savechanges = False Application.DisplayAlerts = True End Sub -- Jim "Joel" wrote: If you uncomment this line in the code it does close the workbook. If you look at the VBA project window it is still listed but the book is really closed. wkbk.Close SaveChanges:=False ' this does work You can also close workbooks by name workbooks("book1.xls").close When you close a workbook by name youmust remove the path name and only include the filename with the extension. There are a few ways of doing this. I sometimes use this code Filename = "c:\temp\workbook1.xls" Do While InStr(Filename, "\") 0 Filename = Mid(Filename, InStr(Filename, "\") + 1) Loop "Jim G" wrote: Sorry Joel, I mentioned that I would post the other macros. They are identical to this except for the message prompt and destination sheets which I should be able to sort out myself. Cheers -- Jim "Jim G" wrote: Thanks Joel, I was hoping to have the data files close after the data was pasted. However, I was being a bit opportunistic asking if the user could be prompted for the file name (job No) to save them scolling through dozens in the list-nice but not essential. The following macro is activated on file open to select the first data file. For clarity I'll post the others separtely. They are activated via button. Sub OpenLedger() Dim Msg, Style, Title, Help, Ctxt, Response, MyString Msg = "Select ""YES"" to proceed to Open a Job Ledger Data File, ""NO"" to view Current File only" Style = vbYesNoCancel + vbCritical + vbDefaultButton2 ' Define buttons. Title = "Open a New Ledger Data File " ' Define title. Response = MsgBox(Msg, Style, Title, Help, Ctxt) If Response = vbYes Then Dim myFileName As Variant Dim wkbk As Workbook Dim MyPath As String MyPath = "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" MsgBox "Select a Job GL File to use" ChDrive "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" 'ChDir "N:\MINING\PLANT\SERVICES\AXAPTA\Job Analysis\Seraji" ChDir MyPath '---didn't seem to work on it's own- best with ChDrive as well myFileName = Application.GetOpenFilename("Excel Files, *.xls") If myFileName = False Then Exit Sub 'user hit cancel End If Set wkbk = Workbooks.Open(Filename:=myFileName) Else Exit Sub End If ActiveSheet.Cells.Select Selection.Copy Application.DisplayAlerts = False 'wkbk.Close SaveChanges:=False '---attempts to make it work 'ActveWindow.Close '---Attempts to make it work Windows("JobCost Template V3.3.xls").Activate Sheets("Ledger").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Summary").Select Range("A16").Select Application.DisplayAlerts = True End Sub -- Jim "Joel" wrote: I'm not sure from your description if you are just asking to close the 3 files, or also asking about a better way of opening the files. Everything you are asking is possible. I think upgrading your poresent macros rather than add ing new macros is the best way of going. It would be easier if you posted the old macros so they can be modified. "Jim G" wrote: I have a template that collates and summaries data from three data sheets created by our database. Each file is saved as a job number followed by GL, L or J. For example, General Ledger data is exported to a file called 1234GL.xls, Labour data is exported to a file called 1234L.xls, etc. Each file has only one sheet with the same name as the file name without the xls extension. I have destination sheets in a Job Summary Template called "Ledger", "Labour" and 'JobCard". Macros are triggered when the destination sheet is updated. Currently users are taken via a macro to the file directory and prompted to select a data file. The entire contents of the ActiveSheet is copied to the sheet allocated to that macro (€śLedger€ť, €śLabour€ť etc). Unfortunately, the user must close the three open data sheets before continuing to the next job summary. Would it be possible for the user to be prompted for the job number, have this added to the €śGL, €śL or €śJ€ť and the file selected for them, and the contents of the ActiveSheet copied to the named target sheet and the data file closed. If the prompt is too difficult I would be happy with being able to close the data file after the data is copied to the named target sheet. -- Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste contents of one sheet into another | Excel Programming | |||
Macro to paste in the active cell the contents of a cell from another file?? | Excel Programming | |||
Active Cell Copy And Paste Sheet to Sheet | New Users to Excel | |||
Rename active sheet to contents of specific cell | Excel Programming | |||
Copy from active sheet and paste into new sheet using info from cell in active | Excel Programming |