| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
import info from one workbook to another workbook
I developed a technical pack template to be used for each style that is
designed. Designer opens tech pack and on the first sheet she fills in the heading with information such as style # description fabric fabric content factory these are all in different cells When she is done she saves the tech pack using the style # so the tech pack template can be used for another style. What I would like to do but do not know how to is in another workbook called techbook log is when the designer fills in the information that it gets logged into the techbook log. The techbook log will have column headings style # Description Fabric Fabric Content Factory Is there a way to have the log book automatically be filled in for each tech pack that is created. If there are 20 styles there would be 20 tech packs therefore there would be 20 entries into the techbook log. Can this be done and if so how? Greatly appreciate your help. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import info from one workbook to another workbook
On 3 jan, 03:53, ljgent wrote:
I developed a technical pack template to be used for each style that is designed. Designer opens tech pack and on the first sheet she fills in the heading with information such as style # description fabric fabric content factory these are all in different cells When she is done she saves the tech pack using the style # so the tech pack template can be used for another style. What I would like to do but do not know how to is in another workbook called techbook log is when the designer fills in the information that it gets logged into the techbook log. The techbook log will have column headings style # * Description * Fabric * Fabric Content * Factory Is there a way to have the log book automatically be filled in for each tech pack that is created. If there are 20 styles there would be 20 tech packs therefore there would be 20 entries into the techbook log. *Can this be done and if so how? Greatly appreciate your help. Hi Ijgent, In Excel 2003 I have created the macro below. It should be placed in the workbook macro set of the technical pach template. Change the line with Set wrkLog = ... to the correct location Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wrbLog As Workbook Dim lngRow As Long Dim blnLogged As Boolean Set wrbLog = Workbooks.Open("C:\...\techbook_log.xls") If IsEmpty(wrbLog.Sheets("Sheet1").Cells(2, 1)) Then lngRow = 2 Else lngRow = 1 Do lngRow = lngRow + 1 Loop Until wrbLog.Sheets("Sheet1").Cells(lngRow, 1).Value _ ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value Or _ IsEmpty(wrbLog.Sheets("Sheet1").Cells(lngRow, 1)) If Not IsEmpty(wrbLog.Sheets("Sheet1").Cells(lngRow, 1)) Then If wrbLog.Sheets("Sheet1").Cells(lngRow - 1, 1).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value Then ' style already logged : modify lngRow = lngRow - 1 Else ' create empty row wrbLog.Sheets("Sheet1").Cells(lngRow, 1).EntireRow.Select Selection.Insert Shift:=xlDown End If End If End If ' copy info wrbLog.Sheets("Sheet1").Cells(lngRow, 1).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value wrbLog.Sheets("Sheet1").Cells(lngRow, 2).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(2, 2).Value wrbLog.Sheets("Sheet1").Cells(lngRow, 3).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(3, 2).Value wrbLog.Sheets("Sheet1").Cells(lngRow, 4).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(4, 2).Value wrbLog.Sheets("Sheet1").Cells(lngRow, 5).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(5, 2).Value ThisWorkbook.Activate wrbLog.Save wrbLog.Close End Sub HTH, Wouter |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import info from one workbook to another workbook
Once I figure this out I will give it a try.
Thanks for your time and help.. I will let you know. P.S. I may need more help ljgent "RadarEye" wrote: On 3 jan, 03:53, ljgent wrote: I developed a technical pack template to be used for each style that is designed. Designer opens tech pack and on the first sheet she fills in the heading with information such as style # description fabric fabric content factory these are all in different cells When she is done she saves the tech pack using the style # so the tech pack template can be used for another style. What I would like to do but do not know how to is in another workbook called techbook log is when the designer fills in the information that it gets logged into the techbook log. The techbook log will have column headings style # Description Fabric Fabric Content Factory Is there a way to have the log book automatically be filled in for each tech pack that is created. If there are 20 styles there would be 20 tech packs therefore there would be 20 entries into the techbook log. Can this be done and if so how? Greatly appreciate your help. Hi Ijgent, In Excel 2003 I have created the macro below. It should be placed in the workbook macro set of the technical pach template. Change the line with Set wrkLog = ... to the correct location Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wrbLog As Workbook Dim lngRow As Long Dim blnLogged As Boolean Set wrbLog = Workbooks.Open("C:\...\techbook_log.xls") If IsEmpty(wrbLog.Sheets("Sheet1").Cells(2, 1)) Then lngRow = 2 Else lngRow = 1 Do lngRow = lngRow + 1 Loop Until wrbLog.Sheets("Sheet1").Cells(lngRow, 1).Value _ ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value Or _ IsEmpty(wrbLog.Sheets("Sheet1").Cells(lngRow, 1)) If Not IsEmpty(wrbLog.Sheets("Sheet1").Cells(lngRow, 1)) Then If wrbLog.Sheets("Sheet1").Cells(lngRow - 1, 1).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value Then ' style already logged : modify lngRow = lngRow - 1 Else ' create empty row wrbLog.Sheets("Sheet1").Cells(lngRow, 1).EntireRow.Select Selection.Insert Shift:=xlDown End If End If End If ' copy info wrbLog.Sheets("Sheet1").Cells(lngRow, 1).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value wrbLog.Sheets("Sheet1").Cells(lngRow, 2).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(2, 2).Value wrbLog.Sheets("Sheet1").Cells(lngRow, 3).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(3, 2).Value wrbLog.Sheets("Sheet1").Cells(lngRow, 4).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(4, 2).Value wrbLog.Sheets("Sheet1").Cells(lngRow, 5).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(5, 2).Value ThisWorkbook.Activate wrbLog.Save wrbLog.Close End Sub HTH, Wouter |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import info from one workbook to another workbook
Hi There,
Where you say Change the line with Set wrkLog = ... to the correct location my files are in the C drive. I changed the file name to gbtechbook_log.xls Where you have "C\... do I put the name of the techpack where the ... are? I thought I would try and run the macro and see what information gets logged in then I would make modifications as to what info I need and dont need that was transferred. I get the following message: Compile error: Expected type name Can you assist me further? I would greatly appreciate it. thanks ljgent Set wrbLog = Workbooks.Open("C:\...\techbook_log.xls") "ljgent" wrote: Once I figure this out I will give it a try. Thanks for your time and help.. I will let you know. P.S. I may need more help ljgent "RadarEye" wrote: On 3 jan, 03:53, ljgent wrote: I developed a technical pack template to be used for each style that is designed. Designer opens tech pack and on the first sheet she fills in the heading with information such as style # description fabric fabric content factory these are all in different cells When she is done she saves the tech pack using the style # so the tech pack template can be used for another style. What I would like to do but do not know how to is in another workbook called techbook log is when the designer fills in the information that it gets logged into the techbook log. The techbook log will have column headings style # Description Fabric Fabric Content Factory Is there a way to have the log book automatically be filled in for each tech pack that is created. If there are 20 styles there would be 20 tech packs therefore there would be 20 entries into the techbook log. Can this be done and if so how? Greatly appreciate your help. Hi Ijgent, In Excel 2003 I have created the macro below. It should be placed in the workbook macro set of the technical pach template. Change the line with Set wrkLog = ... to the correct location Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim wrbLog As Workbook Dim lngRow As Long Dim blnLogged As Boolean Set wrbLog = Workbooks.Open("C:\...\techbook_log.xls") If IsEmpty(wrbLog.Sheets("Sheet1").Cells(2, 1)) Then lngRow = 2 Else lngRow = 1 Do lngRow = lngRow + 1 Loop Until wrbLog.Sheets("Sheet1").Cells(lngRow, 1).Value _ ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value Or _ IsEmpty(wrbLog.Sheets("Sheet1").Cells(lngRow, 1)) If Not IsEmpty(wrbLog.Sheets("Sheet1").Cells(lngRow, 1)) Then If wrbLog.Sheets("Sheet1").Cells(lngRow - 1, 1).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value Then ' style already logged : modify lngRow = lngRow - 1 Else ' create empty row wrbLog.Sheets("Sheet1").Cells(lngRow, 1).EntireRow.Select Selection.Insert Shift:=xlDown End If End If End If ' copy info wrbLog.Sheets("Sheet1").Cells(lngRow, 1).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value wrbLog.Sheets("Sheet1").Cells(lngRow, 2).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(2, 2).Value wrbLog.Sheets("Sheet1").Cells(lngRow, 3).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(3, 2).Value wrbLog.Sheets("Sheet1").Cells(lngRow, 4).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(4, 2).Value wrbLog.Sheets("Sheet1").Cells(lngRow, 5).Value = _ ThisWorkbook.Sheets("Sheet1").Cells(5, 2).Value ThisWorkbook.Activate wrbLog.Save wrbLog.Close End Sub HTH, Wouter |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import info from one workbook to another workbook
On 4 jan, 15:32, ljgent wrote:
Hi There, Where you say Change the line with Set wrkLog = ... to the correct location my files are in the C drive. *I changed the file name to gbtechbook_log..xls Where you have "C\... *do I put the name of the techpack where the ... are? I thought I would try and run the macro and see what information gets logged in then I would make modifications as to what info I need and dont need that was transferred. I get the following message: Compile error: Expected type name Can you assist me further? I would greatly appreciate it. thanks ljgent Set wrbLog = Workbooks.Open("C:\...\techbook_log.xls") "ljgent" wrote: Once I figure this out I will give it a try. Thanks for your time and help.. I will let you know. P.S. I may need more help ljgent "RadarEye" wrote: On 3 jan, 03:53, ljgent wrote: I developed a technical pack template to be used for each style that is designed. Designer opens tech pack and on the first sheet she fills in the heading with information such as style # description fabric fabric content factory these are all in different cells When she is done she saves the tech pack using the style # so the tech pack template can be used for another style. What I would like to do but do not know how to is in another workbook called techbook log is when the designer fills in the information that it gets logged into the techbook log. The techbook log will have column headings style # * Description * Fabric * Fabric Content * Factory Is there a way to have the log book automatically be filled in for each tech pack that is created. If there are 20 styles there would be 20 tech packs therefore there would be 20 entries into the techbook log. *Can this be done and if so how? Greatly appreciate your help. Hi Ijgent, In Excel 2003 I have created the macro below. It should be placed in the workbook macro set of the technical pach template. Change the line with Set wrkLog = ... to the correct location Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) * * Dim wrbLog As Workbook * * Dim lngRow As Long * * Dim blnLogged As Boolean * * Set wrbLog = Workbooks.Open("C:\...\techbook_log.xls") * * If IsEmpty(wrbLog.Sheets("Sheet1").Cells(2, 1)) Then * * * * lngRow = 2 * * Else * * * * lngRow = 1 * * * * Do * * * * * * lngRow = lngRow + 1 * * * * Loop Until wrbLog.Sheets("Sheet1").Cells(lngRow, 1).Value _ * * * * * * ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value Or _ * * * * * * IsEmpty(wrbLog.Sheets("Sheet1").Cells(lngRow, 1)) * * * * If Not IsEmpty(wrbLog.Sheets("Sheet1").Cells(lngRow, 1)) Then * * * * * * If wrbLog.Sheets("Sheet1").Cells(lngRow - 1, 1).Value = _ * * * * * * * * ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value Then * * * * * * * * ' style already logged : modify * * * * * * * * lngRow = lngRow - 1 * * * * * * Else * * * * * * * * ' create empty row * * * * * * * * wrbLog.Sheets("Sheet1").Cells(lngRow, 1).EntireRow.Select * * * * * * * * Selection.Insert Shift:=xlDown * * * * * * End If * * * * End If * * End If * * ' copy info * * wrbLog.Sheets("Sheet1").Cells(lngRow, 1).Value = _ * * * * ThisWorkbook.Sheets("Sheet1").Cells(1, 2).Value * * wrbLog.Sheets("Sheet1").Cells(lngRow, 2).Value = _ * * * * ThisWorkbook.Sheets("Sheet1").Cells(2, 2).Value * * wrbLog.Sheets("Sheet1").Cells(lngRow, 3).Value = _ * * * * ThisWorkbook.Sheets("Sheet1").Cells(3, 2).Value * * wrbLog.Sheets("Sheet1").Cells(lngRow, 4).Value = _ * * * * ThisWorkbook.Sheets("Sheet1").Cells(4, 2).Value * * wrbLog.Sheets("Sheet1").Cells(lngRow, 5).Value = _ * * * * ThisWorkbook.Sheets("Sheet1").Cells(5, 2).Value * * ThisWorkbook.Activate * * wrbLog.Save * * wrbLog.Close End Sub HTH, Wouter- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Hi Ijgent, Between "C:\" and "\gbtechbook_log.xl" the complete path to this file must be inserted. What part of the macro is highlighted after you get the compile error? Wouter |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
import info from one workbook to another workbook
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| pasting info from workbook to sheet in another workbook | Excel Programming | |||
| Import/Copy some data from one workbook to a similar workbook | Excel Programming | |||
| Accessing workbook info from a different workbook macro | Excel Programming | |||
| Accessing workbook info from a different workbook macro | Excel Programming | |||
| Excel Gurus = want a macro in 1 workbook to get info from another workbook = Read please | Excel Programming | |||
Linear Mode
